19. Fill the Date Gaps
HardRecursive CTEsWITH RECURSIVEcalendar generationLEFT JOINCOALESCE
events records a count for some — but not all — days. Missing
days should be treated as zero.
Task: produce one row for every day from the earliest to the latest
event_date (inclusive). Return day and cnt, using 0 for days that have
no event. Order by day.
Hint: SQLite's date(d, '+1 day') advances a date string by one day.
Tables
Loading schema…
Expected outputrows must come back in this order
Computing expected output…
This is the result for the example data above. On Submit your query is graded against this example plus 4 hidden edge cases — 5test cases in all. A sloppy query that only fits the example won't pass.
SQL· SQLite · runs in your browser
⌘/Ctrl+↵ run · ⇧⌘/Ctrl+↵ submit
Run a query to see its output here.