LC 1159 & LC 1194 & LC 1225

SQL-Advanced
SQL
Publish Date:   2022-04-17

The following advanced-level SQL problems.

LeetCode 1159

Market Analysis II (Hard) [link]

SELECT u.user_id AS seller_id, 
    IF(t.user_id IS NULL, 'no', CASE WHEN t.favorite_brand = t.item_brand THEN 'yes' ELSE 'no' END) AS 2nd_item_fav_brand
FROM (
    SELECT user_id
    FROM Users
) u
LEFT JOIN (
    SELECT * 
    FROM(
        SELECT Users.user_id, Users.favorite_brand, Items.item_brand,
            dense_rank() OVER(PARTITION BY Users.user_id ORDER BY Orders.order_date) AS rnk
        FROM Users
        LEFT JOIN Orders
        ON Users.user_id = Orders.seller_id
        LEFT JOIN Items
        ON Orders.item_id = Items.item_id
    ) t
    WHERE rnk = 2
) t
ON u.user_id = t.user_id

LeetCode 1194

Tournament Winners (Hard) [link]

SELECT group_id, player AS player_id
FROM(
    SELECT t.group_id, t.player, 
        dense_rank() OVER(PARTITION BY t.group_id ORDER BY sum_score DESC, t.player) AS rnk
    FROM (
        SELECT Players.group_id, t.player, SUM(t.score) AS sum_score
        FROM Players
        LEFT JOIN (
            SELECT first_player AS player, first_score AS score FROM Matches
            UNION ALL
            SELECT second_player, second_score FROM Matches
        ) t
        ON Players.player_id = t.player
        GROUP BY Players.player_id, Players.group_id
    ) t
) t
WHERE rnk = 1

LeetCode 1225

Report Continuous Dates (Hard) [link]

SELECT type AS period_state, MIN(date) AS start_date, MAX(date) AS end_date
FROM(
    SELECT *, 
        SUBDATE(date, row_number() OVER (PARTITION BY type ORDER BY date) ) AS diff
    FROM(
        SELECT 'failed' AS type, fail_date AS date
        FROM Failed
        UNION ALL
        SELECT 'succeeded' AS type, success_date AS date
        FROM Succeeded
    ) t
    WHERE YEAR(date) = '2019'
) t
GROUP BY type, diff
ORDER BY 2
SQL-Advanced
  TOC