240713_TIL

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

SQL 코드카타

문제 링크

1. 회사 전체의 월 평균 salary를 company CTE에 저장
2. company에 salary와 Employee 테이블을 left join해서 부서(department_id)별 평균을 구하고 결과를 result CTE에 저장
3. result CTE로부터 case when 구문을 써서 본 쿼리 작성

WITH company
     AS (SELECT Date_format(pay_date, '%Y-%m') AS "pay_month",
                Avg(amount)                    AS "avg_company"
         FROM   Salary
         GROUP  BY 1),
     result
     AS (SELECT pay_month,
                e.department_id,
                Avg(s.amount) AS "avg_dept",
                avg_company
         FROM   company c
                LEFT JOIN Salary s
                       ON c.pay_month = Date_format(s.pay_date, '%Y-%m')
                LEFT JOIN Employee e
                       ON s.employee_id = e.employee_id
         GROUP  BY 1,
                   2)
SELECT pay_month,
       department_id,
       CASE
         WHEN avg_dept > avg_company THEN "higher"
         WHEN avg_dept = avg_company THEN "same"
         WHEN avg_dept < avg_company THEN "lower"
       END AS comparison
FROM   result;

정답 쿼리를 쓸 때 처음부터 저렇게 양식을 다 갖춰서 쓰는 건 아니고,
일단 답만 맞게 낸 다음에 쿼리는 아래 사이트에서 보기 좋게 포맷을 바꾼다.

https://www.dpriver.com/pp/sqlformat.htm

Database를 MySQL로 선택한 다음 [Format SQL] 버튼을 클릭하면
알아서 들여쓰기나 줄바꿈 등이 조절된다.
쿼리의 가독성을 높이고 싶은데 일일이 들여쓰기나 대소문자 구분을 하는 게 귀찮다면
유용하게 쓸 수 있을 사이트.

문제 링크
이 문제는 어제 코드카타에서 풀었던 세 번째 문제와 비슷하다.

그루핑의 조건을 주기 위해 CTE에서 row_id를 먼저 만들어 주고,
본 쿼리에서 group by의 기준으로 log_id - row_id 값을 사용하는 방식.

WITH result
     AS (SELECT log_id,
                Row_number()
                  OVER(
                    ORDER BY log_id) AS "row_id"
         FROM   Logs)
SELECT Min(log_id) AS "start_id",
       Max(log_id) AS "end_id"
FROM   result
GROUP  BY log_id - row_id;

문제 링크

WITH team_size
     AS (SELECT team_id,
                Count(employee_id) AS "team_size"
         FROM   Employee
         GROUP  BY 1)
SELECT e.employee_id,
       t.team_size
FROM   Employee e
       LEFT JOIN team_size t
              ON e.team_id = t.team_id;

문제 링크
기본적인 window 함수 문제.

SELECT gender,
       day,
       Sum(Sum(score_points))
         OVER(
           partition BY gender
           ORDER BY day) AS "total"
FROM   Scores
GROUP  BY 1,
          2
ORDER  BY 1,
          2;

문제 링크

SELECT ad_id,
       Round(Ifnull(100.0 * Count(CASE
                                    WHEN action = 'Clicked' THEN action
                                  end) / Count(CASE
                                                 WHEN action = 'Clicked'
                                                       OR action = 'Viewed' THEN
                                                 action
                                               end), 0), 2) AS "ctr"
FROM   Ads
GROUP  BY 1
ORDER  BY 2 DESC,
          1;

다행히 크게 어려운 문제가 안 걸려서 시간이 오래 걸리진 않았다.
오늘 코드카타는 여기까지.