Daily LeetCode #12

Some notes on problems from SQL 50.

https://leetcode.com/problems/consecutive-numbers/submissions/1431765550/?envType=study-plan-v2&envId=top-sql-50

SELECT l1.num AS  ConsecutiveNums
FROM Logs AS l1 JOIN Logs AS l2 JOIN Logs AS l3
WHERE l3.id - l2.id = 1 AND l2.id - l1.id = 1 
      AND l1.num = l2.num AND l2.num = l3.num
GROUP BY l1.num

Notes:

• need three consecutive same num, so JOIN three Logs

https://leetcode.com/problems/product-price-at-a-given-date/submissions/1431882416/?envType=study-plan-v2&envId=top-sql-50

SELECT product_id, new_price AS price
FROM Products
WHERE (product_id, change_date) 
       IN (SELECT product_id, MAX(change_date)
           FROM Products
           WHERE change_date <= '2019-08-16'
           GROUP BY product_id)

UNION

SELECT product_id, 10 AS price
FROM Products
WHERE product_id NOT IN 
          (SELECT product_id
           FROM Products
           WHERE change_date <= '2019-08-16')

Note:

• UNION will combine two table together, need to ensure attributes are the same
• UNION ALL, is the same as UNION, but keep duplicates

https://leetcode.com/problems/last-person-to-fit-in-the-bus/description/?envType=study-plan-v2&envId=top-sql-50

# Write your MySQL query statement below
SELECT Q1.person_name
FROM Queue AS Q1 JOIN Queue AS Q2
WHERE Q2.turn <= Q1.turn
GROUP BY Q1.turn
HAVING SUM(Q2.weight) <= 1000
ORDER BY SUM(Q2.weight) DESC
LIMIT 1
          

Notes:

• LIMIT n will return first n rows

https://leetcode.com/problems/exchange-seats/?envType=study-plan-v2&envId=top-sql-50

Method 1:

# Write your MySQL query statement below
SELECT * 
FROM (
    SELECT S1.id, S2.student
    FROM Seat AS S1 JOIN Seat AS S2
    WHERE S1.id % 2 = 1 AND (S1.id + 1 = S2.id OR (S1.id = (SELECT COUNT(*) FROM Seat) AND S1.id = S2.id))

    UNION

    SELECT S1.id, S2.student
    FROM Seat AS S1 JOIN Seat AS S2
    WHERE S1.id % 2 = 0 AND S1.id - 1 = S2.id
) AS Result
ORDER BY id

Notes:

• use UNION
• first query selects odd ids
• second query selects even ids
• use SELECT * FROM (A UNION B) AS table_name ORDER BY _ to sort the table after UNION

Method 2:

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

Notes:

• use CASE WHEN:
  CASE
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
ELSE default_result
END

Method 3:

WITH X AS (
    SELECT id,
           student,
           LAG(student) OVER (ORDER BY id) AS prev_student,
           LEAD(student) OVER (ORDER BY id) AS next_student
    FROM Seat
)

SELECT id, 
       CASE
            WHEN id % 2 = 1 AND next_student IS NOT NULL THEN next_student
            WHEN id % 2 = 0 THEN prev_student
            ELSE student
        END AS student
FROM X
ORDER BY id

Notes:

• WITH table_name AS (query) creats a new temporaty table
• LAG(attribute) OVER (ORDER BY attribute) AS new_attribute_name chooses the previous row
• LEAD(attribute) OVER (ORDER BY attribute) AS new_attribute_name chooses the next row