meet it

Shipped queries survive edge cases, not only the sample data.

A query that works on today's data can fail tomorrow. Professionals hunt for every : a day with no sales, two customers tied, a NULL amount, broken JSON, a customer with no orders. Then they test the same query on several datasets.

The checklist: filter bad rows once, in a view or CTE. Fill gaps with a series and COALESCE. Break every tie in ORDER BY. Handle NULL on purpose.

This report works even for Bath, where nobody has ordered yet.

WITH city_spend AS (
  SELECT c.city, COALESCE(SUM(o.total), 0) AS spent
  FROM customers AS c
  LEFT JOIN orders AS o ON o.customer_id = c.id
  GROUP BY c.city
)
SELECT city, spent,
  RANK() OVER (ORDER BY spent DESC) AS place,
  ROUND(100.0 * spent / SUM(spent) OVER (), 1) AS pct
FROM city_spend
ORDER BY place, city;

Ask what each query does with no rows at all. It should still give a sensible answer.

guess first

A day with no orders. What do the totals say?

Guess before you run it. Wrong guesses are how you learn, and nothing is marked.

SELECT COUNT(*) AS orders,
  SUM(total) AS takings,
  COALESCE(SUM(total), 0) AS safe_takings
FROM orders
WHERE day = '2024-06-01';

your turn

Your turn. A week with an empty day.

Nobody ordered on 7 May. Fill the blanks so that day still appears, with 0 takings, and the running total carries on.

Your code

result

Your answer appears here as a table.

fix it

Raj and Sam never ordered, but show one order each. Fix it!

Broken code to fix

result

Your answer appears here as a table.

Raj and Sam have never ordered, yet each shows 1. What is COUNT(*) counting?
After a LEFT JOIN, a customer with no orders still gets one row, full of NULLs, and COUNT(*) counts it.
Change COUNT(*) to COUNT(o.id), which skips NULLs, then press run.

nailed it

your queries are ready to ship. :D

Hunt for edge cases: gaps, ties, NULLs and broken data.
Break every tie and handle every NULL on purpose.
Test the same queries on several datasets before shipping.

new words in your collection

edge case

This lesson isn’t saved. A free account remembers every lesson you finish.