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.

All difficulty levels