250422_TIL

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

SQL 문제풀이 복습

Leetcode

2893. Calculate Orders Within Each Interval
풀이시간 01:40

SELECT CEIL(minute / 6) AS "interval_no",
       SUM(order_count) AS "total_orders"
FROM   Orders
GROUP  BY 1
ORDER  BY 1;

2922. Market Analysis III
풀이시간 14:14

WITH result
     AS (SELECT o.seller_id,
                Count(DISTINCT CASE
                                 WHEN favorite_brand <> item_brand THEN
                                 o.item_id
                               END) AS "cnt"
         FROM   Orders o
                JOIN Users u
                  ON o.seller_id = u.seller_id
                JOIN Items i
                  ON o.item_id = i.item_id
         GROUP  BY 1)
SELECT seller_id,
       cnt AS "num_items"
FROM   result
WHERE  cnt = (SELECT Max(cnt)
              FROM   result)
ORDER  BY 1;

2978. Symmetric Coordinates
풀이시간 05:05

SELECT LEAST(c1.X, c2.X)    AS "x",
       GREATEST(c1.Y, c2.Y) AS "y"
FROM   Coordinates c1
       JOIN Coordinates c2
         ON c1.X = c2.Y
            AND c1.Y = c2.X
            AND c1.X <= c2.Y
GROUP  BY 1,
          2
HAVING COUNT(*) > 1
ORDER  BY 1,
          2;