Streaks (Gaps and Islands) in SQL

Example query

This example assumes: events(id, user_id bigint, occurred_at timestamptz). It is written in PostgreSQL syntax, and your table and column names will differ from these.

WITH days AS (
  SELECT DISTINCT user_id, occurred_at::date AS day
  FROM events
),
grouped AS (
  SELECT user_id,
         day,
         day - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY day))::int AS grp
  FROM days
)
SELECT user_id,
       MIN(day) AS streak_start,
       MAX(day) AS streak_end,
       COUNT(*) AS streak_days
FROM grouped
GROUP BY user_id, grp
ORDER BY streak_days DESC, user_id;

How it works

• The first CTE reduces events to one row per user per active day.
• Subtracting a running row number from the date gives the same value for every day in an unbroken run: consecutive days minus consecutive numbers stay constant.
• A gap in the dates changes that value, so grouping by it separates the streaks, and MIN, MAX and COUNT describe each.

MySQL, BigQuery, SQL Server and SQLite differences

• Date minus integer works in PostgreSQL. Elsewhere subtract with DATE_SUB(day, INTERVAL rn DAY) in MySQL and BigQuery, DATEADD(day, -rn, day) in SQL Server, or date(day, '-' || rn || ' days') in SQLite.

Common mistakes

• Duplicate days break the arithmetic, so the DISTINCT in the first step matters.
• The day boundary depends on the time zone; a late-night user can appear to skip a day.
• A streak that is still running ends today; decide whether to treat it as ongoing.

Ask for it in plain English

Instead of writing the query yourself, you can type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. Check that the query matches the definition your business uses.

• What is each user's longest streak of consecutive active days?
• How many users were active on at least 7 days in a row?