Off the
Clock

Card one. Stamped 21:40, which is when I stopped teaching and started writing. You asked how many orders Aiko has and you shipped the answer five. COUNT(*) says 5. COUNT(o.order_id) says 5. COUNT(DISTINCT o.order_id) says 2. All three ran in one query and all three are correct. Two orders, five line items, one FROM clause. I drew that crow’s foot this morning and left it up all day. You had forty minutes in front of it. Carla has no orders and still counts as 1.

SELECT c.name,
       COUNT(*)                   AS count_star,
       COUNT(o.order_id)          AS count_order_id,
       COUNT(DISTINCT o.order_id) AS count_distinct_orders
FROM customers c
LEFT JOIN orders     o  ON o.customer_id = c.customer_id
LEFT JOIN line_items li ON li.order_id   = o.order_id
GROUP BY c.name;

22:15. The tea was poured at 21:50 and I have not touched it. Your second question errored, so you moved the condition into HAVING and called it fixed. It compiles. It also reports three customers over 100.00 of revenue, and the true count is two. Customer 2 clears the bar on a 300.00 order that was cancelled. WHERE removes rows before the sum. HAVING removes groups after it. You needed both and wrote only the one that silenced the error. The date stamp is bolted to this desk. I have a screwdriver.

SELECT customer_id, SUM(order_total) AS revenue
FROM orders
GROUP BY customer_id
HAVING SUM(order_total) > 100
ORDER BY customer_id;
-- the version that errors: WHERE SUM(order_total) > 100
-- Binder Error: WHERE clause cannot contain aggregates!

The hall is the only room where the whole schema is visible at once, so I bring the hard ones here. Yours was not hard. FROM orders o, customers c, and no predicate. That is legal SQL. It returned 12 rows and 1335.00 against a true 4 rows and 445.00, which is exactly three times, because there are three customers. Look at the third result. Aiko 445.00, Bruno 445.00, Carla 445.00. Every customer credited with every order. I rewrote it with an ON clause so the same mistake would refuse to run. That took nine minutes I had not planned to spend.

SELECT COUNT(*)           AS rows_returned,
       SUM(o.order_total) AS revenue
FROM orders o, customers c
WHERE o.order_total > 0;

I read this card three times before filing it. The procedure allows one. Your NOT IN returned zero rows and you took zero rows as a clean result. Order 204 is a guest checkout with a NULL customer_id. Aiko and Bruno appear in that subquery, so their test is FALSE. Carla and Dmitri appear nowhere in it, so their test should be TRUE; the NULL leaves it UNKNOWN instead. WHERE keeps only TRUE, so nothing comes back. NOT EXISTS finds Carla and Dmitri. An empty result is a claim about your data. There are four customers in that table. You could have counted them by hand.

SELECT c.customer_id, c.name
FROM customers c
WHERE c.customer_id NOT IN (SELECT o.customer_id FROM orders o)
ORDER BY c.customer_id;

Stamped 06:40, before the lanes opened. This is the hour I keep for myself, because it is the only one that does not lie to me, and I was already here. SUM(amount) OVER (ORDER BY paid_on) with no frame clause defaults to RANGE, and under RANGE a row is given every peer that shares its value. Payments 2 and 3 both landed on 2026-03-02, so payment 2, at 50.00, is credited with payment 3 as well and reports 225.00. Two swimmers touching the wall together still get two times. Name ROWS and a tiebreaker and payment 2 reads 150.00.

SELECT payment_id, paid_on, amount,
       SUM(amount) OVER (ORDER BY paid_on) AS running_total
FROM payments
ORDER BY payment_id;

I did not come for the show. I came because the house keeps its takings in a ledger I wanted to see, and the card went in my cuff on the way. Stamped 23:58 in a corridor, because there was nowhere to put a card down. You caught the fanout yourself. That was good. Then you wrote SUM(DISTINCT o.order_total). 450.00 became 165.00. The true revenue is 285.00. DISTINCT inside an aggregate deduplicates values. Orders 101 and 102 are both 120.00 and are different orders, so one of them stopped existing. 450.00 gets questioned by somebody. 165.00 gets filed.

SELECT SUM(DISTINCT o.order_total) AS revenue
FROM orders o
JOIN line_items li ON li.order_id = o.order_id;

This card is not stamped. Both of your numbers were correct, which is the problem. AVG(rating) returns 4 because AVG ignores the NULLs and only three of the five reviews carry a star. SUM(rating) divided by COUNT(*) returns 2.4, which is the same as scoring an unrated review as a zero. The dashboard heading has to say which of those it means; the warehouse will not choose for you. Two support tickets, no refunds, and SUM(refund_amount) returns NULL rather than 0.00. Nothing is not the same as zero. I took my glasses off at some point and have not put them back on.

SELECT COUNT(*)                     AS reviews,
       COUNT(rating)                AS rated_reviews,
       AVG(rating)                  AS avg_of_rated,
       SUM(rating) * 1.0 / COUNT(*) AS avg_over_all
FROM order_reviews;

The storm put the observatory on backup at 02:11 and I stamped this one by the emergency light. BETWEEN DATE ‘2026-12-01’ AND DATE ‘2026-12-31’ reported 3 orders and 245.00. December had 4 and 745.00. BETWEEN includes both endpoints, so a date-valued upper bound is midnight at the start of the 31st, and order 104 was placed at 2026-12-31 23:45:10 for 500.00. It fell off the end of the year and nothing said so. You wrote the half-open version at the bottom of the page and crossed it out. It was correct. Next time, do not cross it out.

SELECT COUNT(*) AS orders, SUM(order_total) AS revenue
FROM orders
WHERE placed_at BETWEEN DATE '2026-12-01' AND DATE '2026-12-31';

Stamped 01:30 on the marble. The cold out here matches the cold I keep. I came up for a forty-page paper on market microstructure and a red pen, which I read for enjoyment. The card took an ink stain on the way. Your LEFT JOIN returned 2 rows. Written correctly it returns 4. Putting o.status = ‘shipped’ in the WHERE applies it after the join, so Bruno’s cancelled row fails it and Dmitri’s NULL-filled row does too. Both leave the report. Your OR o.status IS NULL patch gets you 3. It rescues Dmitri, who has no orders, and loses Bruno, whose only order was cancelled.

SELECT c.name, COUNT(o.order_id) AS shipped_orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'shipped' OR o.status IS NULL
GROUP BY c.name
ORDER BY c.name;

Card ten. Stamped 05:12, in a library that moves at ninety kilometres an hour. Last question: the revenue you sent finance. 455.00. The true figure is 245.00. Joining line items copied order 101 twice and order 103 three times, so 120.00 was counted as 240.00 and 45.00 as 135.00. Sum the line items, or use EXISTS, and it is 245.00 either way. You wrote the EXISTS version in the margin before I reached it. I filed your wording and not mine, because yours was shorter. The drawer is on your desk. Ten cards, in order. The stamp is back where it belongs. You keep the drawer.

SELECT SUM(o.order_total) AS revenue
FROM orders o
JOIN line_items li ON li.order_id = o.order_id;