SQL Practice
Hard SQL Exercises
The challenging set. These problems need window functions, recursive CTEs, and the classic analytics patterns that come up in senior interviews: gaps and islands, top-N per group, running totals, medians and pivots. Try hard before you reveal the solution.
Hard
15 problems
Topic:
1
Find the Nth highest salary
Generalise second highest to any N.
Analytics Patterns
2
Running total of sales
Accumulate with SUM OVER and a window frame.
Window Functions
3
Top 3 products per category
Rank within groups and filter to the top N.
Window Functions
4
Delete duplicate rows keeping one
De-duplicate with ROW_NUMBER in a CTE.
Analytics Patterns
5
Find consecutive ranges (gaps and islands)
Collapse consecutive rows into ranges.
Analytics Patterns
6
Month over month growth
Compare each period to the previous with LAG.
Window Functions
7
Calculate the median salary
Find the median with PERCENTILE_CONT.
Analytics Patterns
8
Walk an employee hierarchy
Traverse a tree with a recursive CTE.
CTEs & Recursion
9
Each product as a percent of its category
Divide a row by a partitioned SUM OVER.
Window Functions
10
Longest consecutive login streak
A gaps and islands variant over dates.
Analytics Patterns
11
Pivot sales into month columns
Turn rows into columns with PIVOT or CASE.
Analytics Patterns
12
Three day moving average
Average over a sliding window frame.
Window Functions
13
Days between each customer order
Difference each row from the previous with LAG.
Window Functions
14
Highest paid employee per department
One row per group with a partitioned ranking.
Window Functions
15
Cumulative count of new customers
Count first-seen customers over time.
CTEs & Recursion
No problems match this topic.