250214_TIL

아무리 사소하더라도 배움이 없는 날은 없다.

SQL 문제풀이 복습

---

Leetcode

2153. The Number of Passengers in Each Bus II
이건 너무 어려워서 손도 못 댔다.
나중에 다른 사람 쿼리를 보고 하나씩 따라해 봐야지.

2159. Order Two Columns Independently
풀이시간 09:57

SELECT first_col,
       second_col
FROM   (SELECT ROW_NUMBER()
                 OVER(
                   ORDER BY first_col) AS "row1",
               first_col
        FROM   Data) a
       LEFT JOIN (SELECT ROW_NUMBER()
                           OVER(
                             ORDER BY second_col DESC) AS "row2",
                         second_col
                  FROM   Data) b
              ON a.row1 = b.row2;

2173. Longest Winning Streak
풀이시간 11:21

SELECT player_id,
       Max(streak) AS "longest_streak"
FROM   (SELECT player_id,
               Count(CASE
                       WHEN result = 'Win' THEN match_day
                     END) AS "streak"
        FROM   (SELECT player_id,
                       match_day,
                       result,
                       ROW_NUMBER()
                         OVER(
                           partition BY player_id
                           ORDER BY match_day ASC) AS "row_id",
                       RANK()
                         OVER(
                           partition BY player_id, result
                           ORDER BY match_day ASC) AS "row_id2"
                FROM   Matches) a
        GROUP  BY player_id,
                  row_id - row_id2) b
GROUP  BY 1;

2175. The Change in Global Rankings
풀이시간 04:07

SELECT team_id,
       NAME,
       Cast(original_ranking AS SIGNED) - Cast(changed_ranking AS SIGNED) AS
       "rank_diff"
FROM   (SELECT p.team_id,
               NAME,
               RANK()
                 OVER(
                   ORDER BY points DESC, NAME ASC)               AS
               "original_ranking",
               RANK()
                 OVER(
                   ORDER BY points+points_change DESC, NAME ASC) AS
               "changed_ranking"
        FROM   Teampoints t
               JOIN PointsChange p
                 ON t.team_id = p.team_id) r;

2199. Finding the Topic of Each Post
풀이시간 15:00

이런 문제는 신기하긴 한데 실전에서 마주칠 일이 있을까 싶다.

SELECT post_id,
       IFNULL(GROUP_CONCAT(DISTINCT k.topic_id ORDER BY k.topic_id ASC),
       'Ambiguous!')
       AS "topic"
FROM   Posts p
       LEFT JOIN Keywords k
              ON CONCAT(' ', LOWER(p.content), ' ') LIKE
                 CONCAT('% ', LOWER(k.word), ' %')
GROUP  BY 1;

---

다시 풀어볼 문제

2084. Drop Type 1 Orders for Customers With Type 0 Orders
풀이시간 02:27

SELECT o.order_id,
       o.customer_id,
       o.order_type
FROM   Orders o
       LEFT JOIN (SELECT customer_id
                  FROM   Orders
                  GROUP  BY 1
                  HAVING Count(DISTINCT order_type) = 2) a
              ON o.customer_id = a.customer_id
                 AND order_type = 1
WHERE  a.customer_id IS NULL;