250213_TIL

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

SQL 문제풀이 복습

---

Leetcode

2082. The Number of Rich Customers
풀이시간 03:25

SELECT COUNT(customer_id) AS "rich_count"
FROM   (SELECT customer_id
        FROM   Store
        GROUP  BY 1
        HAVING SUM(amount > 500) > 0) r;

2084. Drop Type 1 Orders for Customers With Type 0 Orders
15:00까지 시도했지만 실패.
조건을 너무 어렵게 생각해서 오히려 더 안풀렸던 듯.

아래는 정답 쿼리.

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;

2112. The Airport With the Most Traffic
풀이시간 02:26

SELECT airport_id
FROM   (SELECT airport_id,
               Sum(flights_count)                    AS "total",
               RANK()
                 OVER(
                   ORDER BY Sum(flights_count) DESC) AS "ranking"
        FROM   (SELECT departure_airport AS "airport_id",
                       flights_count
                FROM   Flights
                UNION ALL
                SELECT arrival_airport,
                       flights_count
                FROM   Flights) result
        GROUP  BY 1) final_result
WHERE  ranking = 1;

2118. Build the Equation
풀이시간 11:26

SELECT CONCAT(GROUP_CONCAT(test ORDER BY power DESC SEPARATOR ''), '=0') AS
       "equation"
FROM   (SELECT power,
               factor,
               CASE
                 WHEN power > 1
                      AND factor > 0 THEN CONCAT('+', factor, 'X^', power)
                 WHEN power = 1
                      AND factor > 0 THEN CONCAT('+', factor, 'X')
                 WHEN power = 0
                      AND factor > 0 THEN CONCAT('+', factor)
                 WHEN power > 1
                      AND factor < 0 THEN CONCAT(factor, 'X^', power)
                 WHEN power = 1
                      AND factor < 0 THEN CONCAT(factor, 'X')
                 WHEN power = 0
                      AND factor < 0 THEN factor
               end AS "test"
        FROM   Terms) result;

2142. The Number of Passengers in Each Bus I
풀이시간 11:19

SELECT b.bus_id,
       COUNT(DISTINCT passenger_id) AS "passengers_cnt"
FROM   Buses b
       LEFT JOIN (SELECT passenger_id,
                         MIN(b.arrival_time) AS "min_bus"
                  FROM   Buses b
                         LEFT JOIN Passengers p
                                ON b.arrival_time >= p.arrival_time
                  GROUP  BY 1) a
              ON b.arrival_time = a.min_bus
GROUP  BY 1;