LeetCode SQL - Rank

Posted on Edited on

RANK

DENSE_RANK

176. Second Highest Salary

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
DROP TABLE IF EXISTS Employee;
CREATE TABLE
    If NOT EXISTS Employee (Id INT, Salary INT);
TRUNCATE TABLE Employee;
INSERT INTO
    Employee (Id, Salary)
VALUES
    ('1', '100');
-- insert into Employee (Id, Salary) values ('2', '100');
INSERT INTO
    Employee (Id, Salary)
VALUES
    ('2', '200');
INSERT INTO
    Employee (Id, Salary)
VALUES
    ('3', '300');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
/*Select the second highest salary
- Salary PK: Id

- Missing values? Show NULL

- SOLUTION: window function, DENSE_RANK
 */
WITH
    cte AS (
        SELECT
            salary,
            DENSE_RANK() OVER (
                ORDER BY
                    salary desc
            ) AS salary_dr
        FROM
            Employee
    )
SELECT
    MAX(salary) AS SecondHighestSalary -- to avoid empty query and duplicate values
FROM
    cte
WHERE
    salary_dr = 2;

177. Nth Highest Salary

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
DROP TABLE IF EXISTS Employee;
CREATE TABLE
    If NOT EXISTS Employee (Id INT, Salary INT);
TRUNCATE TABLE Employee;
INSERT INTO
    Employee (Id, Salary)
VALUES
    ('1', '100');
-- insert into Employee (Id, Salary) values ('2', '100');
INSERT INTO
    Employee (Id, Salary)
VALUES
    ('2', '200');
INSERT INTO
    Employee (Id, Salary)
VALUES
    ('3', '300');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- Select the nth highest salary
-- Q. How to handle missing values? Show null
-- Q. DENSE_RANK or RANK? DENSE_RANK
CREATE FUNCTION getNthHighestSalary (n INT) RETURNS INT BEGIN RETURN (
    WITH
        cte AS (
            SELECT
                salary,
                DENSE_RANK() OVER (
                    ORDER BY
                        salary desc
                ) AS salary_dr
            FROM
                Employee
        )
    SELECT
        MAX(salary)
    FROM
        cte
    WHERE
        salary_dr = n
);
END

185. Department Top Three Salaries

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
DROP TABLE IF EXISTS Employee;
DROP TABLE IF EXISTS Department;
CREATE TABLE
    If NOT EXISTS Employee (Id INT, Name VARCHAR(255), Salary INT, DepartmentId INT);
CREATE TABLE
    If NOT EXISTS Department (Id INT, Name VARCHAR(255));
TRUNCATE TABLE Employee;
INSERT INTO
    Employee (Id, Name, Salary, DepartmentId)
VALUES
    ('1', 'Joe', '85000', '1');
INSERT INTO
    Employee (Id, Name, Salary, DepartmentId)
VALUES
    ('2', 'Henry', '80000', '2');
INSERT INTO
    Employee (Id, Name, Salary, DepartmentId)
VALUES
    ('3', 'Sam', '60000', '2');
INSERT INTO
    Employee (Id, Name, Salary, DepartmentId)
VALUES
    ('4', 'Max', '90000', '1');
INSERT INTO
    Employee (Id, Name, Salary, DepartmentId)
VALUES
    ('5', 'Janet', '69000', '1');
INSERT INTO
    Employee (Id, Name, Salary, DepartmentId)
VALUES
    ('6', 'Randy', '85000', '1');
INSERT INTO
    Employee (Id, Name, Salary, DepartmentId)
VALUES
    ('7', 'Will', '70000', '1');
TRUNCATE TABLE Department;
INSERT INTO
    Department (Id, Name)
VALUES
    ('1', 'IT');
INSERT INTO
    Department (Id, Name)
VALUES
    ('2', 'Sales');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
/*Select top three salaries for each department
- Employee PK: Id
- Department PK: Id

- Solution: window function, DENSE_RANK
 */
WITH
    cte AS (
        SELECT
            d.name AS Department,
            e.name AS Employee,
            Salary,
            DENSE_RANK() OVER (
                PARTITION BY
                    d.id
                ORDER BY
                    Salary desc
            ) AS Salary_dr
        FROM
            Employee e
            INNER JOIN Department d ON e.departmentId = d.id
    )
SELECT
    Department,
    Employee,
    Salary
FROM
    cte
WHERE
    Salary_dr <= 3;

