240902_TIL

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

SQL 코드카타

문제 링크
어제 풀었던 문제와 비슷.

1. CTE를 만들 때 l1.user_id <> l2.user_id로 할 필요 없이 l1.user_id < l2.user_id로만 해 줘도 된다. 어차피 애초에 정렬이 그렇게 되어 있으므로.
2. CTE에서 group by를 쓸 때 count(별)가 아니라 count(distinct l1.song_id)라고 쓴 점에도 주의. 그냥 count(별)로 해버리면 중복되는 행도 count되기 때문에 결과가 잘못 나올 수 있음.

WITH result
     AS (SELECT l1.user_id AS "user1_id",
                l2.user_id AS "user2_id"
         FROM   listens l1
                JOIN listens l2
                  ON l1.user_id < l2.user_id
                     AND l1.song_id = l2.song_id
                     AND l1.day = l2.day
         GROUP  BY l1.user_id,
                   l2.user_id,
                   l1.day
         HAVING Count(DISTINCT l1.song_id) >= 3)
SELECT DISTINCT r.user1_id,
                r.user2_id
FROM   result r
       INNER JOIN friendship f
               ON r.user1_id = f.user1_id
                  AND r.user2_id = f.user2_id;

문제 링크
이것도 예전에 못 풀어서 묵혀놨던 medium 문제인데
오늘 푸니까 이상하게 그냥 풀렸다;;

지난번엔 왜 못풀었던 걸까..

WITH result
     AS (SELECT school_id,
                capacity,
                Max(student_count)     AS "count",
                Ifnull(Min(score), -1) AS "score"
         FROM   schools s
                LEFT JOIN exam e
                       ON s.capacity >= e.student_count
         GROUP  BY 1,
                   2)
SELECT school_id,
       score
FROM   result;

문제 링크
조건이 여러 개 겹쳐 있어서 그렇지
막상 쿼리를 짜는 로직이 그렇게까지 복잡한 건 아니다.

1. 가점/감점을 계산하는 cte
2. 프로젝트가 요구하는 기술 수와 지원자가 해당 프로젝트에서 갖고 있는 기술의 가짓수를 비교하는 cte (candidate_final)
3. 기준점수(100점)를 기준으로 가점/감점을 더해준 다음, 총점 기준으로 내림차순 & candidate_id 기준으로 오름차순해서 랭킹을 뽑아주는 cte (result)
4. result를 기준으로 ranking이 1인 경우만 뽑아주는 본 쿼리

이렇게 4개 파트로 구성해서 정답을 냈다.

WITH cte
     AS (SELECT c.candidate_id,
                c.skill,
                c.proficiency,
                p.project_id,
                p.importance,
                CASE
                  WHEN proficiency > importance THEN 10
                  WHEN proficiency < importance THEN -5
                  WHEN proficiency = importance THEN 0
                END AS "add_score"
         FROM   candidates c
                JOIN projects p
                  ON c.skill = p.skill),
     candidate_final
     AS (SELECT p.project_id,
                c.candidate_id,
                Count(DISTINCT p.skill) AS "cnt_required",
                Count(DISTINCT c.skill) AS "cnt_having",
                cnt_total
         FROM   projects p
                LEFT JOIN candidates c
                       ON p.skill = c.skill
                LEFT JOIN (SELECT project_id,
                                  Count(DISTINCT skill) AS "cnt_total"
                           FROM   projects
                           GROUP  BY 1) temp
                       ON p.project_id = temp.project_id
         GROUP  BY 1,
                   2
         HAVING cnt_total = cnt_required
                AND cnt_required = cnt_having),
     result
     AS (SELECT c.project_id,
                c.candidate_id,
                100 + Sum(add_score)                                      AS
                "score",
                Rank()
                  OVER(
                    partition BY c.project_id
                    ORDER BY 100+Sum(add_score) DESC, c.candidate_id ASC) AS
                "ranking"
         FROM   cte c
                INNER JOIN candidate_final cf
                        ON c.candidate_id = cf.candidate_id
                           AND c.project_id = cf.project_id
         GROUP  BY 1,
                   2)
SELECT project_id,
       candidate_id,
       score
FROM   result
WHERE  ranking = 1
ORDER  BY 1;