sql · level 3 · unit 4 · lesson 2
- meet it
- guess first
- your turn
- fix it
- nailed it
Tap a green word if you get lost.
meet it
Make a of dates, then join the real data onto it.
The café has no row for 4 June, so reports skip that day and averages mislead. Fix it with a series: every date from the first day to the last, made by a recursive CTE. date(day, '+1 day') gives the next date.
Then LEFT JOIN the takings onto the series. Days with no takings get NULL, because nothing matched them.
swaps that NULL for 0. It gives back the first of its values that is not NULL, so a missing day becomes a day that took nothing.
WITH RECURSIVE days(day) AS (
SELECT '2024-06-01'
UNION ALL
SELECT date(day, '+1 day') FROM days WHERE day < '2024-06-08'
)
SELECT d.day, COALESCE(t.amount, 0) AS amount
FROM days AS d
LEFT JOIN takings AS t ON t.day = d.day
ORDER BY d.day;Dates written as YYYY-MM-DD sort and compare correctly as text.
guess first
Three days, one of them missing. What shows?
Guess before you run it. Wrong guesses are how you learn, and nothing is marked.
WITH RECURSIVE days(day) AS (
SELECT '2024-06-03'
UNION ALL
SELECT date(day, '+1 day') FROM days WHERE day < '2024-06-05'
)
SELECT d.day, t.amount
FROM days AS d
LEFT JOIN takings AS t ON t.day = d.day
ORDER BY d.day;your turn
Your turn. A calendar that fits any dates.
Hard-coded dates break next month. Fill the blanks so the series starts at the first day in takings and stops at the last one.
result
Your answer appears here as a table.
fix it
A closed day did not pull the average down. Fix it!
result
Your answer appears here as a table.
- Look at 4 June. The café took nothing, yet its average is as high as the day before.
AVGskipsNULLs, so the empty day is left out instead of counting as 0.- Change AVG(t.amount) to AVG(COALESCE(t.amount, 0)) on line 7, then press run.
nailed it
no day goes missing now. :D
- A recursive CTE can make a series of dates.
LEFT JOINthe data onto the series to keep empty days.COALESCE(x, 0)turns aNULLinto 0.
new words in your collection
- series
- COALESCE
This lesson isn’t saved. A free account remembers every lesson you finish.