184. Department Highest Salary

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
DROP TABLE IF EXISTS Employee;
DROP TABLE IF EXISTS Department;
CREATE TABLE
    If NOT EXISTS Employee (Id INT, Name VARCHAR(255), Salary INT, DepartmentId INT);
CREATE TABLE
    If NOT EXISTS Department (Id INT, Name VARCHAR(255));
TRUNCATE TABLE Employee;
INSERT INTO
    Employee (Id, Name, Salary, DepartmentId)
VALUES
    ('1', 'Joe', '70000', '1');
INSERT INTO
    Employee (Id, Name, Salary, DepartmentId)
VALUES
    ('2', 'Jim', '90000', '1');
INSERT INTO
    Employee (Id, Name, Salary, DepartmentId)
VALUES
    ('3', 'Henry', '80000', '2');
INSERT INTO
    Employee (Id, Name, Salary, DepartmentId)
VALUES
    ('4', 'Sam', '60000', '2');
INSERT INTO
    Employee (Id, Name, Salary, DepartmentId)
VALUES
    ('5', 'Max', '90000', '1');
TRUNCATE TABLE Department;
INSERT INTO
    Department (Id, Name)
VALUES
    ('1', 'IT');
INSERT INTO
    Department (Id, Name)
VALUES
    ('2', 'Sales');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
/*Select the heighest Salary in each departments
- Employee PK: Id
- Department PK: Id

- Missing values? ignore

- Soution: window function; dense_rank()
 */
WITH
    cte AS (
        SELECT
            d.name AS Department,
            e.name AS Employee,
            Salary,
            DENSE_RANK() OVER (
                PARTITION BY
                    d.id
                ORDER BY
                    salary desc
            ) AS salary_dr
        FROM
            Employee e
            INNER JOIN Department d ON e.departmentId = d.id
    )
SELECT
    Department,
    Employee,
    Salary
FROM
    cte
WHERE
    salary_dr = 1;

178. Rank Scores

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
DROP TABLE IF EXISTS Scores;
CREATE TABLE
    If NOT EXISTS Scores (Id INT, Score DECIMAL(3, 2));
TRUNCATE TABLE Scores;
INSERT INTO
    Scores (Id, Score)
VALUES
    ('1', '3.5');
INSERT INTO
    Scores (Id, Score)
VALUES
    ('2', '3.65');
INSERT INTO
    Scores (Id, Score)
VALUES
    ('3', '4.0');
INSERT INTO
    Scores (Id, Score)
VALUES
    ('4', '3.85');
INSERT INTO
    Scores (Id, Score)
VALUES
    ('5', '4.0');
INSERT INTO
    Scores (Id, Score)
VALUES
    ('6', '3.65');
1
2
3
4
5
6
7
8
9
10
11
12
13
/* Rank the scores
- Scores PK: Id

- SOLUTION: DENSE_RANK
 */
SELECT
    score,
    DENSE_RANK() OVER (
        ORDER BY
            score desc
    ) AS `rank`
FROM
    Scores;

1412. Find the Quiet Students in All Exams

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
DROP TABLE IF EXISTS Student;
DROP TABLE IF EXISTS Exam;
CREATE TABLE
    If NOT EXISTS Student (student_id INT, student_name VARCHAR(30));
CREATE TABLE
    If NOT EXISTS Exam (exam_id INT, student_id INT, score INT);
TRUNCATE TABLE Student;
INSERT INTO
    Student (student_id, student_name)
VALUES
    ('1', 'Daniel');
INSERT INTO
    Student (student_id, student_name)
VALUES
    ('2', 'Jade');
INSERT INTO
    Student (student_id, student_name)
VALUES
    ('3', 'Stella');
INSERT INTO
    Student (student_id, student_name)
VALUES
    ('4', 'Jonathan');
INSERT INTO
    Student (student_id, student_name)
VALUES
    ('5', 'Will');
TRUNCATE TABLE Exam;
INSERT INTO
    Exam (exam_id, student_id, score)
VALUES
    ('10', '1', '70');
INSERT INTO
    Exam (exam_id, student_id, score)
VALUES
    ('10', '2', '80');
INSERT INTO
    Exam (exam_id, student_id, score)
VALUES
    ('10', '3', '90');
INSERT INTO
    Exam (exam_id, student_id, score)
VALUES
    ('20', '1', '80');
INSERT INTO
    Exam (exam_id, student_id, score)
VALUES
    ('30', '1', '70');
