240916_TIL

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

SQL 문제풀이 복습

문제 링크
7/7에 이어 두 번째로 풀었던 문제.
join과 집계함수만으로도 해결할 수 있는 문제였는데,
첫 번째 풀이에서는 공연히 너무 어렵게 접근했구나 싶다.

SELECT DISTINCT f1.follower,
                Count(DISTINCT f2.follower) AS "num"
FROM   follow f1
       LEFT JOIN follow f2
              ON f1.follower = f2.followee
WHERE  f2.followee IS NOT NULL
GROUP  BY 1
ORDER  BY 1;

문제 링크
난이도는 hard지만 별로 어렵지는 않은 문제.

월별/부서별로 salary 평균을 구하고
월별/회사 전체 salary 평균을 구한 후
join해서 case when 구문으로 처리하면 된다.

WITH a
     AS (SELECT Date_format(pay_date, '%Y-%m') AS "pay_month",
                department_id,
                Avg(amount)                    AS "pay_dept"
         FROM   salary s
                JOIN employee e
                  ON s.employee_id = e.employee_id
         GROUP  BY 1,
                   2),
     b
     AS (SELECT Date_format(pay_date, '%Y-%m') AS "pay_month",
                Avg(amount)                    AS "pay_company"
         FROM   salary
         GROUP  BY 1)
SELECT a.pay_month,
       a.department_id,
       CASE
         WHEN pay_dept > pay_company THEN 'higher'
         WHEN pay_dept = pay_company THEN 'same'
         WHEN pay_dept < pay_company THEN 'lower'
       END AS "comparison"
FROM   a
       JOIN b
         ON a.pay_month = b.pay_month;

문제 링크
피벗테이블 만드는 문제.
max와 case when을 조합하면
피벗테이블의 기본적인 형태를 만들 수 있다.

다만 여기서는 출력의 결과가 두 줄 이상이므로
줄을 구분할 수 있게 row_number를 써 주고,
이름의 오름차순으로 정렬되어야 하므로 order by name
으로 조건을 표시해 주었다.
마지막 group by의 기준을 row_id로 주기만 하면 완성.

SELECT Max(CASE
             WHEN continent = 'America' THEN NAME
           END) AS "America",
       Max(CASE
             WHEN continent = 'Asia' THEN NAME
           END) AS "Asia",
       Max(CASE
             WHEN continent = 'Europe' THEN NAME
           END) AS "Europe"
FROM   (SELECT *,
               Row_number()
                 OVER(
                   partition BY continent
                   ORDER BY NAME) AS "row_id"
        FROM   student) a
GROUP  BY row_id;