Gaps and Islands in SQL

Deduplicate first. ROW_NUMBER gives every row its own number, even when 2 rows share a date, so a user with 2 logins on day 2 would get row numbers that outrun the dates and split 1 streak into 2. The SELECT DISTINCT in the first CTE prevents that. If you would rather keep the duplicate rows, DENSE_RANK numbers each distinct date once, because it counts peer groups and gives equal values the same rank.

If the column is a timestamp, cast it to a date before numbering; otherwise each event becomes its own row and its own island. Pick the time zone on purpose: a login at 23:50 in 1 zone is a login on the next day in another, and a streak that crosses midnight depends on which one you chose.

The subtraction itself is engine specific. In PostgreSQL, subtracting an integer from a date returns a date and subtracting 2 dates returns a number of days, which is why the query casts ROW_NUMBER, a bigint, to int first. Other engines use a date function for the same step, such as shifting the date back by the row number in days. The same trick works on plain numbers: for consecutive ids or years, id - ROW_NUMBER() OVER (ORDER BY id) needs no date function.

The gaps are the other half of the question, and they need no grouping column at all. LEAD reads the next date within the same user, so each row can compare itself with its successor. When the next date is more than 1 day later, the days in between are missing. The gap starts the day after the current row and ends the day before the next one.

On the example days, that returns 2 gaps. The first covers days 4 to 4 and is 1 missing day; the second covers days 7 to 8 and is 2 missing days. The last row of each user has no successor, and LEAD returns NULL by default there, so the WHERE clause drops it. Once a user's streak is over, nothing after it is reported as a gap, which matches what most questions mean.

The row number trick assumes a break means exactly 1 missing step. Real questions change the rule: a session ends after 30 minutes of silence, a streak survives 1 missed day, an order series resets when the price changes. For those, mark each row that starts a new island and number the islands with a running sum.

The LAG of the timestamp gives the previous event. A CASE expression returns 1 when the gap to it exceeds the limit and 0 otherwise, and the first row of each user, where LAG is NULL, falls into the 0 branch. A SUM of the flags ordered by time then counts how many breaks came before each row, which is the island number. Events in the same island share a number, and grouping by it gives each session's start and end, while counting its rows gives the size.

Rows with identical timestamps are peers, and an ordered aggregate with the default frame gives all of them the same running total, so ties stay in 1 session. Change the threshold and the same shape answers a different question, for instance > 2 on whole days allows 1 missed day inside a streak.

Sometimes the sequence has no missing step at all, and a run is a stretch of rows with the same value, such as a device that is online, online, offline, online. The row number difference still works, with a second numbering. Number each user's rows in time order, number them again inside each user and status, and subtract the 2 numbers.

Within a run, both counters advance together, so their difference holds. When the status changes, the overall counter keeps climbing while the per-status counter does not, and the difference moves. Group by the difference within each user and status to get each run and its length.

Streak questions usually add 1 more step on top. The current streak is the island whose end is today or yesterday, the longest streak is the MAX of the island length, and a streak of at least N days is a HAVING COUNT(*) >= N on the grouped result.

The question is rarely named. It arrives as consecutive logins or the longest streak of profitable months, and sometimes as uninterrupted employment spells or the periods a subscription was active. The tell is a request for a run of rows with a start and an end, or a count of consecutive anything. Say out loud that you recognise it, then state what must be equal inside a run and different across runs.

Check the data before you write the query. Ask whether a user can have several rows per day, whether the column is a date or a timestamp and in which time zone, whether ties are possible and what counts as a break. Those 4 answers decide whether you need the DISTINCT or the truncation, which DENSE_RANK fits, and where the threshold sits; asking them first is what interviewers read as production experience.

The catalogue holds a hard SQL problem on exactly this pattern, Consecutive Cost Growth Periods, which asks for 5 straight months of spending increases. The SQL practice problems page has it with the editor, and the SQL interview questions page covers the rest of the round. For the windows underneath this pattern, read the SQL window functions interview questions, and for how the round is run see the SQL round guide.

Forgetting PARTITION BY numbers all users as 1 long sequence, and a streak can leak from the end of 1 user into the start of the next. Ordering by something other than the date gives a grouping value that is stable by accident on small data and wrong on real data. Both fail silently, so test the query on 2 users whose dates interleave.

Putting the island expression in a WHERE clause fails because a window function cannot appear there. Compute it in a CTE or subquery, then group in the next step. Using COUNT(*) as the streak length is fine after deduplication and wrong before it, and measuring a streak as end minus start misses the days inside it if a date is absent, so count the rows.

For more practice with the building blocks, the SQL window functions practice page drills ROW_NUMBER and RANK one at a time, and also LAG. Advanced SQL covers the rest of the pattern family.