Detect Consecutive Reporting Streaks
Use ROW_NUMBER in a CTE and the period_no minus row number gaps-and-islands key to collapse each streak.
- CTEs
- Window functions
- Subqueries
- Aggregation
- Sorting
Exercise brief
Understand the request
Data reliability manager Missing reporting periods split each team’s coverage into consecutive streaks.
Missing reporting periods split each team’s coverage into consecutive streaks. Use ROW_NUMBER in a CTE and the period_no minus row number gaps-and-islands key to collapse each streak.
Return
- Return team_name, streak_start, streak_end, and periods_in_streak.
- Order by team_name and streak_start.
Constraints
- Number rows independently for each team by period_no.
- Group by team_name and the derived island key.
- Do not hard-code the known missing periods.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
team_metrics
snapshot_idINTEGERteam_nameVARCHAR(40)period_noINTEGERrevenueINTEGERtickets_closedINTEGERquality_scoreINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
For consecutive integers, period_no - ROW_NUMBER stays constant inside an island.
Hint 2
Calculate row_num before deriving and grouping by the island key.
Hint 3
Aggregate each team-and-island group to its minimum, maximum, and count.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
WITH numbered AS (SELECT team_name, period_no, ROW_NUMBER() OVER (PARTITION BY team_name ORDER BY period_no, snapshot_id) AS row_num FROM team_metrics), islands AS (SELECT team_name, period_no, period_no - row_num AS island_key FROM numbered) SELECT team_name, MIN(period_no) AS streak_start, MAX(period_no) AS streak_end, COUNT(*) AS periods_in_streak FROM islands GROUP BY team_name, island_key ORDER BY team_name, streak_start;Why this works
The difference key converts an ordered sequence problem into an ordinary grouped aggregation without relying on fixture-specific IDs.
Success check
Each uninterrupted run becomes one row and every fixture gap starts a new streak.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| team_name | streak_start | streak_end | periods_in_streak |
|---|---|---|---|
| alpha | 1 | 3 | 3 |
| alpha | 5 | 6 | 2 |
| beta | 1 | 2 | 2 |
| beta | 4 | 6 | 3 |
| gamma | 2 | 4 | 3 |
| gamma | 6 | 6 | 1 |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
Ranking & NTH Value
Solve deterministic ranking, top-N, distribution, positional-frame, and rolling-window problems.
SQL Subqueries
Practice scalar, derived-table, correlated, EXISTS, NULL-safe anti-subquery, quantified, and row-subquery patterns.
Self Joins & Hierarchical Queries
Query organization charts, trees, and parent-child relationships.
Open the interactive workspace and practice across SQL topics.