SQL Topic
Window Functions
Window functions compute a value across a set of rows related to the current row without collapsing them into a single output row. They are the workhorse of modern SQL interviews: ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, and aggregate functions used with OVER.
When is Window Functions used?
Whenever the answer needs a per-group ranking, the previous or next row, a running total, a moving average, or a share of a total. Interviewers use them to test whether you can express row-level context in SQL.
Core syntax
function_name() OVER (PARTITION BY col ORDER BY col [ROWS BETWEEN frame]) — e.g. ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC).Common mistakes
- Confusing RANK (leaves gaps after ties) with DENSE_RANK (no gaps).
- Forgetting PARTITION BY, which makes the window span the whole table.
- Applying WHERE to window results instead of wrapping in a subquery.
- Missing a deterministic ORDER BY tiebreaker inside OVER.
Practice Window Functions questions
Work through them in order — each question builds on the last.
- easyRising Temperature
- mediumConsecutive Login Days
- mediumConsecutive Numbers
- mediumFirst and Last Order Per Customer
- mediumGame Play Analysis IV
- mediumImmediate Food Delivery
- mediumLatest Event Per User
- mediumLongest Login Streak
- mediumMonthly Sales Ranking
- mediumNth Highest Salary
- mediumOrders Gap Analysis
- mediumPercentage of Total Sales
- mediumRank Scores
- mediumRunning Total
- mediumSeven-Day Rolling Average
- hardTop Three Per Category