240803_TIL

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

SQL 코드카타

문제 링크
최대 연승 횟수를 묻는 문제.
이 문제의 킥은 row_number 함수 2개를 사용하는 것인데,
하나는 match_day가 연속되었는지 여부를 판별하는 용도고
하나는 match_day별로 결과(win/lose/draw)가 연속되었는지를 판별하는 용도다.

이렇게 두 개의 window함수를 써서 CTE를 만들어 두고
where 조건에서 win인 경우만 필터링한 뒤에
row_number로 구한 각 값들의 차이를 기준으로
연승한 최대 횟수를 하나씩 구해 나가면 된다.

단, 승리한 경기가 아예 없는 경우도 있을 수 있으니
마지막에 distinct player_id를 구한 결과와 join하고
ifnull을 써서 0으로 처리해주는 부분만 주의.

WITH a
     AS (SELECT *,
                Row_number()
                  OVER(
                    partition BY player_id
                    ORDER BY match_day) AS "rank1",
                Row_number()
                  OVER(
                    partition BY player_id, result
                    ORDER BY match_day) AS "rank2"
         FROM   matches),
     b
     AS (SELECT player_id,
                Count(rank1 - rank2) AS "streak"
         FROM   a
         WHERE  result = 'win'
         GROUP  BY 1,
                   rank1 - rank2),
     c
     AS (SELECT player_id,
                Max(streak) AS "max_streak"
         FROM   b
         GROUP  BY 1)
SELECT DISTINCT m.player_id,
                Ifnull(c.max_streak, 0) AS "longest_streak"
FROM   matches m
       LEFT JOIN c
              ON m.player_id = c.player_id;

문제 링크
rank 함수 쓸 때 이름 순으로 나열하는 조건 빼먹지 말 것.

그리고 마지막에 rank_diff를 구하는 과정에서
계속 오류가 나서 확인해 보니 음수값이 나올 수 있도록
처리해주지 않아서 그런 것이었다.

cast(컬럼 as SIGNED)로 처리해 주면
결과가 음수로 출력될 수 있다.
물론 CTE를 만들 때에 적용해도 상관없음.

WITH a
     AS (SELECT *,
                Rank()
                  OVER(
                    ORDER BY points DESC, NAME) AS "rank1"
         FROM   teampoints),
     b
     AS (SELECT a.team_id,
                a.NAME,
                a.points + p.points_change                          AS "result",
                rank1,
                Rank()
                  OVER(
                    ORDER BY a.points+p.points_change DESC, a.NAME) AS "rank2"
         FROM   a
                LEFT JOIN pointschange p
                       ON a.team_id = p.team_id)
SELECT team_id,
       NAME,
       Cast(rank1 AS SIGNED) - Cast(rank2 AS SIGNED) AS "rank_diff"
FROM   b;

문제 링크

SELECT DISTINCT p1.user_id
FROM   purchases p1
       LEFT JOIN purchases p2
              ON p1.user_id = p2.user_id
                 AND p1.purchase_date >= p2.purchase_date
                 AND Datediff(p1.purchase_date, p2.purchase_date) <= 7
WHERE  p1.purchase_id <> p2.purchase_id
ORDER  BY 1;

문제 링크
이게 왜 medium일까🤔
내가 모르는 복잡한 뭔가가 있을까봐 괜히 꼬아서 생각했네..

SELECT r1.driver_id,
       Count(DISTINCT r2.ride_id) AS "cnt"
FROM   rides r1
       LEFT JOIN rides r2
              ON r1.driver_id = r2.passenger_id
GROUP  BY 1; 

문제 링크
minute이 어디까지 이어질지 모르므로
case when을 써서 1부터 6까지, 7부터 12까지 끊는 건 안된다.

minute을 6으로 나눈 값을 올림(ceil)한 결과를
interval_no로 잡고, 본 쿼리에서 이걸 기준으로
order_count를 더해주면 쉽게 구할 수 있음.

WITH result
     AS (SELECT *,
                Ceil(minute / 6) AS "interval_no"
         FROM   orders)
SELECT interval_no,
       Sum(order_count) AS "total_orders"
FROM   result
GROUP  BY 1
ORDER  BY 1;