Intermediate LevelSQL & MySQL
What are SQL Window Functions (ROW_NUMBER, RANK, DENSE_RANK, OVER)?
❓ Interview Prompt: Explain Window Functions, `OVER()` clause, `PARTITION BY`, `ORDER BY`, `ROW_NUMBER()`, `RANK()`, and `DENSE_RANK()` differences.
Window Functions perform calculations across a set of table rows related to the current row without collapsing rows into a single summary output (unlike 'GROUP BY'):
Ranking Functions Comparison:
•
'ROW_NUMBER()': Assigns a unique sequential integer to every row ($1, 2, 3, 4$).
•
'RANK()': Assigns identical rank to tied values, skipping subsequent ranks ($1, 2, 2, 4$).
•
'DENSE_RANK()': Assigns identical rank to tied values without skipping subsequent ranks ($1, 2, 2, 3$).
Code Implementation Example
sql
SELECT
id,
category_id,
price,
ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS price_row_num,
DENSE_RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS price_dense_rank
FROM products;💡Ranking category product prices using ROW_NUMBER and DENSE_RANK window functions.
🎯 Interview Key Takeaway
Use Window Functions with PARTITION BY to execute per-group ranking and calculations while retaining individual detail rows.
#SQL#Window Functions#ROW_NUMBER#RANK#DENSE_RANK#Intermediate
Back to all SQL & MySQL Questions→