22. Consecutive Login Streaks (Gaps & Islands)
HardRankingROW_NUMBERgaps and islandsgrouping trick
The classic gaps-and-islands problem. Subtracting a row number from a sequential date yields a constant for every run of consecutive dates — a ready-made grouping key.
Task: each row in logins is one date a user was active. Collapse runs of
consecutive days into streaks. Return start_date, end_date, and days
(length of the streak), ordered by start_date.
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.