Module 1 · Reading one table without fooling yourself
Lesson 1 of 6 · 7 min · English · Read-only
The grain of a table is what one row of it stands for, so name it before you query: a count reports that thing, and a filter keeps or drops whole rows at that grain.
Before you write a query, finish this sentence: “one row of this table is one …”. What you put after “one” is the table’s grain, and it decides what every count, sum and filter means. COUNT(*) does not count orders or customers. It counts rows, so the number it returns depends on what a row stands for.
This course follows one small online shop that sells kettles, mugs, tea tins and filters. Prices are whole dollars, an assumption of the example, and the SPRING coupon gives free shipping. Three tables describe the shop, and each has its own grain.
To find a grain, look for the column that never repeats, then say a real row aloud: “this row is the order customer 2 placed on 9 March.” If the sentence needs an “and also”, two grains are mixed in one table.
Counting goes wrong when the grain is finer than the thing you are counting. Order lines are finer than orders, because an order has one row for each product on it. A question about orders, asked of the lines table, needs more than a row count.
Every query in this course runs as it stands. It builds its own small tables from typed-in values (the WITH lines at the top), so all you need is SQLite or PostgreSQL; other databases may need small changes to build the same tables. The result under each query comes from running it in SQLite, and PostgreSQL gives the same values, though it may show more decimal places.
A query reads like a sentence. SELECT lists the columns you want back, FROM names the table they come from, and WHERE keeps only the rows that pass a test. UNION ALL, which you will meet in several queries, stacks the rows of the SELECT above it and the SELECT below it into one result.
Inspect the artifact
sqlLanguage: sql4 annotationsWITH order_lines(line_id, order_id, product, quantity, unit_price) AS ( VALUES ( 1, 101, 'Mug', 2, 8), ( 2, 102, 'Kettle', 1, 30), ( 3, 102, 'Tea tin', 2, 12), ( 4, 103, 'Mug', 1, 8), ( 5, 103, 'Tea tin', 1, 12), ( 6, 103, 'Filter', 3, 4), ( 7, 104, 'Kettle', 1, 30), ( 8, 105, 'Tea tin', 3, 12), ( 9, 106, 'Mug', 4, 8), (10, 106, 'Filter', 2, 4), (11, 107, 'Tea tin', 1, 12), (12, 107, 'Mug', 1, 8), (13, 108, 'Kettle', 1, 30), (14, 108, 'Filter', 1, 4), (15, 109, 'Tea tin', 2, 12))SELECT COUNT(*) AS lines_under_10, COUNT(DISTINCT order_id) AS ordersFROM order_linesWHERE unit_price < 10;lines_under_10 orders -------------- ------ 7 5
Orders 103 and 106 each contain two items under $10, a mug and a filter. That is why seven lines belong to only five orders. Reporting 7 as “orders with a small item” would overstate it by two.
Artifact content has 23 lines and 4 annotations.
WHERE keeps the rows for which its condition is true and drops the rest. A business question never says exactly which rows those are, so the filter is where the definition gets written down. “Orders in March” hides two decisions: where March starts and ends, and which statuses count.
Dates first. The timestamp 2025-03-31 18:20 is later than 2025-03-31, which means midnight at the start of that day. So BETWEEN '2025-03-01' AND '2025-03-31' silently drops every order placed after midnight on the 31st. Write the range as a start that is included and an end that is not: from 1 March, before 1 April.
Statuses next. “Orders we took” and “orders that stand today” are different groups once some orders are cancelled or refunded. Pick one and write it beside the number.
Then read the rows. List the orders on either side of each edge of the filter, here the last orders of March and the first of April, and read their timestamps. A count cannot show you the order that fell just outside; the rows can.
Think it over
Choose a table or export you work with and finish “one row is one …” for it. Read ten of its rows aloud against that sentence: do any belong to a different grain, such as a total row or a repeated header?
Inspect the artifact
sqlLanguage: sql5 annotationsWITH orders(order_id, customer_id, order_ts, status, coupon, shipping_fee) AS ( VALUES (101, 1, '2025-02-28 21:15', 'paid', NULL, 5), (102, 1, '2025-03-02 10:05', 'paid', 'SPRING', 0), (103, 2, '2025-03-09 14:30', 'paid', NULL, 5), (104, 4, '2025-03-12 11:00', 'refunded', NULL, 5), (105, 3, '2025-03-15 09:40', 'cancelled', NULL, 5), (106, 3, '2025-03-21 16:10', 'paid', 'SPRING', 0), (107, 2, '2025-03-28 12:00', 'paid', NULL, 5), (108, 4, '2025-03-31 18:20', 'paid', NULL, 5), (109, 1, '2025-04-01 08:00', 'paid', NULL, 5))SELECT 'BETWEEN 1 Mar and 31 Mar' AS filter, COUNT(*) AS ordersFROM ordersWHERE order_ts BETWEEN '2025-03-01' AND '2025-03-31'UNION ALLSELECT 'from 1 Mar, before 1 Apr', COUNT(*)FROM ordersWHERE order_ts >= '2025-03-01' AND order_ts < '2025-04-01'UNION ALLSELECT 'same range, status paid', COUNT(*)FROM ordersWHERE order_ts >= '2025-03-01' AND order_ts < '2025-04-01' AND status = 'paid';filter orders ------------------------ ------ BETWEEN 1 Mar and 31 Mar 6 from 1 Mar, before 1 Apr 7 same range, status paid 5
The first count is wrong on purpose: it misses order 108, placed at 18:20 on 31 March, and returns 6. The second keeps all seven March orders, including 104 (refunded) and 105 (cancelled). The third keeps five: orders 102, 103, 106, 107 and 108. Each of the last two is right for its own definition.
Artifact content has 25 lines and 5 annotations.