SQL Query Patterns Quiz: Gaps and Islands, Pivots, Nth Highest
An SQL query patterns practice test: 12 questions and three queries on gaps and islands, deduplication, pivots, rolling windows, nth highest and date series.
- Course: SQL study plan
- Module: Query patterns
- Kind: Checkpoint — cleared at 70%
- Reading time: 30 min
- Runtime: MySQL 8 syntax on SQLite 3.49
Checkpoint — Query patterns is the checkpoint that closes the Query patterns module: a graded quiz and whole-program exercises, passed at 70%.
Instructions
The checkpoint for query patterns: the shapes that reporting and interview questions take once the individual clauses are known — gaps and islands, deduplication, pivots, rolling windows, the nth highest value and date series.
How it works. Twelve questions and three queries; 70% on the questions and every query accepted clears the module.
Before you start, make sure you can answer these from memory:
- Why
date − ROW_NUMBER()is constant along a run of consecutive dates, and what a duplicate date does to it. - How LAG with a running SUM numbers islands when "consecutive" means something other than one step.
- How to find the gaps in a sequence with LEAD.
- How to keep one row per duplicate group, and why MySQL needs a derived table to delete the rest (error 1093).
- How SUM(CASE WHEN …) pivots rows into columns, why ELSE 0 matters, and how UNION ALL unpivots.
- The difference between a ROWS frame and a RANGE frame, and why a ROWS frame is not a 7-day window over missing dates.
- Why DENSE_RANK, not RANK, finds the nth highest distinct value, and how a scalar subquery makes "none" a NULL.
- How a recursive CTE builds a calendar, the 1,000-iteration limit, and why
COUNT(o.id)andCOALESCEfollow the LEFT JOIN.
The three queries combine the patterns the way real reports do: each student's longest attendance streak (islands, then a maximum per student), a monthly ticket report with a column per status and the empty months filled in (a month series and a pivot together), and every department's top two pay levels side by side with the gap between them (the nth highest value per group, pivoted into columns).
Common questions
How do you group consecutive dates into streaks in SQL?
Number each user's distinct dates with ROW_NUMBER() and subtract that many days from the date. Consecutive dates share the result, so grouping by user and that value gives one row per streak with its first day, last day and length.
Why does a ROWS frame not give a 7-day window when dates are missing?
A ROWS frame counts rows, not days, so 6 PRECEDING can reach back more than six days when some days have no row, and several rows on one day each get their own frame. Aggregate to one row per day and fill the calendar first, or frame on the date with RANGE.
Why does COUNT(*) report 1 for an empty day after a LEFT JOIN to a calendar?
The LEFT JOIN keeps the calendar row and fills the data columns with NULL, so the group holds one row. COUNT(*) counts rows and reports 1; COUNT(column) from the joined table counts non-NULL values and reports 0.
Exercises
Longest attendance streak
attendance records each day a student attended; a day may have been recorded twice.
| student | day |
|---|---|
| Ana | 2024-09-02 |
| Ana | 2024-09-03 |
| Ana | 2024-09-04 |
| Ana | 2024-09-06 |
| Ben | 2024-09-02 |
| Ben | 2024-09-04 |
| … | … |
For each student, return student and longest_streak: the length in days of their longest run of consecutive attended days. Order by longest_streak descending, then student.
student | longest_streak
Cy | 4
Ana | 3
Dee | 2
Ben | 1Tickets per month by status
tickets has a status of open, pending or closed.
| id | opened_on | status |
|---|---|---|
| 1 | 2024-01-03 | open |
| 2 | 2024-01-15 | closed |
| 3 | 2024-01-20 | closed |
| 4 | 2024-03-02 | pending |
| 5 | 2024-03-09 | open |
| 6 | 2024-04-30 | closed |
Report every month from the month of the first ticket to the month of the last, inclusive — including months with no tickets — with the columns month (YYYY-MM), open, pending, closed (the number of tickets opened that month with that status) and total. Order by month.
month | open | pending | closed | total
2024-01 | 1 | 0 | 2 | 3
2024-02 | 0 | 0 | 0 | 0
2024-03 | 1 | 1 | 0 | 2
2024-04 | 0 | 0 | 1 | 1Top two pay levels per department
employees:
| id | name | dept | salary |
|---|---|---|---|
| 1 | Asha | eng | 120 |
| 2 | Bo | eng | 120 |
| 3 | Cara | eng | 100 |
| 4 | Dan | eng | 90 |
| 5 | Eli | ops | 70 |
| 6 | Fay | sales | 80 |
| 7 | Gus | sales | 95 |
| 8 | Hal | sales | 60 |
For every department, return dept, top_salary (the highest distinct salary), second_salary (the second-highest distinct salary, NULL if none) and gap (top_salary - second_salary, NULL if there is no second). Order by dept.
dept | top_salary | second_salary | gap
eng | 120 | 100 | 20
ops | 70 | NULL | NULL
sales | 95 | 80 | 15In this module: Query patterns
- Gaps and islands — finding runs and holes in ordered data
- Deduplication — finding, keeping and deleting duplicate rows
- Pivoting — turning rows into columns and back
- Rolling windows — moving averages, sliding sums and period-over-period
- The nth highest value — LIMIT OFFSET, DENSE_RANK and the NULL answer
- Date series — calendars from recursive CTEs and filling missing days
- Checkpoint — Query patterns (this lesson)
← Date series — calendars from recursive CTEs and filling missing days · The classic SQL questions — six problems every interview reuses →