241115_TIL

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

SQL 문제풀이 복습

Leetcode

문제 링크
간단한 window 함수 활용 문제.

SELECT city_id,
       day,
       degree
FROM   (SELECT city_id,
               day,
               degree,
               RANK()
                 OVER(
                   PARTITION BY city_id
                   ORDER BY degree DESC, day ASC) AS "ranking"
        FROM   Weather) result
WHERE  ranking = 1
ORDER  BY 1;

문제 링크
중간에 join이 한 번 들어갔냐, 아니냐만 제외하면
위의 문제와 구조와 풀이법이 완전히 똑같은 문제.
어려울 거 없음.

SELECT user_id,
       product_id
FROM   (SELECT s.user_id,
               s.product_id,
               RANK()
                 OVER(
                   PARTITION BY s.user_id
                   ORDER BY Sum(s.quantity * p.price) DESC) AS "ranking"
        FROM   Sales s
               JOIN Product p
                 ON s.product_id = p.product_id
        GROUP  BY 1,
                  2) result
WHERE  ranking = 1;

문제 링크

SELECT user_id,
       SUM(quantity * price) AS "spending"
FROM   Sales s
       JOIN Product p
         ON s.product_id = p.product_id
GROUP  BY 1
ORDER  BY 2 DESC,
          1 ASC;