sql / intermediate
Snippet
Ranking Data with Window Functions Using DENSE_RANK
Window functions compute values across a set of table rows related to the current row without collapsing the output dataset. Unlike RANK(), DENSE_RANK() assigns consecutive integer rankings to ordered partition partitions without leaving gaps when multiple rows share equal values.
snippet.sql
sql
1
2
3
SELECT employee_id, department_id, salary,DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rankFROM employees;
Breakdown
1
SELECT employee_id, department_id, salary,
Selects standard employee details alongside the calculated window function column.
2
DENSE_RANK() OVER (
Invokes DENSE_RANK to assign consecutive rank numbers without skipping values on ties.
3
PARTITION BY department_id
Divides the dataset into independent partitions per department.
4
ORDER BY salary DESC) AS salary_rank
Orders rows within each partition by salary descending and assigns the column alias.
5
FROM employees;
Specifies the source table for the query.