Advanced Window Functions
RANK, DENSE_RANK, NTILE, and frame clauses.
Questions this answers
- →What's the difference between ROW_NUMBER, RANK, and DENSE_RANK?
- →How do I split rows into percentile buckets?
- →How do I compute a rolling 7-day average?
- →How do I get the first value in a partition?
How it works
Beyond the basics, window functions have a richer set of ranking functions and frame clauses that let you precisely control which rows are included in each computation.
RANK vs DENSE_RANK
SELECT name, score, RANK() OVER (ORDER BY score DESC) AS rank, DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank FROM results;
💡 RANK skips numbers after ties (1,2,2,4). DENSE_RANK doesn't (1,2,2,3).
NTILE — percentile buckets
SELECT name, monthly_listeners, NTILE(4) OVER (ORDER BY monthly_listeners) AS quartile FROM artists;
Rolling average with frame clause
SELECT date, revenue,
AVG(revenue) OVER (
ORDER BY date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS rolling_7day_avg
FROM daily_revenue;Ready to practise Advanced Window Functions?
Solve real exercises with AI feedback — free to start.
Try it free →