240709_TIL

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

SQL 코드카타

문제 링크
먼저 학생id별 최고 점수만 그루핑해서 CTE에 저장해 둔 후,
최고 점수가 동률일 경우 작은 course_id를 찾아야 하므로
본 쿼리에서 inner join + min(course_id)로 출력했다.

WITH a
     AS (SELECT student_id,
                Max(grade) AS "highest"
         FROM   enrollments
         GROUP  BY 1)
SELECT e.student_id,
       Min(course_id) AS "course_id",
       e.grade
FROM   enrollments e
       INNER JOIN a
               ON e.student_id = a.student_id
                  AND a.highest = e.grade
GROUP  BY 1,
          3
ORDER  BY 1;

문제 링크

WITH avg_oc
     AS (SELECT event_type,
                Avg(occurrences) AS "avg"
         FROM   events
         GROUP  BY 1)
SELECT business_id
FROM   events e
       LEFT JOIN avg_oc a
              ON e.event_type = a.event_type
                 AND e.occurrences > avg
WHERE  a.avg IS NOT NULL
GROUP  BY 1
HAVING Count(*) >= 2;

문제 링크
날짜 차이를 계산할 때 2019-07-27을 '포함해서' 30일 이내라고 했으므로
실제 쿼리를 짤 때는 조건을 '29일 이내'로 주어야 한다.

여기서 30으로 하면 2019-06-27까지 포함되기 때문에
(원래는 2019-06-28에서 끊겨야 함) 값이 애매하게 달라져서
오답이 뜨는 케이스가 있음.

만약 문제에서 '2019-07-27' exclusively라고 했으면 30으로 써야 할 듯.
아래는 정답 쿼리.

WITH result
     AS (SELECT user_id,
                Count(DISTINCT session_id) AS cnt
         FROM   activity
         WHERE  Timestampdiff(day, activity_date, '2019-07-27') <= 29
         GROUP  BY 1)
SELECT Ifnull(Round(Sum(cnt) / (SELECT Count(DISTINCT user_id)
                                FROM   result), 2), 0) AS
       "average_sessions_per_user"
FROM   result;

문제 링크
leetcode가 오답 기준을 섬세하게 잘 만들어 놓았다고 느낄 때가 많다.

이 문제도 distinct를 빼먹으면 소수점 자릿수에 미세한 차이가 나면서
오답이 뜨는 케이스가 있는데, 원리를 생각하면 distinct가 들어가 주는 게 맞다.

전혀 생각지도 못한 오답이 뜨는 게 아니라
미세한 차이로 인해 오답이 생긴 경우라면
distinct 같은 문법을 빼먹진 않았는지 생각해 볼 것.

WITH a
     AS (SELECT action_date,
                post_id
         FROM   actions
         WHERE  action = 'report'
                AND extra = 'spam'),
     result
     AS (SELECT a.action_date,
                Count(DISTINCT r.post_id) / Count(DISTINCT a.post_id) AS "ratio"
         FROM   a
                LEFT JOIN removals r
                       ON a.post_id = r.post_id
         GROUP  BY 1)
SELECT Round(100.0 * Avg(ratio), 2) AS "average_daily_percent"
FROM   result;

문제 링크

오늘도 최고로 시간을 많이 잡아먹은 난이도 hard 문제.
5개의 CTE를 만들어가며 난리를 친 끝에;; 해결했다.
아 물론 시간은 훨씬 초과됨^^

먼저 한 날짜에 2개의 플랫폼을 이용한 경우(both)를 구분하기 위해
a라는 이름의 CTE에 결과를 저장했다.

WITH a
     AS (SELECT spend_date,
                user_id,
                Count(DISTINCT platform) AS cnt
         FROM   spending
         GROUP  BY 1,
                   2
         HAVING Count(DISTINCT platform) >= 2)

이 문제의 킥은 both에 해당하는 유저 수와 total_amount가 없더라도
0으로라도 출력해야 한다는 것. 따라서 Spending 테이블에서 고유한 날짜를 구해서 dates라는 CTE에 저장하고, platform의 고유값(mobile, desktop)에다가 'both'까지 합쳐진 결과를 platforms라는 CTE에 저장한 다음 두 개의 CTE를 cross join해서 all_combinations라는 CTE를 만들었다.

