241112_TIL

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

SQL 문제풀이 복습

Leetcode

문제 링크
1트(8/4) 때에는 '2년 연속'이라는 조건만 맞출 수 있도록
year끼리 차이를 1로 두게끔 join을 수행해서 풀었다.

당시 포스팅에서 만약 문제의 조건이 2년 연속이 아니라
3년, 4년 연속으로 바뀐다면 어떻게 풀어야 할지를 고민해 보겠다고 메모해 두었는데,
오늘 풀이가 그 고민의 결과다.

먼저 서브쿼리에서 year와 product_id 기준으로 cnt가 3 이상인,
즉 한 해에 3번 이상 팔린 product_id를 파악한다.
그리고 window 함수와 year를 연결해서 row_id라는 컬럼으로 저장해 둔 후,
서브쿼리를 빠져나온 본 쿼리에서 row_id를 group by의 기준으로 쓰고
count(*)가 2 이상인 경우를 구하면 정답이다.

만약 문제가 3년 연속 3개 이상 팔린 product_id를 찾으라는 것으로 바뀌면
본 쿼리 제일 마지막 부분을 count(*) >= 3으로만 바꾸면 된다.
window함수와 year를 조합해서 '몇 년 연속'인지 파악할 수 있게 하는
컬럼을 생성하는 것이 포인트.

SELECT DISTINCT product_id
FROM   (SELECT product_id,
               Year(purchase_date) - ROW_NUMBER()
                                       OVER(
                                         PARTITION BY product_id
                                         ORDER BY Year(purchase_date)) AS
               "row_id",
               Count(*)                                                AS "cnt"
        FROM   Orders
        GROUP  BY Year(purchase_date),
                  product_id
        HAVING cnt >= 3) a
GROUP  BY product_id,
          row_id
HAVING Count(*) >= 2;

문제 링크
cte 없이 푼 피벗테이블 문제.

SELECT MAX(CASE
             WHEN day_of_week = 'weekend' THEN cnt
           END) AS "weekend_cnt",
       MAX(CASE
             WHEN day_of_week = 'working' THEN cnt
           END) AS "working_cnt"
FROM   (SELECT day_of_week,
               COUNT(task_id) AS "cnt"
        FROM   (SELECT task_id,
                       submit_date,
                       CASE
                         WHEN DAYOFWEEK(submit_date) = 1
                               OR DAYOFWEEK(submit_date) = 7 THEN 'weekend'
                         ELSE 'working'
                       END AS "day_of_week"
                FROM   Tasks) a
        GROUP  BY 1) b;

문제 링크
window 함수를 써서 순서를 나열할 기준을 마련해 주되,
female-other-male 순으로도 나열될 수 있게끔
case when을 써서 기준을 하나 더 만들어 준다.

그 뒤에는 order by로 각 기준에 맞게 정렬해 주면 완성.

SELECT user_id,
       gender
FROM   (SELECT user_id,
               gender,
               ROW_NUMBER()
                 OVER(
                   PARTITION BY gender
                   ORDER BY user_id) AS "row_id",
               CASE
                 WHEN gender = 'female' THEN 1
                 WHEN gender = 'other' THEN 2
                 WHEN gender = 'male' THEN 3
               END                   AS "gender_id"
        FROM   Genders) a
ORDER  BY row_id,
          gender_id;