240708_TIL

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

SQL 코드카타
바빠도 할 일은 해야지.

문제 링크

WITH a
     AS (SELECT p.project_id,
                e.employee_id,
                e.experience_years
         FROM   project p
                INNER JOIN employee e
                        ON p.employee_id = e.employee_id),
     b
     AS (SELECT project_id,
                Max(experience_years) AS max_ex
         FROM   a
         GROUP  BY 1)
SELECT a.project_id,
       a.employee_id
FROM   a
       INNER JOIN b
               ON a.project_id = b.project_id
                  AND a.experience_years = b.max_ex;

문제 링크

WITH s8
     AS (SELECT s.buyer_id,
                s.quantity
         FROM   sales s
                INNER JOIN product p
                        ON s.product_id = p.product_id
         WHERE  p.product_name = 'S8'),
     iphone
     AS (SELECT s.buyer_id,
                s.quantity
         FROM   sales s
                INNER JOIN product p
                        ON s.product_id = p.product_id
         WHERE  p.product_name = 'iPhone')
SELECT DISTINCT s8.buyer_id
FROM   s8
       LEFT JOIN iphone
              ON s8.buyer_id = iphone.buyer_id
WHERE  iphone.buyer_id IS NULL; 

문제 링크
문제의 텍스트가 애매하게 쓰여있어서 한참 애먹었다.

Write a solution to report the books
that have sold less than 10 copies in the last year,
excluding books that have been available for
less than one month from today.
Assume today is 2019-06-23.

이렇게 써 놓고 last year를 2018로 쓰면 틀리게 하다니;
알고 보니 Date_sub('2019-06-23', interval 1 year) 조건을 주면 정답이었다🤬

WITH a
     AS (SELECT book_id,
                name
         FROM   books
         WHERE  Timestampdiff(month, available_from, '2019-06-23') >= 1),
     b
     AS (SELECT book_id,
                SUM(quantity) AS "sum"
         FROM   orders
         WHERE  dispatch_date >= Date_sub('2019-06-23', interval 1 year)
         GROUP  BY 1)
SELECT a.book_id,
       a.name
FROM   a
       left join b
              ON a.book_id = b.book_id
WHERE  Ifnull(SUM, 0) < 10;

별 일도 아니었는데 진짜. 아오 시간 아까워라.

문제 링크

WITH a
     AS (SELECT user_id,
                Min(activity_date) AS "first_login"
         FROM   traffic
         WHERE  activity = 'login'
         GROUP  BY 1)
SELECT first_login             AS "login_date",
       Count(DISTINCT user_id) AS "user_count"
FROM   a
WHERE  Timestampdiff(day, first_login, '2019-06-30') <= 90
GROUP  BY 1;

문제 링크

SELECT extra                   AS "report_reason",
       Count(DISTINCT post_id) AS "report_count"
FROM   actions
WHERE  extra IS NOT NULL
       AND action = 'report'
       AND Timestampdiff(day, action_date, '2019-07-05') = 1
GROUP  BY 1;