WITH a
     AS (SELECT spend_date,
                user_id,
                Count(DISTINCT platform) AS cnt
         FROM   spending
         GROUP  BY 1,
                   2
         HAVING Count(DISTINCT platform) >= 2),
     dates
     AS (SELECT DISTINCT spend_date
         FROM   spending),
     platforms
     AS (SELECT 'both' AS platform
         UNION
         SELECT DISTINCT platform
         FROM   spending),
     all_combinations
     AS (SELECT d.spend_date,
                p.platform
         FROM   dates d
                CROSS JOIN platforms p),

그리고 Spending과 위에서 만든 a테이블을 left join하고, case when 구문을 써서 유저별 cnt가 2이면 both, 아니면 원래 platform을 뱉게끔 한 뒤 total_amount와 total_users까지 구한 후 결과를 b라는 이름의 CTE에 저장한다.

WITH a
     AS (SELECT spend_date,
                user_id,
                Count(DISTINCT platform) AS cnt
         FROM   spending
         GROUP  BY 1,
                   2
         HAVING Count(DISTINCT platform) >= 2),
     dates
     AS (SELECT DISTINCT spend_date
         FROM   spending),
     platforms
     AS (SELECT 'both' AS platform
         UNION
         SELECT DISTINCT platform
         FROM   spending),
     all_combinations
     AS (SELECT d.spend_date,
                p.platform
         FROM   dates d
                CROSS JOIN platforms p),
     b
     AS (SELECT s.spend_date,
                CASE
                  WHEN a.cnt = 2 THEN 'both'
                  ELSE platform
                END                       AS 'platform',
                Sum(amount)               AS total_amount,
                Count(DISTINCT s.user_id) AS total_users
         FROM   spending s
                LEFT JOIN a
                       ON s.spend_date = a.spend_date
                          AND s.user_id = a.user_id
         GROUP  BY 1,
                   2)

마지막으로, all_combinations와 b를 left join으로 연결한 후
null인 값들을 0으로 처리하면서 불러와주면 완성이다.
아래는 완성된 정답 쿼리.

WITH a
     AS (SELECT spend_date,
                user_id,
                Count(DISTINCT platform) AS cnt
         FROM   spending
         GROUP  BY 1,
                   2
         HAVING Count(DISTINCT platform) >= 2),
     dates
     AS (SELECT DISTINCT spend_date
         FROM   spending),
     platforms
     AS (SELECT 'both' AS platform
         UNION
         SELECT DISTINCT platform
         FROM   spending),
     all_combinations
     AS (SELECT d.spend_date,
                p.platform
         FROM   dates d
                CROSS JOIN platforms p),
     b
     AS (SELECT s.spend_date,
                CASE
                  WHEN a.cnt = 2 THEN 'both'
                  ELSE platform
                END                       AS 'platform',
                Sum(amount)               AS total_amount,
                Count(DISTINCT s.user_id) AS total_users
         FROM   spending s
                LEFT JOIN a
                       ON s.spend_date = a.spend_date
                          AND s.user_id = a.user_id
         GROUP  BY 1,
                   2)
SELECT ac.spend_date,
       ac.platform,
       Ifnull(total_amount, 0) AS "total_amount",
       Ifnull(total_users, 0)  AS "total_users"
FROM   all_combinations ac
       LEFT JOIN b
              ON ac.spend_date = b.spend_date
                 AND ac.platform = b.platform;

7월 2일에 both가 없는 버전까지 만드는 데는 성공했는데 없는 both를 생성하고 0으로 값을 띄워야 하는 부분에서 생각이 막혀서 시간이 오래 걸렸다. 'cross join을 써서 가능한 모든 조합을 먼저 만들어 둔 후 left join하면 되는 문제'를 예전에 코드카타에서도 풀었던 적이 있는데, 그 기억이 늦게 떠올랐던 게 아쉬웠다.

그래도 hard 문제를 몇 번 접하니
조금씩 어떻게 접근해야 할지 감이 잡히는 것도 같다..!