240721_TIL

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

SQL 코드카타

문제 링크
간단한 피벗테이블 문제.

SELECT product_id,
       Max(CASE
             WHEN store = 'store1' THEN price
           end) AS "store1",
       Max(CASE
             WHEN store = 'store2' THEN price
           end) AS "store2",
       Max(CASE
             WHEN store = 'store3' THEN price
           end) AS "store3"
FROM   products
GROUP  BY 1; 

문제 링크
조건을 잘 따라가다 보면 그렇게 복잡할 건 없는 문제.

• 연속이 아니더라도 금메달 3번 땄으면 포함
• 연속해서 3번의 대회에서 메달 땄으면 포함

WITH gold_medal
     AS (SELECT gold_medal        AS "id",
                Count(contest_id) AS cnt
         FROM   contests
         GROUP  BY 1
         HAVING cnt >= 3),
     result
     AS (SELECT gold_medal AS "id",
                contest_id
         FROM   contests
         UNION ALL
         SELECT silver_medal AS "id",
                contest_id
         FROM   contests
         UNION ALL
         SELECT bronze_medal AS "id",
                contest_id
         FROM   contests
         ORDER  BY 1,
                   2),
     final_result
     AS (SELECT id,
                contest_id,
                Row_number()
                  OVER(
                    partition BY id
                    ORDER BY contest_id) AS "ranking"
         FROM   result),
     consecutive3
     AS (SELECT id,
                contest_id - ranking,
                Count(*) AS "cnt2"
         FROM   final_result
         GROUP  BY 1,
                   2
         HAVING cnt2 >= 3),
     a
     AS (SELECT id
         FROM   gold_medal
         UNION
         SELECT id
         FROM   consecutive3)
SELECT u.NAME,
       u.mail
FROM   a
       INNER JOIN users u
               ON a.id = u.user_id;

문제 링크

SELECT p.session_id
FROM   playback p
       LEFT JOIN ads a
              ON p.customer_id = a.customer_id
                 AND a.timestamp BETWEEN p.start_time AND p.end_time
WHERE  a.ad_id IS NULL;

문제 링크
승점 계산하는 CTE를 빼먹었다가 시간이 조금 오래 걸렸던 문제.
이 문제는 나중에 다시 풀어봐야겠다.
이렇게 복잡하지 않아도 됐을 것 같다.

WITH a
     AS (SELECT home_team_id    AS "team_id",
                home_team_goals AS "goals"
         FROM   matches
         UNION ALL
         SELECT away_team_id,
                away_team_goals
         FROM   matches),
     b
     AS (SELECT team_id,
                Count(*)   AS "matches_played",
                Sum(goals) AS "goal_for"
         FROM   a
         GROUP  BY 1),
     goal_against
     AS (SELECT home_team_id         AS "team_id",
                Sum(away_team_goals) AS "goal_against"
         FROM   matches
         GROUP  BY 1
         UNION ALL
         SELECT away_team_id,
                Sum(home_team_goals)
         FROM   matches
         GROUP  BY 1),
     goal_against_f
     AS (SELECT team_id,
                Sum(goal_against) AS "goal_against"
         FROM   goal_against
         GROUP  BY 1),
     result
     AS (SELECT b.team_id,
                t.team_name,
                matches_played,
                goal_for,
                goal_against,
                goal_for - goal_against AS "goal_diff"
         FROM   b
                INNER JOIN goal_against_f g
                        ON b.team_id = g.team_id
                INNER JOIN teams t
                        ON b.team_id = t.team_id),
     points
     AS (SELECT home_team_id AS "team_id",
                CASE
                  WHEN home_team_goals > away_team_goals THEN 3
                  WHEN home_team_goals = away_team_goals THEN 1
                  WHEN home_team_goals < away_team_goals THEN 0
                END          AS "point"
         FROM   matches
         UNION ALL
         SELECT away_team_id,
                CASE
                  WHEN home_team_goals < away_team_goals THEN 3
                  WHEN home_team_goals = away_team_goals THEN 1
                  WHEN home_team_goals > away_team_goals THEN 0
                END AS "point"
         FROM   matches),
     points2
     AS (SELECT team_id,
                Sum(point) AS "points"
         FROM   points
         GROUP  BY 1)
SELECT r.team_name,
       r.matches_played,
       p2.points,
       r.goal_for,
       r.goal_against,
       r.goal_diff
FROM   result r
       INNER JOIN points2 p2
               ON r.team_id = p2.team_id
ORDER  BY 3 DESC,
          6 DESC,
          1 ASC;

문제 링크
어제 풀었던 문제와 똑같은 데이터인데 약간만 응용하는 문제.

두 번째 accepted_count CTE를 만들 때 count(distinct driver_id)를 써야 했는데,
count(distinct ride_id)로 쓰는 바람에 cnt가 1씩 늘어나서 오류가 뜨는 문제가 있었다.
이걸 찾는 데 시간을 너무 오래 쓰는 바람에 심각하게 타임오버가 되었는데ㅠ

오류가 뜰 때는 당황하지 말고 CTE를 하나씩 다시 실행하면서 점검해 보자.
실전에서 만나면 15분 컷을 내야 한다...!

WITH recursive all_month
AS
  (
         SELECT
                CASE
                       WHEN min(join_date) >= '2020-01-01' THEN '2020-01-01'
                       ELSE min(join_date)
                end AS "first"
         FROM   drivers
         UNION ALL
         SELECT date_add(first, INTERVAL 1 month)
         FROM   all_month
         WHERE  first < '2020-11-30'),
  accepted_count
AS
  (
             SELECT     date_format(requested_at,'%Y-%m') AS "month",
                        count(DISTINCT driver_id)         AS "cnt"
             FROM       rides r
             INNER JOIN acceptedrides a
             ON         r.ride_id = a.ride_id
             GROUP BY   1),
  a
AS
  (
            SELECT    a.first,
                      count(driver_id) AS "cnt_driver"
            FROM      all_month a
            LEFT JOIN drivers d
            ON        date_format(a.first,'%Y-%m') = date_format(d.join_date,'%Y-%m')
            GROUP BY  1),
  b
AS
  (
           SELECT   first,
                    cnt_driver,
                    sum(cnt_driver) over(ORDER BY first) AS "total_driver"
           FROM     a)
  SELECT    month(b.first)                            AS "month",
            ifnull(round(100.0*cnt/total_driver,2),0) AS "working_percentage"
  FROM      b
  LEFT JOIN accepted_count ac
  ON        date_format(b.first,'%Y-%m') = ac.month
  WHERE     year(first) = '2020';