LC 176 & LC 177 & LC 178 & LC 1077 & LC 1596

The following advanced-level SQL problems are about ‘Rank Function’.

LeetCode 176

Second Highest Salary (Medium) [link]

SELECT salary AS SecondHighestSalary
FROM(
    SELECT *,
        dense_rank() OVER(ORDER BY salary DESC) as rnk
    FROM Employee
) t
WHERE rnk = 2;

If we don’t use IFNULL we would get empty output. If we don’t add DISTINCT, we would get wrong answer when there is a tie.

SELECT IFNULL((
    SELECT DISTINCT salary 
    FROM Employee
    ORDER BY salary DESC
    LIMIT 1
    OFFSET 1
), NULL) AS SecondHighestSalary;

LeetCode 177

Nth Highest Salary (Medium) [link]

Using window function.

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
      SELECT DISTINCT t.salary
      FROM (
          SELECT *,
            dense_rank() OVER(ORDER BY salary DESC) AS rnk
          FROM Employee
      ) t
      WHERE t.rnk = N
  );
END

Using LIMIT. Note that we cannot use N-1 directly in LIMIT, and we need to use GROUP BY before ORDER BY to remove duplications.

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
declare m INT;
SET m = N-1;
  RETURN (
      # Write your MySQL query statement below.
      SELECT salary
      FROM Employee
      GROUP BY salary
      ORDER BY salary DESC
      LIMIT m,1
  );
END

LeetCode 178

Rank Scores (Medium) [link]

SELECT score, dense_rank() OVER(ORDER BY score DESC) as 'rank'
FROM Scores

LeetCode 1077

Project Employees III (Medium) [link]

Using dense_rank window function

SELECT product_id, year AS first_year, quantity, price
FROM(
    SELECT *,
        dense_rank() OVER(PARTITION BY product_id ORDER BY year) AS rnk
    FROM Sales
) t
WHERE rnk = 1;

LeetCode 1596

The Most Frequently Ordered Products for Each Customer (Medium) [link]

Note that in the sub table, COUNT(*) is grouped by customer_id and product_id.

SELECT t.customer_id, t.product_id, p.product_name
FROM(
    SELECT customer_id, product_id,
        rank() OVER(PARTITION BY customer_id ORDER BY COUNT(*) DESC) AS rnk
    FROM Orders
    GROUP BY customer_id, product_id
) t
LEFT JOIN Products p
ON t.product_id = p.product_id
WHERE rnk = 1;