INSERT INTO
    Exam (exam_id, student_id, score)
VALUES
    ('30', '3', '80');
INSERT INTO
    Exam (exam_id, student_id, score)
VALUES
    ('30', '4', '90');
INSERT INTO
    Exam (exam_id, student_id, score)
VALUES
    ('40', '1', '60');
INSERT INTO
    Exam (exam_id, student_id, score)
VALUES
    ('40', '2', '70');
INSERT INTO
    Exam (exam_id, student_id, score)
VALUES
    ('40', '4', '80');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
/*SELECT the students who are in the middle of the rank of ALL exams
- Student PK: student_id
- Exam PK: (exam_id, student_id)

- SOLUTION: DENSE_RANK

- Step 1: Dense rank the students with ascending and descending for each exam
 */
WITH
    CTE AS (
        SELECT
            e.exam_id,
            e.student_id,
            s.student_name,
            e.score,
            DENSE_RANK() OVER (
                PARTITION BY
                    exam_id
                ORDER BY
                    score ASC
            ) AS score_DR_ASC,
            DENSE_RANK() OVER (
                PARTITION BY
                    exam_id
                ORDER BY
                    score DESC
            ) AS score_DR_DESC
        FROM
            Exam e
            INNER JOIN Student s ON e.student_id = s.student_id
    )
-- SELECT * FROM CTE;
-- Step 2: Select the quiet student
SELECT DISTINCT
    student_id,
    student_name
FROM
    CTE
WHERE
    student_id NOT IN (
        SELECT
            student_id
        FROM
            CTE
        WHERE
            score_DR_ASC = 1
            OR score_DR_DESC = 1
    )
ORDER BY
    student_id;

1076. Project Employees II

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
/*Query the project has most employees
- Project PK: (project_id, employee_id)
- Employee PK: employee_id
 */
WITH
    CTE AS (
        SELECT
            project_id,
            DENSE_RANK() OVER (
                ORDER BY
                    COUNT(employee_id) DESC
            ) AS n_employee_DR
        FROM
            Project
        GROUP BY
            project_id
    )
SELECT
    project_id
FROM
    CTE
WHERE
    n_employee_DR = 1;

1077. Project Employees III

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
/*Query the the most experienced employees in each project
- Project PK: (project_id, employee_id)
- Employee PK: employee_id
*/
WITH CTE AS
    (
    SELECT 
        p.project_id,
        p.employee_id,
        DENSE_RANK() OVER(PARTITION BY p.project_id ORDER BY e.experience_years DESC) AS experience_DR
    FROM Project p
    INNER JOIN Employee e
        USING(employee_id)
    )
SELECT project_id, employee_id
FROM CTE
WHERE experience_DR = 1;

1951. All the Pairs With the Maximum Number of Common Followers

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
DROP TABLE if EXISTS Relations;
CREATE TABLE
    If NOT EXISTS Relations (user_id INT, follower_id INT);
TRUNCATE TABLE Relations;
INSERT INTO
    Relations (user_id, follower_id)
VALUES
    ('1', '3');
INSERT INTO
    Relations (user_id, follower_id)
VALUES
    ('2', '3');
INSERT INTO
    Relations (user_id, follower_id)
VALUES
    ('7', '3');
INSERT INTO
    Relations (user_id, follower_id)
VALUES
    ('1', '4');
INSERT INTO
    Relations (user_id, follower_id)
VALUES
    ('2', '4');
INSERT INTO
    Relations (user_id, follower_id)
VALUES
    ('7', '4');
INSERT INTO
    Relations (user_id, follower_id)
VALUES
    ('1', '5');
INSERT INTO
    Relations (user_id, follower_id)
VALUES
    ('2', '6');
INSERT INTO
    Relations (user_id, follower_id)
VALUES
    ('7', '5');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
WITH
    cte AS (
        SELECT
            r1.user_id AS user1_id,
            r2.user_id AS user2_id,
            DENSE_RANK() OVER (
                ORDER BY
                    COUNT(r1.follower_id) desc
            ) AS dr_common_followers
        FROM
            Relations r1
            INNER JOIN Relations r2 ON r1.follower_id = r2.follower_id
            AND r1.user_id < r2.user_id
        GROUP BY
            r1.user_id,
            r2.user_id
        ORDER BY
            3 desc
    )
SELECT
    user1_id,
    user2_id
FROM
    cte
WHERE
    dr_common_followers = 1;