I gave 40 candidates this puzzle: get the top N records per group from a student marks table. Here is the simple answer using a Ranking Function.
How to Find Median in SQL Server? – Interview Question of the Week #116
There is no MEDIAN function in T-SQL. Here is an easy way to find median values in SQL Server 2012 and later, plus what median really means.
Top N per Group: Three Ways and Which Is Fastest
Compare top N per group with ROW_NUMBER, APPLY and a correlated subquery, define tie behavior, and measure plans with a supporting index.
Gaps and Islands: Finding Missing Ranges in a Sequence
Solve gaps and islands with LEAD, ROW_NUMBER, and GENERATE_SERIES, then apply the same date patterns to missing days and login streaks.
First and Repeat Purchases: Tagging Customer Orders With ROW_NUMBER
Tag repeat purchases with deterministic order numbering, calculate days to the second order, and compare customer cohorts fairly.
LAG and LEAD: Comparing Each Row With the One Before It
Use LAG and LEAD for monthly comparisons, handle defaults and percent change, choose explicit frames, and detect status transitions.
MySQL – Reset Row Number for Each Group – Partition By Row Number
MySQL has no ROW_NUMBER() OVER (PARTITION BY), so here is how to reset row number for each group with a variable, using a simple example.