240716_TIL

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

SQL 코드카타

문제 링크

SELECT w.name                                       AS "warehouse_name",
       Sum(w.units * p.width * p.length * p.height) AS "volume"
FROM   warehouse w
       INNER JOIN products p
               ON w.product_id = p.product_id
GROUP  BY 1;

문제 링크

WITH apple
     AS (SELECT sale_date,
                sold_num AS "sold_apple"
         FROM   sales
         WHERE  fruit = 'apples'),
     oranges
     AS (SELECT sale_date,
                sold_num AS "sold_orange"
         FROM   sales
         WHERE  fruit = 'oranges')
SELECT a.sale_date,
       sold_apple - sold_orange AS "diff"
FROM   apple a
       LEFT JOIN oranges o
              ON a.sale_date = o.sale_date
GROUP  BY 1;

문제 링크
LEAST와 GREATEST 함수를 처음 써 봤던 문제.

아래의 예시처럼 (from_id, to_id) 조합이 (1,2)가 됐든 (2,1)이 됐든
통화를 서로 한 것이기 때문에 같은 조합으로 보고,
count도 동일한 건으로 간주해서 세고 duration도 합쳐줘야 한다.

처음에는 조건을 복잡하게 걸어서 join을 해야 하나 싶었는데,
join된 결과를 놓고도 from_id와 to_id를 뒤집어야 하는 문제가 남는다.
이 문제의 킥은 반대로 놓여있는 from_id와 to_id를
어떻게 from_id 기준으로 열끼리도 오름차순 정렬하고 행끼리도 오름차순 정렬하냐는 것.
LEAST와 GREATEST를 섞어서 쓰면 간단하게 해결할 수 있다.

이 함수가 어떤 방식으로 작동하냐면,

SELECT LEAST(from_id, to_id) as "person1",
       GREATEST(from_id, to_id) as "person2"
FROM Calls

이렇게 된다.

즉, 같은 row에 있는 from_id와 to_id를 조합한 뒤
작은 값을 뱉는 게 LEAST, 큰 값을 뱉는 게 GREATEST다.
따라서 이 함수를 쓰면 자동으로 문제에서 원하는 출력결과처럼
(작은 id, 큰 id) 순으로 정렬된다.
남은 건 간단하게 count와 sum을 해 주는 것. 아래는 정답 쿼리.

SELECT Least(from_id, to_id)    AS "person1",
       Greatest(from_id, to_id) AS "person2",
       Count(*)                 AS "call_count",
       Sum(duration)            AS "total_duration"
FROM   calls
GROUP  BY 1,
          2;

문제 링크

WITH result
     AS (SELECT c.NAME                                      AS "customer_name",
                o.customer_id,
                o.order_id,
                o.order_date,
                Row_number()
                  OVER(
                    partition BY customer_id
                    ORDER BY order_date DESC, order_id ASC) AS "ranking"
         FROM   orders o
                INNER JOIN customers c
                        ON o.customer_id = c.customer_id)
SELECT customer_name,
       customer_id,
       order_id,
       order_date
FROM   result
WHERE  ranking <= '3'
ORDER  BY 1,
          2,
          4 DESC;

문제 링크

WITH june
     AS (SELECT o.customer_id,
                Date_format(o.order_date, '%Y-%m') AS "ym",
                Sum(p.price * o.quantity)          AS "spending"
         FROM   orders o
                INNER JOIN product p
                        ON o.product_id = p.product_id
         GROUP  BY 1,
                   2
         HAVING ym = '2020-06'
                AND spending >= 100),
     july
     AS (SELECT o.customer_id,
                Date_format(o.order_date, '%Y-%m') AS "ym",
                Sum(p.price * o.quantity)          AS "spending"
         FROM   orders o
                INNER JOIN product p
                        ON o.product_id = p.product_id
         GROUP  BY 1,
                   2
         HAVING ym = '2020-07'
                AND spending >= 100),
     list
     AS (SELECT june.customer_id
         FROM   june
                INNER JOIN july
                        ON june.customer_id = july.customer_id)
SELECT l.customer_id,
       c.NAME
FROM   list l
       INNER JOIN customers c
               ON l.customer_id = c.customer_id;

문제 링크

1. caller_id와 duration, callee_id와 duration을 묶어서 a에 저장
2. CTE a에 person과 country 테이블을 join해서 국가를 알아낸 후,
3. 국가별 평균이 전세계 평균(having절의 서브쿼리)보다 큰 경우만 필터링

WITH a
     AS (SELECT caller_id,
                duration
         FROM   calls
         UNION ALL
         SELECT callee_id,
                duration
         FROM   calls)
SELECT c.NAME AS "country"
FROM   a
       INNER JOIN person p
               ON a.caller_id = p.id
       INNER JOIN country c
               ON LEFT(p.phone_number, 3) = c.country_code
GROUP  BY 1
HAVING Avg(a.duration) > (SELECT Avg(duration)
                          FROM   a);

문제 링크

WITH june
     AS (SELECT content_id,
                channel
         FROM   tvprogram
         WHERE  Date_format(program_date, '%Y-%m') = '2020-06')
SELECT DISTINCT title
FROM   content c
       INNER JOIN june j
               ON c.content_id = j.content_id
WHERE  kids_content = 'Y'
       AND content_type = 'movies';

문제 링크

WITH result
     AS (SELECT year,
                wimbledon AS "player_id"
         FROM   championships
         UNION ALL
         SELECT year,
                fr_open
         FROM   championships
         UNION ALL
         SELECT year,
                us_open
         FROM   championships
         UNION ALL
         SELECT year,
                au_open
         FROM   championships)
SELECT r.player_id,
       player_name,
       Count(*) AS "grand_slams_count"
FROM   result r
       LEFT JOIN players p
              ON r.player_id = p.player_id
GROUP  BY 1;

문제 링크

WITH difftoday
     AS (SELECT user_id, Datediff('2021-01-01', Max(visit_date)) AS "diff"
         FROM   uservisits GROUP BY 1),
     result
     AS (SELECT user_id,
                visit_date,
                Lag(visit_date)
                  OVER(
                    partition BY user_id
                    ORDER BY visit_date) AS "previous_visit"
         FROM   uservisits),
     total
     AS (SELECT user_id,
                Ifnull(Datediff(visit_date, previous_visit), 0) AS "diff"
         FROM   result
         UNION ALL
         SELECT user_id,
                diff
         FROM   difftoday
         ORDER  BY 1)
SELECT user_id,
       Max(diff) AS biggest_window
FROM   total
GROUP  BY 1;

오늘은 오전 내내 SQL 코드카타에만 집중해서 9문제를 풀었다.
Advanced SQL 50 완료까지 5문제 남았는데, 내일 오전이면 다 해결될 것 같다.