Gaps and Islands: Beginner

Each of these phrasings asks about a group of rows that is defined by what the rows next to it look like. A user's 3-day streak is a streak because the 3 dates sit side by side with nothing missing between them. Remove the middle date and the same 2 remaining rows become 2 streaks of 1. That dependence on neighbours is the mark of the pattern: no single row carries enough information to say which group it belongs to, so a WHERE clause cannot find the group and a plain GROUP BY has nothing to group on.

SQL has no keyword for "consecutive". GROUP BY collapses rows that share a value in a column, and a table of dates has no column whose value is shared along a streak. The database sees 1, 2, 3, 7, 8, 12 as 6 numbers with no idea that the first 3 belong together. The whole pattern is the work of inventing that column: a value that is identical for every row in a run and different for rows in other runs, computed from the order of the rows, so that an ordinary GROUP BY can finish the job.

An island is a maximal stretch of rows with no break between any 2 neighbours. A gap is the empty space between 2 islands: the values that would have continued the sequence and are missing. In the sequence 1, 2, 3, 7, 8, 12 there are 3 islands and 2 gaps, and the gaps are made of values that appear in no row at all, which is the part that trips people up. You cannot SELECT a gap, because the gap has no rows; the only thing the query can do is find where each island stops.

A strong answer states the 3 facts the definition pins down before any SQL: what the sequence column is, what the expected step is, and whether islands of 1 belong in the output. "The sequence is login_date, the step is 1 day, and a single login is a streak of 1 unless you tell me otherwise." Every later choice, including the exact arithmetic of the group key and the aggregates at the end, follows from those 3 facts, so stating them first lets the interviewer check your plan before you commit to it.

A table has no order. The rows of logins come back in whatever order the storage engine finds convenient, and 2 runs of the same query can return them differently. A run is a statement about which row comes after which, so until the query has imposed an order on the rows inside each user there are no runs to find: there is a bag of dates. ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) is the step that turns the bag into a sequence, and every later step depends on it being right.

The ORDER BY inside the OVER clause is the only one that matters to the run. It tells the engine the sequence the row numbers follow, within each partition, independent of how the rows are stored and of any ORDER BY at the end of the query. The final ORDER BY arranges the output for a human and has no effect on the numbering. Two different orders can appear in 1 query without conflict: number by login_date, then sort the result by streak_length DESC.

The window runs after the WHERE clause, so a filter such as only mobile logins is applied before the numbering and the row numbers count only the kept rows. Apply the same filter in an outer query after the numbering and the removed rows take their row numbers with them: if Alice's March 2 login came from a laptop, March 1 keeps row number 1 and March 3 keeps row number 3, both give the same difference, and 2 days that are not consecutive merge into 1 streak. The row number skips exactly where the date skips, which hides the gap, so every filter on which rows take part belongs before the window, in the same ranked stage.

When the interviewer asks "what if 2 users log in on the same date", the answer is that the partition keeps them apart: Alice's March 2 and Bob's March 2 each take the number their own sequence gives them. The trouble starts when a single user has 2 rows on the same date, which the next lesson in this track covers in full. Here, say that you would use SELECT DISTINCT user_id, login_date to reduce the table to 1 row per user per day before numbering, then move on.

Watch the 6 rows of sequence_data, which hold 1 through 3, then jump to 7 and 8, then to 12. Row numbers land as 1 through 6. n - rn gives 0 on each of the first 3 rows and 3 on the rows for 7 and 8; the row for 12 gets 6. That is 3 distinct differences and 3 islands. The gap from 3 to 7 skipped 3 values and the difference moved from 0 to 3; the gap from 8 to 12 skipped 3 more and it moved from 3 to 6. The size of each jump in the key equals the number of missing values, which is a useful check when you trace by hand.

Read the CTEs top down and they name the steps: ranked numbers the rows, grouped computes the key, and the final SELECT collapses each key into 1 row with MIN for the start, MAX for the end and COUNT(*) for the length. Point at island_id when you trace it and say "this column is identical for every row in a run, which is what lets GROUP BY fold the run into 1 row". The output has as many rows as there are islands, 3 here, never as many as the input.

COUNT(*) and MAX(n) - MIN(n) + 1 both give 3 for the first island, and the interviewer may ask which you would use. On clean data they always agree, so pick COUNT(*) because it reads as "rows in the run". Neither one catches a duplicated value: in 1, 2, 2, 3 the second 2 takes its own row number, the differences come out 0, 0, -1, -1, and the run splits in 2, with an island from 1 to 2 overlapping one from 2 to 3. Count and span both say 2 for each. The defence is the SELECT DISTINCT from section 2, which leaves 1 row per value before the numbering starts.

The keys in the example (0, 3, 6) are neither run lengths nor run starts, and no consumer should ever see them: they only happen to stay constant along a run. Drop island_id from the final SELECT in production and keep it only while debugging. Two users can produce the same label for unrelated runs, since each user's row numbers restart at 1, which is why the GROUP BY always carries the entity column beside the key; the next lesson in this track works through that pairing, and here the rule is to never group by the key alone when there is more than 1 entity.

Volunteer the caveat about the label unprompted: it tells the interviewer you derived the trick rather than copied it.

A date minus an integer needs the integer turned into days, so the subtraction becomes login_date - rn * INTERVAL '1 day', and the key is a date that stays the same along a streak. Alice's logins from March 1 to March 3 carry row numbers 1 to 3, so every one of them gives February 29, while her March 5 with row number 4 gives March 1, a different key. The query returns a 3-day streak and a 1-day streak for her and a 2-day streak for Bob. Everything else about the query is identical, and the calendar details of the date form are the next lesson's subject.

The follow-up is always "now give me the longest one per user". Wrap the streaks in a CTE and take MAX(streak_length) grouped by user_id for the number alone; returning the dates of that streak means picking a row, and the tie between 2 equal streaks is the awkward case the next section sketches.

The tie is the one that costs offers. ORDER BY island_length DESC LIMIT 1 returns A or C depending on which the engine happens to place first, and an interviewer who built the example put the tie in on purpose. Ask "should ties at the longest length both come back, or do you want exactly 1 row?" If both, filter the runs to the ones whose length equals the maximum, as the query below does; if 1, add a deterministic tiebreak such as the earliest start to the ORDER BY before the LIMIT.

runs here is the output of section 3's query over the 9 values above, 1 row per island. The scalar subquery finds that the largest length is 3, and WHERE keeps every run that long, so the runs starting at 1 and at 12 both come back while 7 to 8 and 20 drop out. With 1 run alone at the top, the same query returns 1 row, so it is correct in both cases without changing anything.

When the interviewer hands you a sample with 2 users, sketch each user separately, because the partition is what the sketch is checking. A sketch that numbers both users in 1 column reproduces the missing-partition bug by hand, and watching a letter change in the middle of a real streak on the whiteboard is the fastest way to show why the query needs PARTITION BY. If the sample has a user with 1 login, trace that user too: an island of 1 row is where most "too many rows" complaints come from, and it takes 1 line to show that the query is right and the question was underspecified.

The sketch also tells you what to type last. Once the letters are down, the aggregates write themselves: the smallest value under each letter is the start, the largest is the end, and the count of rows is the length. If the question asked for a minimum streak of 2, cross out D on the sketch and add the HAVING that does the same in the query. Everything the interviewer sees on the board matches a line in the SQL, which is what makes the trace convincing.