LC 1070 & LC 1112 & LC 1285 & LC 1951

Window Functions
SQL
Publish Date:   2022-04-28

Solution to LeetCode about ‘window function’.

LeetCode 1070

Product Sales Analysis III (Medium) [link]

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 1112

Highest Grade For Each Student (Medium) [link]

Use row_number() rather than rank() or dense_rank() to get rid of ties.

SELECT student_id, course_id, grade
FROM(
    SELECT *,
        row_number() OVER(PARTITION BY student_id ORDER BY grade DESC, course_id) as row_num
    FROM Enrollments
) t
WHERE row_num = 1;

LeetCode 1285

Find the Start and End Number of Continuous Range (Medium) [link]

Group continuous sequences by log_id - row_number().

SELECT MIN(log_id) AS start_id, MAX(log_id) AS end_id
FROM(
    SELECT *,
        log_id - row_number() OVER() as rnk_diff
    FROM Logs
) t
GROUP BY rnk_diff;

LeetCode 1951

All the Pairs with the Maximum Number of Common Followers (Medium) [link]

SELECT t2.user1_id, t2.user2_id
FROM(
    SELECT user1_id, user2_id, 
        rank() OVER(ORDER BY COUNT(*) DESC) AS rnk
    FROM(
        SELECT r1.user_id AS user1_id, r2.user_id AS user2_id, r1.follower_id
        FROM Relations r1
        JOIN Relations r2
        ON r1.follower_id = r2.follower_id AND r1.user_id < r2.user_id
    ) t
    GROUP BY user1_id, user2_id
) t2
WHERE rnk = 1;
Window Functions
  TOC