SQL Topic
Gaps and Islands
The gaps-and-islands pattern finds contiguous runs ('islands') of rows — consecutive dates, consecutive IDs — by subtracting a row number from a date to bucket each run. It answers 'longest streak', 'consecutive logins', and 'missing dates'.
When is Gaps and Islands used?
Consecutive-day login streaks, consecutive numbers in a sequence, periods of continuous activity, and missing-date analysis.
Core syntax
WITH numbered AS (SELECT *, ROW_NUMBER() OVER (ORDER BY login_at) AS rn FROM logins) SELECT ... GROUP BY julianday(login_at) - rn — each run shares a constant.Common mistakes
- Not de-duplicating first when a user can log in twice the same day.
- Using RANK instead of ROW_NUMBER, which breaks the run math on ties.
- Assuming dates are already contiguous and skipping the date arithmetic.
Practice Gaps and Islands questions
Work through them in order — each question builds on the last.