SQL Gaps and Islands

Finish the pattern from the previous lesson: GROUP BY that constant to turn scattered dates into clean streaks.

What & Why

"Islands" are the runs of consecutive dates; "gaps" are the breaks between them. Once each row has that grp value from the previous lesson, grouping by it collapses each island into a single summary row — start date, end date, streak length.

See How It Works

BUSINESS QUESTION

Collapse the observed growth-event dates into continuous calendar-day streaks.

id	user_id	event_name	created_at	properties
1001	101	signup	2024-01-15 09:12:00+00	{"source":"organic"}
1002	101	activated	2024-01-16 14:25:00+00	{"step":"workspace"}
1003	102	signup	2024-02-10 11:08:00+00	{"source":"paid"}
1004	103	report_viewed	2024-03-12 16:40:00+00	{"report":"retention"}

Watch stable date keys collapse consecutive events into islandsStep 1 of 2

event_date - ROW_NUMBER()::int = island_key

event_date	row_number	island_key
2024-01-15	1	2024-01-14
2024-01-16	2	2024-01-14
2024-02-10	3	2024-02-07
2024-03-12	4	2024-03-08

January 15 and 16 produce the same shifted key, so they belong to one island.

EXAMPLE QUERY

WITH event_days AS (
  SELECT DISTINCT created_at::date AS event_date
  FROM growth.events
),
numbered AS (
  SELECT
    event_date,
    event_date - ROW_NUMBER() OVER (ORDER BY event_date)::int AS island_key
  FROM event_days
)
SELECT
  MIN(event_date) AS streak_start,
  MAX(event_date) AS streak_end,
  COUNT(*) AS streak_days
FROM numbered
GROUP BY island_key
ORDER BY streak_start;

This lesson's practice is part of Pro.

See what's included →

Advanced business practice