✅ Software engineer [ Javascript, Typescript, React, Node.js, Express.js, Tailwind CSS, Playwright ] | Building rock-solid solutions
I've been diving deeper into SQL and discovered something new and interesting to me: the difference between RANK() and DENSE_RANK() window functions. I was only aware of RANK(), but skipping ranking after ties bothered me. Here's a concise example of employees and their revenue contributions to the company: | employee_id | employee_name | revenue | | 1 | Alice | 5000 | | 2 | Bob | 7000 | | 3 | Charlie | 7000 | | 4 | David | 6000 | | 5 | Eva | 5000 | ------------------------------------------------------ Consider this query: SELECT employee_id, employee_name, revenue, RANK() OVER (ORDER BY revenue DESC) AS rank, DENSE_RANK() OVER (ORDER BY revenue DESC) AS dense_rank FROM employees; It provides us the result set of: | id | name | revenue | rank | dense_rank | 2 | Bob | 7000 | 1 | 1 | 3 | Charlie | 7000 | 1 | 1 | 4 | David | 6000 | 3 | 2 | 1 | Alice | 5000 | 4 | 3 | 5 | Eva | 5000 | 4 | 3 Do you see the difference? In this example, RANK() skips ranks after ties, whereas DENSE_RANK() does not. Understanding these differences can be crucial.
