240804_TIL

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

SQL 코드카타

문제 링크

1. product_id와 purchase_date의 year를 기준으로 그루핑해서 order_id의 숫자가 3회 이상인 결과만 뽑는다. 이 결과를 CTE a에 저장한다.
2. 이제 a와 a를 셀프 조인하되, 조건은 'product_id가 동일할 것' + 'a1의 year가 a2의 year보다 하나 클 것'이다. 이렇게 해야 2년 연속으로 3회 이상 주문한 경우를 구할 수 있다.
   • 만약 문제에서 3년 연속 3회 이상 주문한 경우를 구하라고 한다면 셀프 조인을 3회 수행해야 할 텐데,
   • n년 연속 3회 이상 주문한 경우를 구하라고 할 때는 셀프 조인을 n번씩 하고 있을 수 없기 때문에 다른 방법으로 구해야 할 것이다.
   • 이 다른 방법에 대해서는 나중에 다시 풀면서 정리해 보기로.

WITH a
     AS (SELECT product_id,
                Year(purchase_date) AS "year"
         FROM   orders
         GROUP  BY 1,
                   2
         HAVING Count(order_id) >= 3)
SELECT DISTINCT a1.product_id
FROM   a a1
       JOIN a a2
         ON a1.product_id = a2.product_id
            AND a1.year = a2.year + 1;

문제 링크
잊을만 하면 한번씩 나오는 피벗테이블 문제.
계산의 기준이 될 CTE만 잘 만들어주면
그 뒤는 컬럼명만 설정해주면 되는 문제니 복잡하게 생각하지 말자. 아래는 피벗테이블 문제를 풀었던 코드카타 링크.

7월 12일 코드카타
7월 19일 코드카타

WITH result
     AS (SELECT *,
                Weekday(submit_date) AS "dayoftheweek"
         FROM   tasks),
     final_result
     AS (SELECT CASE
                  WHEN dayoftheweek BETWEEN 0 AND 4 THEN 'working'
                  WHEN dayoftheweek BETWEEN 5 AND 6 THEN 'weekend'
                END      AS "var",
                Count(*) AS "cnt"
         FROM   result
         GROUP  BY 1)
SELECT Max(CASE
             WHEN var = 'weekend' THEN cnt
           END) AS "weekend_cnt",
       Max(CASE
             WHEN var = 'working' THEN cnt
           END) AS "working_cnt"
FROM   final_result;

문제 링크
CTE에서 order by의 기준으로 쓸 만한 컬럼 2개를
미리 정의해 주면 간단하게 풀 수 있는 문제.

WITH result
     AS (SELECT user_id,
                gender,
                Row_number()
                  OVER(
                    partition BY gender
                    ORDER BY user_id) AS "rank1",
                CASE
                  WHEN gender = 'female' THEN 1
                  WHEN gender = 'other' THEN 2
                  WHEN gender = 'male' THEN 3
                END                   AS "rank2"
         FROM   genders)
SELECT user_id,
       gender
FROM   result
ORDER  BY rank1,
          rank2; 

문제 링크
window 함수 활용 문제.
CTE에서 구한 순위 중
하나는 city_id별로 단순 기온만 놓고 구한 순위,
다른 하나는 city_id별로 기온 +
동률일 경우 날짜까지 기준으로 잡고 구한 순위다.

본 쿼리에서는 이 두 순위가 모두 1위인 경우만 출력하면 끝.

WITH result
     AS (SELECT city_id,
                day,
                degree,
                Rank()
                  OVER(
                    partition BY city_id
                    ORDER BY degree DESC)          AS "rank1",
                Rank()
                  OVER(
                    partition BY city_id
                    ORDER BY degree DESC, day ASC) AS "rank2"
         FROM   weather)
SELECT city_id,
       day,
       degree
FROM   result
WHERE  rank1 = '1'
       AND rank2 = '1'
ORDER  BY 1; 

문제 링크
CTE에서 쓴 컬럼명을 그냥 rank라고 썼다가
본 쿼리에서 계속 오류가 떠서 잠깐 당황.

alias는 예약어를 피해서 쓰자.

WITH result
     AS (SELECT user_id,
                s.product_id,
                Sum(s.quantity * p.price)                  AS "spent",
                Dense_rank()
                  OVER(
                    partition BY user_id
                    ORDER BY Sum(s.quantity*p.price) DESC) AS "ranking"
         FROM   sales s
                INNER JOIN product p
                        ON s.product_id = p.product_id
         GROUP  BY 1,
                   2)
SELECT user_id,
       product_id
FROM   result
WHERE  ranking = '1';