240711_TIL

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

SQL 코드카타
지랄말고 지금 하자

문제 링크

WITH t_app
     AS (SELECT Date_format(trans_date, '%Y-%m') AS "month",
                country,
                Count(DISTINCT id)               AS "approved_count",
                Sum(amount)                      AS "approved_amount"
         FROM   transactions
         WHERE  state = 'approved'
         GROUP  BY 1,
                   2),
     cb
     AS (SELECT Date_format(c.trans_date, '%Y-%m') AS "month",
                t.country,
                Count(DISTINCT c.trans_id)         AS "chargeback_count",
                Sum(amount)                        AS "chargeback_amount"
         FROM   chargebacks c
                LEFT JOIN transactions t
                       ON c.trans_id = t.id
         GROUP  BY 1,
                   2),
     all_combination
     AS (SELECT month,
                country
         FROM   t_app
         UNION
         SELECT month,
                country
         FROM   cb)
SELECT ac.month,
       ac.country,
       Ifnull(approved_count, 0)    AS "approved_count",
       Ifnull(approved_amount, 0)   AS "approved_amount",
       Ifnull(chargeback_count, 0)  AS "chargeback_count",
       Ifnull(chargeback_amount, 0) AS "chargeback_amount"
FROM   all_combination ac
       LEFT JOIN t_app t
              ON ac.month = t.month
                 AND ac.country = t.country
       LEFT JOIN cb c
              ON ac.month = c.month
                 AND ac.country = c.country;

• approved 상태인 거래의 월별/국가별 집계를 구하는 t_app,
• chargeback의 건수와 수량을 월별/국가별 집계하는 cb,
• 그리고 위 두 테이블의 고유한 month와 country를 모두 조합한 all_combination

이렇게 3개의 CTE를 만들어서 join으로 해결했다.
내 문제풀이 패턴을 체크해 보면, 특히 3번째 CTE인 all_combination을 만드는 걸 빠르게 떠올리지 못했을 때 풀이 시간이 늘어지는 경향이 있다.
패턴을 몇 가지로 정리해 두고 반자동으로 떠오르게 만들어 봐야겠다.

문제 링크

WITH host_side
     AS (SELECT host_team,
                CASE
                  WHEN host_goals > guest_goals THEN '3'
                  WHEN host_goals = guest_goals THEN '1'
                  WHEN host_goals < guest_goals THEN '0'
                END AS 'point_host'
         FROM   matches
         ORDER  BY 1),
     guest_side
     AS (SELECT guest_team,
                CASE
                  WHEN guest_goals > host_goals THEN '3'
                  WHEN guest_goals = host_goals THEN '1'
                  WHEN guest_goals < host_goals THEN '0'
                END AS 'point_guest'
         FROM   matches
         ORDER  BY 1),
     result
     AS (SELECT host_team  AS "team_id",
                point_host AS "points"
         FROM   host_side
         UNION ALL
         SELECT guest_team  AS "team_id",
                point_guest AS "points"
         FROM   guest_side),
     final
     AS (SELECT team_id,
                Sum(points) AS "num_points"
         FROM   result
         GROUP  BY 1)
SELECT t.team_id,
       t.team_name,
       Ifnull(num_points, 0) AS "num_points"
FROM   teams t
       LEFT JOIN final f
              ON t.team_id = f.team_id
ORDER  BY 3 DESC,
          1;

문제 링크
의외로 쉽게 풀렸던 hard 문제.

seller로 거래한 이력의 갯수를 확인해서
1회면 볼 것도 없이 무조건 no고,
아닌 경우에는 2번째 거래(ranking=2)에서 팔았던 브랜드가
좋아하는 브랜드였는지 여부를 체크하면 된다.
두 개의 연산 결과를 union하면 정답.

WITH a
     AS (SELECT u.user_id,
                u.favorite_brand,
                i.item_brand,
                o.item_id,
                o.seller_id,
                Row_number()
                  OVER(
                    partition BY u.user_id
                    ORDER BY o.order_date) AS ranking
         FROM   users u
                LEFT JOIN orders o
                       ON u.user_id = o.seller_id
                LEFT JOIN items i
                       ON o.item_id = i.item_id
         ORDER  BY u.user_id,
                   o.order_date)
SELECT user_id AS "seller_id",
       "no"    AS "2nd_item_fav_brand"
FROM   a
GROUP  BY 1
HAVING Count(*) = 1
UNION
SELECT user_id AS "seller_id",
       CASE
         WHEN favorite_brand = item_brand THEN 'yes'
         ELSE 'no'
       END     AS "2nd_item_fav_brand"
FROM   a
WHERE  ranking = '2';

문제 링크
friends CTE로 전체 친구 리스트를 미리 만들어 놓고
본 쿼리에서 Likes와 left join한다.

이 때 친구들이 아무도 추천하지 않을 경우
null이 아니라 아무것도 결과에 출력되지 않아야 하므로,
조인 후 l.user_id는 null이 아니라는 조건을 추가한다.

마지막으로 1이 이미 좋아하는 page_id는 포함되면 안 되므로
where절에 서브쿼리를 써서 해당 경우를 제외하면 정답.

WITH friends
     AS (SELECT user2_id AS "user_id"
         FROM   friendship
         WHERE  user1_id = '1'
         UNION
         SELECT user1_id AS "user_id"
         FROM   friendship
         WHERE  user2_id = '1')
SELECT DISTINCT page_id AS "recommended_page"
FROM   friends f
       LEFT JOIN likes l
              ON f.user_id = l.user_id
WHERE  page_id NOT IN (SELECT page_id
                       FROM   likes
                       WHERE  user_id = '1')
       AND l.user_id IS NOT NULL;

문제 링크
역시 hard치고는 그렇게까지 어렵진 않았던 문제.

우선 first_player의 score와 second_player의 score를 score CTE에 저장하고
result CTE에서는 각 player의 id별로 결과를 합산해 준다.
final_result에서는 result에 Players 테이블을 left join해서
group_id를 불러오고, rank over 함수를 써서 각 그룹별로 1등을 확인한다.

이 때 순위는 각 그룹별로 매겨야 하고,
총 score가 높아야 winner이므로 score 기준 내림차순,
동률일 경우에는 id가 작은 player가 winner라는 조건이 있었으므로

rank() over(partition BY group_id ORDER BY total_score DESC, player ASC)

위와 같이 적어주면 그룹별 순위가 정해진다.
이제 본 쿼리에서 ranking = 1인 값들만 뽑아서
컬럼명을 맞게 지정해 주면 정답. 아래는 완성된 정답 쿼리다.

WITH score
     AS (SELECT first_player AS "player",
                first_score  AS "score"
         FROM   matches
         UNION ALL
         SELECT second_player,
                second_score
         FROM   matches),
     result
     AS (SELECT player,
                Sum(score) AS "total_score"
         FROM   score
         GROUP  BY 1),
     final_result
     AS (SELECT player,
                total_score,
                p.group_id,
                Rank()
                  OVER(
                    partition BY group_id
                    ORDER BY total_score DESC, player ASC) AS ranking
         FROM   result r
                LEFT JOIN players p
                       ON r.player = p.player_id)
SELECT group_id,
       player AS "PLAYER_ID"
FROM   final_result
WHERE  ranking = '1';