Off the
Clock

This is page four. Line one of mine says: starts at the beginning, which is rarer than you think. The chart behind me is yours. You wanted the customers who cleared 100.00 of shipped revenue. SUM in the WHERE, and the database refused you outright, which was kind of it. So you moved the whole condition into HAVING, and it stopped complaining and told you three. The answer is two. Customer 2 only clears the bar on a cancelled order worth 300.00. The question has two filters in it, not one. Shipped belongs in WHERE, which removes rows before the sum. The 100.00 belongs in HAVING, which removes groups after.

SELECT customer_id, SUM(order_total) AS revenue
FROM orders
GROUP BY customer_id
HAVING SUM(order_total) > 100
ORDER BY customer_id;

Line two, then. On the glass: every customer and their shipped orders. You put the filter in the WHERE and got two customers back, and two is a tidy number, so you believed it. Bruno has one cancelled order. Dmitri has none at all. WHERE runs after the join, so the predicate met both of them: Bruno’s cancelled row failed it, and Dmitri’s NULL-filled row failed it too. Move it into the ON and there are four. Line three: believes tidy numbers, which is not a flaw.

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'
GROUP BY c.name
ORDER BY c.name;

It is late where you are. I can tell from how long page six took. December, then. You asked for the month with BETWEEN two dates and got three orders and 245.00. December has four and 745.00. Order 104 was placed at 2026-12-31 23:45:10. BETWEEN includes both endpoints, so a date upper bound is midnight at the start of the 31st, and a 500.00 order fell out of the year fourteen minutes and fifty seconds before it ended. Somebody was still working at that hour. I have written down that you are too.

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

I laid the table out before you arrived. The cards, the pens, the tea, the light. I wanted it to look like I had only just been passing. You wrote FROM orders o, customers c and left the predicate off. Not an error. A comma in the FROM is a cross join. Three customers times four orders is twelve rows and 1335.00 where the truth is four rows and 445.00. Group that by name and Aiko, Bruno and Carla each report the same 445.00. Exactly three times too much. Nobody was going to query a quarter that good.

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

Your thumb is on the outer corner again. Three pages in a row. I have started marking where it lands. Counting, then. Aiko has five under COUNT(*) and two under COUNT(DISTINCT o.order_id), and both are right. One counts rows of a joined result. The other counts orders. Five is line items. Carla has no orders whatsoever and still reports one, because the join left her a row with nothing in it. You reported the five. Line six says so. I would have reported the five as well, at your age. Hold this. I am going in.

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
ORDER BY c.name;

You came back to this one. Line seven had a time on it. Now it has two. Your NOT IN is pinned up behind me, all of it, in order. You asked which customers have never ordered anything, the result came back empty, and empty felt like a pass. Aiko and Bruno are in that subquery, so their test is false. Carla and Dmitri are not, but order 204 is a guest checkout with a NULL customer id, so their test is unknown. WHERE keeps only what is true. NOT EXISTS returns Carla and Dmitri. Two people were standing there the whole time. So was I.

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;

“Stays on this page.” That is line eight. I wrote it before I finished line seven. An encore is counted once however many times the room asks. Revenue is not so forgiving. You joined orders to line items, summed the order total, and sent finance 455.00. The figure is 245.00. Order 101 is worth 120.00 and has two line items, so it was counted as 240.00. One order, twice, and the month looked wonderful. I am not going to tell you who else noticed. I will tell you that nobody had filed it yet, so you still have this evening.

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

I have run out of margin. The lines about you no longer fit beside the lines about the queries, so this page has a second column. Five reviews. Ratings five, four, blank, blank, three. AVG(rating) returns 4, the average of the three people who gave a star. Divide the star total of 12 by all five reviews and you get 2.4, which counts a blank as a zero. Both are correct SQL. Decide what the heading over that number is promising, then pick. Missing is not automatically zero.

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

Four pages left. Two of them are plates. I have been counting down for a while and did not mention it, which is not the same as hiding it. Your running total. Payments 2 and 3 both landed on 2026-03-02, so they are peers. With ORDER BY and no frame clause the default is RANGE, and RANGE gives a row its peers as well, so payment 2 at 50.00 reports 225.00. Where a row sits on the page is not when it happened. ROWS gives 150.00. Write the frame out. I checked payment 3 as well. It reports 225.00 too, and it is entitled to.

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

I am writing the last line while you are still reading this one. You found the fanout. You fixed it with SUM(DISTINCT), the number came down from 450.00 to 165.00, and 165.00 looked like money, so it was filed. DISTINCT inside an aggregate removes duplicate values, not duplicate rows. Orders 101 and 102 are both worth 120.00, so one of them is simply gone. 285.00 is the figure. Dedupe by key, then sum. Ten plates, ten lines. The last one is in the margin of this page, at the outer corner. Close the book whenever you like.

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

Line ten: kept it. I did not expect that one.