240714_TIL

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

SQL 코드카타

문제 링크
recursive CTE를 써서 해결한 난이도 hard 문제.

visit_count가 없을 때 0으로 채우더라도
transaction_count가 연속적으로 나타나야 하기 때문에,
minmax와 fullcount라는 CTE를 만들어서 transaction_count를 억지로 채워주었다.

사실상 답을 내는 데 필요한 CTE는
semi_result와 fullcount인데, 다른 CTE들은
이 둘을 만들기 위해 추가로 들어간 경우.

재귀 CTE를 만드는 방법은 지난 일요일에 풀었던 코드카타와
거의 동일하다. 빈 행을 메꿔야 할 때 기억해 두어야 할 듯.

WITH recursive non_trans
AS
  (
            SELECT    count(t.user_id) AS "transactions_count",
                      count(v.user_id) AS "visits_count"
            FROM      visits v
            LEFT JOIN transactions t
            ON        v.user_id = t.user_id
            AND       v.visit_date = t.transaction_date
            WHERE     t.user_id IS NULL),
  trans
AS
  (
            SELECT    t.user_id,
                      t.transaction_date,
                      count(t.transaction_date) AS "transactions_count"
            FROM      visits v
            LEFT JOIN transactions t
            ON        v.user_id = t.user_id
            AND       v.visit_date = t.transaction_date
            WHERE     t.user_id IS NOT NULL
            GROUP BY  1,
                      2),
  semi_result
AS
  (
           SELECT   transactions_count,
                    count(*) AS "visits_count"
           FROM     trans
           GROUP BY 1
           UNION
           SELECT *
           FROM   non_trans),
  minmax
AS
  (
         SELECT min(transactions_count) AS "mincount",
                max(transactions_count) AS "maxcount"
         FROM   semi_result),
  fullcount
AS
  (
         SELECT mincount
         FROM   minmax
         UNION ALL
         SELECT fullcount.mincount+1
         FROM   fullcount
         JOIN   minmax
         ON     fullcount.mincount < minmax.maxcount)
  SELECT    f.mincount               AS "transactions_count",
            ifnull(s.visits_count,0) AS "visits_count"
  FROM      fullcount f
  LEFT JOIN semi_result s
  ON        f.mincount = s.transactions_count;

문제 링크

SELECT s.id,
       s.name
FROM   students s
       LEFT JOIN departments d
              ON s.department_id = d.id
WHERE  d.id IS NULL;

문제 링크

WITH cnt_participants
     AS (SELECT activity,
                Count(id) AS cnt
         FROM   friends
         GROUP  BY 1)
SELECT c.activity
FROM   activities a
       INNER JOIN cnt_participants c
               ON a.NAME = c.activity
WHERE  cnt <> (SELECT Min(cnt)
               FROM   cnt_participants)
       AND cnt <> (SELECT Max(cnt)
                   FROM   cnt_participants); 

문제 링크

WITH a
     AS (SELECT i.invoice_id,
                c.customer_name,
                i.price,
                Count(ct.user_id) AS "contacts_cnt"
         FROM   invoices i
                LEFT JOIN customers c
                       ON i.user_id = c.customer_id
                LEFT JOIN contacts ct
                       ON c.customer_id = ct.user_id
         GROUP  BY 1,
                   2,
                   3
         ORDER  BY 1),
     b
     AS (SELECT c.customer_name,
                Count(ct.contact_name) AS "trusted_contacts_cnt"
         FROM   customers c
                LEFT JOIN contacts ct
                       ON c.customer_id = ct.user_id
         WHERE  ct.contact_name IN (SELECT DISTINCT customer_name
                                    FROM   customers)
         GROUP  BY 1)
SELECT a.invoice_id,
       a.customer_name,
       a.price,
       a.contacts_cnt,
       Ifnull(b.trusted_contacts_cnt, 0) AS "trusted_contacts_cnt"
FROM   a
       LEFT JOIN b
              ON a.customer_name = b.customer_name;

문제 링크
표시된 난이도는 hard였는데 딱히 어렵진 않은 문제.
활동을 하나밖에 안 한 케이스를 one_activity CTE에 저장하고,
2개 이상일 경우 row_number를 써서 최근순으로 번호를 부여한 다음 2인 경우만 뽑으면 된다.

정답은 활동을 1개만 한 경우의 정보 + 2개 이상 한 경우 중 2번째로 최근에 했던 활동의 정보를 union해서 출력하면 됨.

WITH one_activity
     AS (SELECT *
         FROM   Useractivity
         GROUP  BY username
         HAVING Count(*) = 1),
     result
     AS (SELECT username,
                activity,
                startdate,
                enddate,
                Row_number()
                  OVER(
                    partition BY username
                    ORDER BY startdate DESC) AS "ranking"
         FROM   Useractivity)
SELECT username,
       activity,
       startdate,
       enddate
FROM   result
WHERE  ranking = '2'
UNION
SELECT *
FROM   one_activity;

아무 생각없이 문제들을 풀고 있다가 문득 마이페이지를 보니
예전에 했던 SQL50 말고 Advanced SQL50에도 거의 근접한 것 같다. 몇 문제만 더 풀면 배지를 주는 것 같아서, 우선 이것부터 따 놓은 다음에 남은 문제를 풀기로.