LEFT JOIN with GROUP BY
Put the CREATE TABLE and INSERT statements in Define Schema and the SELECT in SQL Query. LEFT JOIN keeps customers who have no orders, and IFNULL turns their missing total into 0. Expected rows: Ada with 2 orders and 155.5, Grace with 1 order and 80.25, and Linus with 0 orders and 0.
-- Define Schema
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL, country TEXT);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER REFERENCES customers(id),
total REAL,
placed_on TEXT
);
INSERT INTO customers (name, country) VALUES ('Ada', 'UK'), ('Linus', 'FI'), ('Grace', 'US');
INSERT INTO orders (customer_id, total, placed_on) VALUES
(1, 120.00, '2026-01-05'), (1, 35.50, '2026-02-11'), (3, 80.25, '2026-02-20');
-- SQL Query
SELECT c.name, COUNT(o.id) AS orders, IFNULL(SUM(o.total), 0) AS revenue
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id
ORDER BY revenue DESC;
Running totals and ranking with window functions
Uses the orders table from the previous example. SUM() OVER builds a running total in date order, while RANK() ranks each order by size. Expected rows: 120 with a running total of 120 and rank 1, 35.5 with 155.5 and rank 3, then 80.25 with 235.75 and rank 2.
SELECT placed_on, total,
SUM(total) OVER (ORDER BY placed_on) AS running_total,
RANK() OVER (ORDER BY total DESC) AS size_rank
FROM orders
ORDER BY placed_on;
How SQLite type affinity stores values
SQLite converts values according to each column's type affinity instead of rejecting mismatches. The text '42' is stored as the integer 42 in an INTEGER column, 'abc' stays text, the number 42 becomes the text '42' in a TEXT column, and 7.0 is stored as the integer 7. typeof() shows what was actually stored.
-- Define Schema
CREATE TABLE readings (n INTEGER, label TEXT);
INSERT INTO readings VALUES ('42', 42), ('abc', 3.5), (7.0, NULL);
-- SQL Query
SELECT n, typeof(n) AS n_type, label, typeof(label) AS label_type
FROM readings;
