# Gaps and Islands — SQL Deep Dive

Source: https://www.skillbyai.com/en/sql-deep-dive/a-islands

> Finding consecutive runs.

## Value minus row number

To find streaks of consecutive days, number each user's days with ROW_NUMBER and subtract it from the date: within a run of consecutive days the difference is constant, so it identifies the **island**. Grouping by that key gives each streak's start, end and length. The same trick finds consecutive IDs, uninterrupted sessions or periods of the same status (compare with LAG to detect changes).

## Login streaks per user, run

I ran this with psql on PostgreSQL 16.2 against the course sample data (shown in the first section). User 1 has streaks of 3 and 2 days; user 2 has a single day and then a 4-day streak.

```sql
-- Consecutive login streaks: day minus row_number is constant within a streak
WITH numbered AS (
  SELECT user_id, day,
         day - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY day))::int AS grp
  FROM logins
)
SELECT user_id, min(day) AS streak_start, max(day) AS streak_end, count(*) AS days
FROM numbered GROUP BY user_id, grp
ORDER BY user_id, streak_start;
```

Output:

```
 user_id | streak_start | streak_end | days 
---------+--------------+------------+------
       1 | 2026-03-01   | 2026-03-03 |    3
       1 | 2026-03-05   | 2026-03-06 |    2
       2 | 2026-03-02   | 2026-03-02 |    1
       2 | 2026-03-04   | 2026-03-07 |    4
(4 rows)
```

## Deduplicate first

Two logins on the same day break the trick; use DISTINCT user_id, day before numbering.

**Quiz:** In gaps-and-islands, what stays constant within a run of consecutive days?

- [ ] The date
- [ ] The row number
- [x] The date minus its row number
- [ ] The user count

*Answer:* The date minus its row number. That difference is the island key.