LC 626 & LC 1164 & LC 1294 & LC 1783

SQL-Basic
SQL
Publish Date:   2022-03-26

The following basic-level SQL problems are about ‘linking’. More: MOD

LeetCode 626

Exchange Seats (Medium) [link]

Using CASE WHEN

SELECT (
    CASE
        WHEN MOD(id, 2) != 0 AND cnt != id THEN id+1
        WHEN MOD(id, 2) != 0 AND cnt = id THEN id
        ELSE id-1
    END
) AS id, student
FROM Seat, (
    SELECT COUNT(*) AS cnt
    FROM Seat
) AS t
ORDER BY id;

Use LAG and LEAD to keep track of the last and next student name of the current name, the use IF to decide the name based on odd and even id.

SELECT t.id, IF(t.id % 2 = 0, last, next) AS student
FROM (
    SELECT id, student, 
        lag(student,1,student) OVER() last,
        lead(student,1,student) OVER() next
    FROM seat
) AS t;

LeetCode 1164

Product Price at a Given Date (Medium) [link]

Using LEFT JOIN

SELECT p1.product_id, IFNULL(p2.new_price, 10) AS price
FROM(
    SELECT DISTINCT product_id FROM Products
) p1
LEFT JOIN(
    SELECT product_id, new_price, change_date
    FROM Products
    WHERE (product_id, change_date) IN (
        SELECT product_id, MAX(change_date) AS change_date
        FROM Products
        WHERE change_date <= '2019-08-16'
        GROUP BY product_id
    ) 
) AS p2
ON p1.product_id = p2.product_id;

LeetCode 1294

Weather Type in Each Country (Easy) [link]

SELECT C.country_name,
    CASE 
        WHEN AVG(W.weather_state) <= 15 THEN 'Cold'
        WHEN AVG(W.weather_state) >= 25 THEN 'Hot'
        ELSE 'Warm'
    END AS weather_type
FROM Weather W
LEFT JOIN Countries C
ON W.country_id = C.country_id
WHERE DATE_FORMAT(W.day, '%Y-%m') = "2019-11"
GROUP BY C.country_name;

LeetCode 1783

Grand Slam Titles (Medium) [link]

SELECT t.winners AS player_id, p.player_name, COUNT(t.winners) AS grand_slams_count
FROM (
    SELECT Wimbledon AS winners FROM Championships
    UNION ALL
    SELECT Fr_open FROM Championships
    UNION ALL
    SELECT US_open FROM Championships
    UNION ALL
    SELECT Au_open FROM Championships
) AS t
LEFT JOIN Players p
ON p.player_id = t.winners
GROUP BY t.winners;
SQL-Basic
  TOC