LeetCode SQL - Rank

Table of contents

RANK

RANK is rarely used in LeetCode questions.

DENSE_RANK

178. Rank Scores

SELECT
    score,
    DENSE_RANK() OVER (ORDER BY score DESC) AS "rank"
FROM Scores

176. Second Highest Salary

SELECT
    (
        SELECT DISTINCT salary AS SecondHighestSalary
        FROM Employee
        ORDER BY salary DESC
        OFFSET 1
        LIMIT 1
    )

WITH salary_rank AS (
    SELECT
        DISTINCT salary,
        DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_desc
    FROM employee
)
SELECT (SELECT salary
FROM salary_rank
WHERE salary_desc = 2) AS SecondHighestSalary

177. Nth Highest Salary

CREATE OR REPLACE FUNCTION NthHighestSalary(N INT) RETURNS TABLE (Salary INT) AS $$
BEGIN
  RETURN QUERY (
    WITH salary_rank AS (
        SELECT
            e.salary,
            DENSE_RANK() OVER(ORDER BY e.salary DESC) AS salary_desc
        FROM Employee e
    )
    SELECT DISTINCT sr.salary
    FROM salary_rank sr
    WHERE sr.salary_desc = N
  );
END;
$$ LANGUAGE plpgsql;

185. Department Top Three Salaries

WITH department_salary_rank AS (
    SELECT
        id,
        "name",
        salary,
        DENSE_RANK() OVER (PARTITION BY departmentId ORDER BY salary DESC) salary_desc,
        departmentId
    FROM Employee
)
SELECT
    d.name AS Department,
    dsr.name AS Employee,
    dsr.salary AS Salary
FROM
    department_salary_rank dsr
    JOIN Department d ON dsr.departmentId = d.id
WHERE salary_desc <= 3

184. Department Highest Salary

Exact the same as 185. Department Top Three Salaries

WITH cte AS (
    SELECT
        d.name AS Department,
        e.name AS Employee,
        e.salary AS Salary,
        DENSE_RANK() OVER (PARTITION BY d.id ORDER BY e.salary DESC) AS salary_desc
    FROM
        Employee e
        LEFT JOIN Department d ON e.departmentId = d.id
)
SELECT
    Department,
    Employee,
    Salary
FROM cte
WHERE salary_desc = 1

1112. Highest Grade For Each Student

WITH cte AS (
    SELECT
        *,
        DENSE_RANK() OVER (PARTITION BY student_id ORDER BY grade DESC, course_id ASC) AS grade_desc
    FROM Enrollments
)
SELECT
    student_id,
    course_id,
    grade
FROM cte
WHERE grade_desc = 1
ORDER BY student_id

1412. Find the Quiet Students in All Exams

WITH student_rank AS (
    SELECT
        e.exam_id,
        e.student_id,
        s.student_name,
        DENSE_RANK() OVER (PARTITION BY e.exam_id ORDER BY e.score ASC) AS score_asc,
        DENSE_RANK() OVER (PARTITION BY e.exam_id ORDER BY e.score DESC) AS score_desc
    FROM
        Exam e
        JOIN Student s USING (student_id)
)
SELECT DISTINCT
    student_id,
    student_name
FROM student_rank sr1
WHERE NOT EXISTS (
        SELECT student_id
        FROM student_rank sr2
        WHERE
            sr1.student_id = sr2.student_id
            AND (score_asc = 1 OR score_desc = 1)
    )
ORDER BY student_id

1076. Project Employees II

WITH project_emp_rank AS (
    SELECT
        project_id,
        DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) AS project_desc
    FROM Project
    GROUP BY project_id
)
SELECT project_id
FROM project_emp_rank
WHERE project_desc = 1

1077. Project Employees III

WITH cte AS (
    SELECT
        project_id,
        p.employee_id,
        DENSE_RANK() OVER (PARTITION BY p.project_id ORDER BY e.experience_years DESC) AS exp_desc
    FROM
        Project p
        JOIN Employee e USING (employee_id)
)
SELECT
    project_id,
    employee_id
FROM cte
WHERE exp_desc = 1
ORDER BY
    project_id ASC,
    employee_id ASC

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

WITH cte AS (
    SELECT
        r1.user_id AS user1_id,
        r2.user_id AS user2_id,
        -- COUNT(*) AS common_followers,
        DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) AS common_followers_desc
    FROM
        Relations r1
        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
)
SELECT
    user1_id,
    user2_id
FROM cte
WHERE common_followers_desc = 1

ROW_NUMBER

550. Game Play Analysis IV

See LeetCode SQL Analysis - 550. Game Play Analysis IV