241020_TIL

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

SQL 문제풀이 복습

---

Leetcode

문제 링크
least와 greatest 함수를 알면 쉽고
모르면 마냥 어려울 수밖에 없는..문제.
(아마도 join을 써서 조건을 복잡하게 줘야 할 듯?)

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;

문제 링크
1트(7/16) 때는 괜히 복잡하게
CTE를 3개씩이나 만들면서 풀었었는데
window 함수(lead)와 ifnull, 그리고 서브쿼리를 조합하면
한 번에 해결할 수 있는 문제다.

아마도 저 때는 lag만 알았든지
lead를 미처 떠올리지 못했었든지 그랬던 듯.
여러 가지 개념들을 섞어 쓸 수 있는 좋은 문제였다.

SELECT user_id,
       Max(windows) AS "biggest_window"
FROM   (SELECT user_id, Datediff(ifnull(lead(visit_date) OVER(partition BY
       user_id
       ORDER BY visit_date), '2021-01-01'), visit_date) AS "windows"
        FROM   UserVisits) a
GROUP  BY 1
ORDER  BY 1;

문제 링크
이 문제(7/20)도 그렇지만
전체적으로 1트 때는 특히나 쿼리가 불필요하게 복잡한 느낌.
간단하게 구할 수 있는 문제라면 간단하게 구하자.

SELECT sum(b.apple_count)
       + sum(ifnull(c.apple_count, 0))  AS "apple_count",
       sum(b.orange_count)
       + sum(ifnull(c.orange_count, 0)) AS "orange_count"
FROM   Boxes b
       LEFT JOIN Chests c
              ON b.chest_id = c.chest_id;

---

프로그래머스

문제 링크

SELECT Year(ym)               AS "YEAR",
       Round(Avg(pm_val1), 2) AS "PM10",
       Round(Avg(pm_val2), 2) AS "PM2.5"
FROM   air_pollution
WHERE  location2 = '수원'
GROUP  BY 1
ORDER  BY 1;

문제 링크

SELECT e.dept_id,
       d.dept_name_en,
       Round(Avg(sal), 0) AS "AVG_SAL"
FROM   HR_EMPLOYEES e
       JOIN HR_DEPARTMENT d
         ON e.dept_id = d.dept_id
GROUP  BY 1,
          2
ORDER  BY 3 DESC;

문제 링크
난이도가 높은 것처럼 표시되어 있지만
사실 비슷한 medium이라도 Leetcode 쪽이 훨씬 어렵다.
join과 서브쿼리, 집계함수를 조합한 문제인데
공연히 case when을 여러 번 써야 하는 게 귀찮을 뿐.

SELECT e.emp_no,
       e.emp_name,
       CASE
         WHEN g.score >= 96 THEN 'S'
         WHEN g.score >= 90 THEN 'A'
         WHEN g.score >= 80 THEN 'B'
         ELSE 'C'
       end AS "GRADE",
       CASE
         WHEN g.score >= 96 THEN sal * 0.2
         WHEN g.score >= 90 THEN sal * 0.15
         WHEN g.score >= 80 THEN sal * 0.1
         ELSE sal * 0
       end AS "BONUS"
FROM   HR_EMPLOYEES e
       JOIN (SELECT emp_no,
                    Avg(score) AS 'score'
             FROM   HR_GRADE
             GROUP  BY 1) g
         ON e.emp_no = g.emp_no
ORDER  BY 1;

문제 링크
서브쿼리를 인라인뷰로도 쓰고,
where절에도 쓰는 연습을 해 봤다.
기본적인 문법에서 크게 어려울 건 없음.
다만 1트(6/25) 때에도 느꼈던 거지만
엄청 쫀쫀하게 출제된 문제 같진 않다.

예를 들어 total_score의 조건을 서브쿼리로 구할 때
처음에는 where year = '2022'를 빼먹었는데도 정답 처리됐는데,
사실 HR_GRADE 테이블 안에 2022년이 아닌
다른 년도의 score가 들어가 있었을지도 모르기 때문에
엄밀하게는 2022로 조건을 제한해야 맞을 테다.

Leetcode도 그렇고 프로그래머스도 사람이 내는 문제인지라
어느 정도의 허술함이나 오류가 있을 수도 있다는 점은 감안해야 할 듯.

SELECT total_score AS "SCORE",
       e.emp_no,
       e.emp_name,
       e.position,
       e.email
FROM   HR_EMPLOYEES e
       JOIN (SELECT emp_no,
                    Sum(score) AS "total_score"
             FROM   HR_GRADE
             GROUP  BY 1) a
         ON e.emp_no = a.emp_no
WHERE  total_score = (SELECT Sum(score)
                      FROM   HR_GRADE
                      WHERE  YEAR = '2022'
                      GROUP  BY emp_no
                      ORDER  BY 1 DESC
                      LIMIT  1);

문제 링크
프로그래머스가 Leetcode와 구분되는 가장 큰 특징...이라면
비트 연산 문제를 지독히도 좋아한다는 것.

이 문제에서 조심해야 할 것은
category를 비교할 때는 sum(code)를 해 줘야 한다는 점.
그래야 똑같은 프론트엔드지만 기술명은 다른 경우를
다 커버할 수 있다...고 한다.

일단 차트에는 정리해 놨지만
비트 연산에 시간을 더 투입해서 숙련도를 높이는 게
맞는 건지 모르겠네. 개발자도 아닌데 흠🤔

SELECT ( CASE
           WHEN ( skill_code & (SELECT Sum(code)
                                FROM   SKILLCODES
                                WHERE  category LIKE 'FRONT%') )
                AND skill_code & (SELECT code
                                  FROM   SKILLCODES
                                  WHERE  name = 'PYTHON') THEN 'A'
           WHEN skill_code & (SELECT code
                              FROM   SKILLCODES
                              WHERE  name = 'C#') THEN 'B'
           WHEN skill_code & (SELECT Sum(code)
                              FROM   SKILLCODES
                              WHERE  category LIKE 'FRONT%') THEN 'C'
           ELSE NULL
         end ) AS GRADE,
       id,
       email
FROM   DEVELOPERS
GROUP  BY 1,
          2,
          3
HAVING grade IS NOT NULL
ORDER  BY 1,
          2;