240704_TIL

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

SQL 코드카타
Leetcode 프리미엄을 결제하고 새로운 문제들로 넘어왔다.

문제 링크

WITH first
     AS (SELECT player_id,
                Min(event_date) AS first
         FROM   activity
         GROUP  BY 1)
SELECT first.player_id,
       a.device_id
FROM   first
       INNER JOIN activity a
               ON first.player_id = a.player_id
                  AND first.first = a.event_date; 

문제 링크

SELECT player_id,
       event_date,
       Sum(games_played)
         OVER (
           partition BY player_id
           ORDER BY player_id, event_date) AS "games_played_so_far"
FROM   activity;

문제 링크

WITH result
AS
  (
           SELECT   candidateid,
                    count(*) AS cnt_vote
           FROM     vote
           GROUP BY 1)
  SELECT     c.name
  FROM       candidate c
  INNER JOIN result r
  ON         c.id = r.candidateid
  ORDER BY   cnt_vote DESC
  LIMIT      1;

문제 링크
프리미엄에서 처음 본 난이도 hard 문제.
각 회사에서 중간값에 해당하는 경우를 출력하면 된다.

단, 한 회사(예컨대 A)의 급여 목록 수가 짝수(예컨대 6개)라면 중간값은 2개(세번째, 네번째)가 되므로 두 개를 출력해야 하는 조건이 있다.

먼저 각 회사별로 급여의 순위를 매기는 첫 번째 CTE를 만들어 준다.
이 때 row_number를 넣어서 1씩 증가하는 ranking 컬럼을 추가한다.

WITH a AS (
    SELECT id,
           company,
           salary,
           Row_number() OVER (partition BY company ORDER BY salary) AS ranking
    FROM employee
    ORDER BY 2, 3
),

다음으로 각 회사별 급여 목록의 숫자를 알아야
중간값을 하나로 뽑을지, 2개로 뽑을지 확인할 수 있으므로
회사별 급여 목록의 숫자를 확인할 수 있는 두 번째 CTE b를 추가한다.
이 때 회사별 급여 목록의 수는 cnt로 명명했다.

b AS (
    SELECT company,
           Count(*) AS cnt
    FROM employee
    GROUP BY 1
)

이제 본 쿼리에서 a와 b를 company 기준으로 inner join해 주고,
출력해야 할 컬럼들을 명시해 준다.

SELECT a.id,
       a.company,
       a.salary
FROM   a
       INNER JOIN b
               ON a.company = b.company

중요한 where절 부분인데,
여기서 회사별 급여목록의 수 cnt의 갯수가 짝수면 (즉, 2로 나누어 떨어지면)
출력해야 할 중간값의 ranking은 cnt를 2로 나누고 반올림한 값(예컨대 3)과 그보다 하나 큰 값(4)이 되어야 한다. 반면 cnt의 갯수가 홀수라면 2로 나누었을 때 나머지가 1일 것이고, 그 때는 출력해야 할 중간값의 ranking은 cnt를 2로 나누고 반올림한 값(예컨대 3)만 있으면 된다.

SELECT a.id,
       a.company,
       a.salary
FROM   a
       INNER JOIN b
               ON a.company = b.company
WHERE  CASE
         WHEN cnt%2 = 0 THEN ranking IN ( Round(cnt / 2), Round(cnt/2) + 1 )
         WHEN cnt%2 = 1 THEN ranking = Round(cnt / 2)
       END;

이제 위에서 만든 쿼리들을 하나로 합치면 정답이다.

WITH a
     AS (SELECT id,
                company,
                salary,
                Row_number()
                  OVER(
                    partition BY company
                    ORDER BY salary) AS ranking
         FROM   employee
         ORDER  BY 2,
                   3),
     b
     AS (SELECT company,
                Count(*) AS cnt
         FROM   employee
         GROUP  BY 1)
SELECT a.id,
       a.company,
       a.salary
FROM   a
       INNER JOIN b
               ON a.company = b.company
WHERE  CASE
         WHEN cnt%2 = 0 THEN ranking IN ( Round(cnt / 2), Round(cnt/2) + 1 )
         WHEN cnt%2 = 1 THEN ranking = Round(cnt / 2)
       END;

where절에서 case when을 써 본 경우는 처음인 것 같은데 이런 활용도 가능하다.
홀수/짝수를 판별하는 간단한 로직(%2=0)은 파이썬 기초문법을 배울 때 처음 접했던 건데, 이 경우 MySQL에서도 같은 문법이 적용된다.

다음 번에 풀어볼 때는 문제에서 추가로 요구한
이 조건도 해결해 보고 싶다.

Follow up: Could you solve it without using any built-in or window functions?

문제 링크

WITH result
     AS (SELECT (SELECT Count(DISTINCT sender_id, send_to_id)
                 FROM   friendrequest)   AS total_request,
                (SELECT Count(DISTINCT requester_id, accepter_id)
                 FROM   requestaccepted) AS total_accept)
SELECT Ifnull(Round(total_accept / total_request, 2), 0) AS accept_rate
FROM   result;

난이도가 높진 않았지만 CTE를 하나로 합쳐서 쓰는 것이 약간 헷갈렸던 문제.
어차피 두 개의 스칼라 값을 받아서 계산만 하면 되기 때문에 CTE 내에 꼭 from이 붙어있을 필요는 없다. 필요에 따라 CTE도 유연하게 정의해서 사용하면 될 일.

---

팀프로젝트 2일째.