Description:
Results are one too high:
mysql> SELECT department_id, salary,
-> RANK() OVER w AS rnk,
-> DENSE_RANK() OVER w AS dense,
-> NTILE(4) over w AS ntil,
-> CUME_DIST() OVER w AS cume,
-> PERCENT_RANK() over w AS `%rnk`
-> FROM employee
-> WINDOW w AS (PARTITION BY department_id
-> ORDER BY salary DESC)
-> ORDER BY department_id, salary DESC;
+---------------+--------+-----+-------+------+--------------------+------+
| department_id | salary | rnk | dense | ntil | cume | %rnk |
+---------------+--------+-----+-------+------+--------------------+------+
| NULL | 75000 | 1 | 2 | 1 | 1 | 0 |
| 10 | 100000 | 1 | 2 | 1 | 0.2 | 0 |
| 10 | 70000 | 2 | 3 | 1 | 0.4 | 0.25 |
| 10 | 60000 | 3 | 4 | 2 | 0.8 | 0.5 |
| 10 | 60000 | 3 | 4 | 3 | 0.8 | 0.5 |
| 10 | NULL | 5 | 5 | 4 | 1 | 1 |
| 20 | 80000 | 1 | 2 | 1 | 0.3333333333333333 | 0 |
| 20 | 65000 | 2 | 3 | 2 | 1 | 0.5 |
| 20 | 65000 | 2 | 3 | 3 | 1 | 0.5 |
| 30 | 300000 | 1 | 2 | 1 | 0.5 | 0 |
| 30 | 70000 | 2 | 3 | 2 | 1 | 1 |
+---------------+--------+-----+-------+------+--------------------+------+
How to repeat:
CREATE TABLE employee(department_id INT, salary INT);
INSERT INTO employee VALUES (10, NULL),
(10, 100000),
(10, 60000),
(10, 60000),
(10, 70000),
(20, 80000),
(20, 65000),
(20, 65000),
(30, 300000),
(30, 70000),
(NULL, 75000);
SELECT department_id, salary,
RANK() OVER w AS rnk,
DENSE_RANK() OVER w AS dense,
NTILE(4) over w AS ntil,
CUME_DIST() OVER w AS cume,
PERCENT_RANK() over w AS `%rnk`
FROM employee
WINDOW w AS (PARTITION BY department_id
ORDER BY salary DESC)
ORDER BY department_id, salary DESC;
Description: Results are one too high: mysql> SELECT department_id, salary, -> RANK() OVER w AS rnk, -> DENSE_RANK() OVER w AS dense, -> NTILE(4) over w AS ntil, -> CUME_DIST() OVER w AS cume, -> PERCENT_RANK() over w AS `%rnk` -> FROM employee -> WINDOW w AS (PARTITION BY department_id -> ORDER BY salary DESC) -> ORDER BY department_id, salary DESC; +---------------+--------+-----+-------+------+--------------------+------+ | department_id | salary | rnk | dense | ntil | cume | %rnk | +---------------+--------+-----+-------+------+--------------------+------+ | NULL | 75000 | 1 | 2 | 1 | 1 | 0 | | 10 | 100000 | 1 | 2 | 1 | 0.2 | 0 | | 10 | 70000 | 2 | 3 | 1 | 0.4 | 0.25 | | 10 | 60000 | 3 | 4 | 2 | 0.8 | 0.5 | | 10 | 60000 | 3 | 4 | 3 | 0.8 | 0.5 | | 10 | NULL | 5 | 5 | 4 | 1 | 1 | | 20 | 80000 | 1 | 2 | 1 | 0.3333333333333333 | 0 | | 20 | 65000 | 2 | 3 | 2 | 1 | 0.5 | | 20 | 65000 | 2 | 3 | 3 | 1 | 0.5 | | 30 | 300000 | 1 | 2 | 1 | 0.5 | 0 | | 30 | 70000 | 2 | 3 | 2 | 1 | 1 | +---------------+--------+-----+-------+------+--------------------+------+ How to repeat: CREATE TABLE employee(department_id INT, salary INT); INSERT INTO employee VALUES (10, NULL), (10, 100000), (10, 60000), (10, 60000), (10, 70000), (20, 80000), (20, 65000), (20, 65000), (30, 300000), (30, 70000), (NULL, 75000); SELECT department_id, salary, RANK() OVER w AS rnk, DENSE_RANK() OVER w AS dense, NTILE(4) over w AS ntil, CUME_DIST() OVER w AS cume, PERCENT_RANK() over w AS `%rnk` FROM employee WINDOW w AS (PARTITION BY department_id ORDER BY salary DESC) ORDER BY department_id, salary DESC;