240727_TIL

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

SQL 코드카타

문제 링크
conversion rate가 똑같이 0이어도
방문 횟수가 0이어서 0인 건지, 방문은 했는데 구매가 없어서 0인 건지
구분해야 하기 때문에 방문 횟수 / 구매 횟수를 구분해서 count 잘 해줘야 함.

그것만 아니면 다른 어려운 건 없는 문제.

WITH visit
     AS (SELECT member_id,
                Count(*) AS "cnt_visit"
         FROM   visits
         GROUP  BY 1),
     purchase
     AS (SELECT v.member_id,
                Count(p.visit_id) AS "cnt_purchase"
         FROM   visits v
                INNER JOIN purchases p
                        ON v.visit_id = p.visit_id
         GROUP  BY 1),
     result
     AS (SELECT m.member_id,
                m.NAME,
                Ifnull(cnt_visit, 0)                        AS "cnt_visit",
                Ifnull(100.0 * cnt_purchase / cnt_visit, 0) AS "cvr"
         FROM   members m
                LEFT JOIN visit v
                       ON m.member_id = v.member_id
                LEFT JOIN purchase p
                       ON v.member_id = p.member_id)
SELECT member_id,
       NAME,
       CASE
         WHEN cnt_visit = 0 THEN "Bronze"
         WHEN cnt_visit <> 0
              AND cvr < 50 THEN "Silver"
         WHEN cvr >= 50
              AND cvr < 80 THEN "Gold"
         WHEN cvr >= 80 THEN "Diamond"
       END AS "category"
FROM   result;

문제 링크
어제 풀었던 것과 크게 다르지 않은 문제.
sum over를 써서 최대로 고용할 수 있는 senior부터 먼저 구해놓고
70,000에서 senior 고용에 들어간 비용을 빼고 나머지를 junior로 채우면 된다.

WITH h_senior
     AS (SELECT employee_id,
                salary,
                Sum(salary)
                  OVER(
                    ORDER BY salary) AS "total_senior"
         FROM   candidates
         WHERE  experience = 'Senior'),
     h_junior
     AS (SELECT employee_id,
                salary,
                Sum(salary)
                  OVER(
                    ORDER BY salary) AS "total_junior"
         FROM   candidates
         WHERE  experience = 'Junior'),
     max_senior
     AS (SELECT employee_id,
                total_senior
         FROM   h_senior
         WHERE  total_senior < 70000),
     max_junior
     AS (SELECT employee_id
         FROM   h_junior
         WHERE  total_junior <= 70000 - (SELECT Ifnull(Max(total_senior), 0)
                                         FROM   max_senior))
SELECT employee_id
FROM   max_senior
UNION
SELECT employee_id
FROM   max_junior;

문제 링크

SELECT Count(DISTINCT s.account_id) AS "accounts_count"
FROM   streams s
       LEFT JOIN (SELECT account_id
                  FROM   subscriptions
                  WHERE  Year(start_date) <> '2021'
                         AND Year(end_date) <> '2021') a
              ON s.account_id = a.account_id
WHERE  Year(stream_date) <> '2021'
       AND a.account_id IS NULL;

문제 링크

WITH result
     AS (SELECT problem_id,
                100.0 * likes / ( likes + dislikes ) AS "pct"
         FROM   problems
         GROUP  BY 1
         HAVING pct < 60)
SELECT problem_id
FROM   result
ORDER  BY 1;

문제 링크
쿼리 자체가 복잡하지는 않았는데
한 번도 접해본 적이 없어서 더 어렵게 느껴진 hard 문제.

1. group_concat 함수를 쓸 때
   아무런 인자를 넣지 않으면 자동으로 ,로 구분되는 점 참고
2. Post 테이블과 Keywords 테이블에 join을 위한 키 컬럼이 없어서 word와 content를 기준으로 join을 수행하되,
   • 예를 들어 war와 warning처럼 글자만 일치하는 경우를 join의 조건으로 인식하는 것을 방지하기 위해 '% '와 ' %'로 word의 앞뒤를 감싸서 독립된 단어로 사용했을 때에만 key로서 인식되게 하고,
   • 마찬가지로 content 내에서도 특정 단어로서 사용되었을 때에만 join의 키로 인식하게끔 하기 위해 content의 앞뒤를 공백으로 감싼다.
3. 본 쿼리에서 group_concat을 쓸 때 order by topic_id를 써서 3,1이 아니라 1,3으로 정렬되게끔 해 주고, null일 경우 Ambiguous!를 뱉어야 하므로 ifnull 구문을 써 준다.

아래는 완성된 정답 쿼리.

SELECT p.post_id,
       Ifnull(Group_concat(DISTINCT k.topic_id ORDER BY
       k.topic_id), 'Ambiguous!') AS
       "topic"
FROM   posts p
       LEFT JOIN keywords AS k
              ON Concat(' ', Lower(p.content), ' ') LIKE
                 Concat('% ', Lower(k.word), ' %')
GROUP  BY 1;