SQL Consecutive Dates

The classic trick: subtract a row number from the date itself — consecutive dates all land on the same result.

What & Why

To find runs of consecutive dates, subtract ROW_NUMBER() (as a day count) from each date. Within an unbroken run, the date increases by exactly 1 each row while the row number also increases by exactly 1 — so the subtraction cancels out to the same constant for the whole run.

See How It Works

BUSINESS QUESTION

Assign stable keys to the distinct dates observed in growth.events.

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 consecutive dates form a stable row-number keyStep 1 of 4

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 minus row one establishes the first shifted key.

EXAMPLE QUERY

WITH event_days AS (
  SELECT DISTINCT created_at::date AS event_date
  FROM growth.events
),
numbered AS (
  SELECT
    event_date,
    ROW_NUMBER() OVER (ORDER BY event_date) AS row_number
  FROM event_days
)
SELECT
  event_date,
  row_number,
  event_date - row_number::int AS island_key
FROM numbered
ORDER BY event_date;

This lesson's practice is part of Pro.

See what's included →

Advanced business practice