S06 · interview simulation

SQL gym: consecutive nearly-full streaks and why the first query lies

People, data and outputs in this scenario are simulated. Each stage is recorded when you commit; the reveal comes after.

5stages
35 minabout
0stages committed

The brief

A live SQL interview (simulated). The interviewer gives you the course's synthetic flight_day table (one row per flight and date, 2022-01-01 to 2024-12-31, route, date as ISO text, flight_no, load_factor) in SQLite.

The question: "For each route, find the longest streak of consecutive calendar dates on which at least one of its flights had load_factor of 0.90 or more. Return the route, the start date, the end date and the length in days." A route has one to three flights per day. The table has no missing dates.

Stage 1 of 5

The interviewer says: "Talk me through the approach before you write anything. Keep it portable; assume window functions are available."

Which approach do you take?

Collapse to one row per route and date, keep days at or above 0.90, subtract the row number within the route from the day number, and group by the difference.Use a recursive CTE that starts from each qualifying day and walks forward one day at a time while the next day also qualifies, then take the longest run; it is easy to reason about and stops at the first gap.Count the qualifying days per route and report the route with the largest count as the longest streak, which needs only a single GROUP BY and a MAX and is easy to check by eye.Use `LAG(date)` to compute the gap between each qualifying day and the previous one, and report the largest gap, which needs only one window function.