Notion hard Recursive CTELEFT JOINCASE WHENCTEORDER BY

For the habit 'Morning Meditation', build a full calendar of all seven days from 2026-08-01 through 2026-08-07 using a recursive CTE, then LEFT JOIN it against habit_logs to find that habit's status on each day. Label each day's status as 'Completed' if completed = 1, 'Skipped' if completed = 0, and 'No Data' if there's no matching row in habit_logs at all for that day. Return day and status for all seven days, sorted by day.

Notion · Habit Streak Calendars — practice this real-world SQL scenario live in your browser.

📖 Story Notion · Habit Streak Calendars

Friday morning at Notion's Habit Tracker team. The habit_logs table only gets a row when the app is actually opened that day — a completed habit gets logged as 1, a habit the user opened the app for but chose to skip gets logged as 0, and a day the user never opened the app at all simply has no row whatsoever. Weekly streak calendars need to show all seven days regardless, including the days with no data at all, but habit_logs only ever contains the days something actually happened — there's no table anywhere that already lists 'every day of the week' to join against.

🎯 Your Mission

📋 Table Structure

🗂 habit_logs

log_id INTEGER 1

habit_name TEXT Morning Meditation

log_date TEXT 2026-08-01

completed INTEGER 1

⚡ Step-by-Step Walkthrough

Look at the raw habit log — only five rows for a seven-day week

query.sql

SELECT *
FROM habit_logs
ORDER BY log_date;

💡 Explanation

habit_logs has exactly five rows, but the week being reported on spans seven days — 2026-08-04 and 2026-08-06 don't appear anywhere in this table at all, not as a row with a NULL completed value, just as rows that were never created.
2026-08-03's row has completed = 0 — the user opened the app that day and actively marked the habit as not done, which is a genuinely different event from a day the app was never opened at all. Both will eventually need to be told apart in the final report.
There's no table in this database that already lists every calendar date — habit_logs only ever contains a row for a day something happened, so the seven-day calendar itself has to be built from scratch rather than looked up anywhere.
Nothing about a full week is visible yet from this table alone — this step only confirms how sparse the raw log actually is against the week it needs to represent.

Build the missing calendar with a recursive CTE

query.sql

WITH RECURSIVE date_series(day) AS (
  SELECT DATE('2026-08-01')
  UNION ALL
  SELECT DATE(day, '+1 day')
  FROM date_series
  WHERE day < DATE('2026-08-07')
)
SELECT day FROM date_series;

💡 Explanation

A recursive CTE has two parts joined by UNION ALL: the first SELECT (the anchor) produces the very first row, and the second SELECT (the recursive member) refers back to date_series itself, generating the next row from the previous one — something an ordinary, non-recursive CTE can never do.
The anchor is SELECT DATE('2026-08-01') — exactly one row, 2026-08-01, which becomes the starting point the recursion builds on.
The recursive member, SELECT DATE(day, '+1 day') FROM date_series WHERE day < DATE('2026-08-07'), runs again and again: first against the anchor's single row to produce 08-02, then against 08-02 to produce 08-03, and so on, each pass adding exactly one more day.
WHERE day < DATE('2026-08-07') is the part that eventually stops the recursion — once day reaches 08-07, the condition is false, no more rows get produced, and the CTE settles at exactly seven rows: 08-01 through 08-07. Without a stopping condition like this, a recursive CTE keeps producing rows indefinitely.

LEFT JOIN the calendar against habit_logs and label each day

query.sql

WITH RECURSIVE date_series(day) AS (
  SELECT DATE('2026-08-01')
  UNION ALL
  SELECT DATE(day, '+1 day')
  FROM date_series
  WHERE day < DATE('2026-08-07')
)
SELECT ds.day,
       CASE
         WHEN hl.completed = 1 THEN 'Completed'
         WHEN hl.completed = 0 THEN 'Skipped'
         ELSE 'No Data'
       END AS status
FROM date_series ds
LEFT JOIN habit_logs hl
  ON hl.log_date = ds.day AND hl.habit_name = 'Morning Meditation'
ORDER BY ds.day;

💡 Explanation

LEFT JOIN keeps every one of the seven rows from date_series no matter what — if a day has no matching habit_logs row, hl.completed simply comes back NULL for that day rather than the day disappearing from the result the way an INNER JOIN would make it disappear.
The CASE expression checks hl.completed = 1 and hl.completed = 0 explicitly, in that order, before falling through to ELSE — a genuinely unmatched day has hl.completed as NULL, and NULL never equals 1 or 0 in either comparison, so it correctly falls through to 'No Data' rather than accidentally matching one of the first two branches.
2026-08-04 and 2026-08-06 — the two days with no row in habit_logs at all — correctly come back labeled 'No Data', while 2026-08-03, which does have a row with completed = 0, correctly comes back 'Skipped' instead. The recursive CTE from step 2 is what made it possible to even have a row for 08-04 and 08-06 to label in the first place.
hl.habit_name = 'Morning Meditation' is part of the ON clause, not a separate WHERE filter — that keeps it evaluated during the join itself, so a day would still appear as 'No Data' rather than disappearing entirely if that condition ever excluded every matching row, exactly the same LEFT-JOIN-safe pattern as filtering inside ON instead of WHERE.

▶ Practice this live ← Browse all questions