240820_TIL

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

SQL 코드카타
최종발표 준비도 어느 정도 마무리되어 가서 오늘은 5문제 해결.

조금씩이었지만 꾸준히 하다보니
Leetcode 프리미엄 문제도 이번 달 중에는 마무리 되겠구만
다음엔 뭘 풀어야 하지..?🤔

문제 링크

WITH result
     AS (SELECT city,
                Avg(price) AS "average"
         FROM   listings
         GROUP  BY 1)
SELECT city
FROM   result
WHERE  average > (SELECT Avg(price)
                  FROM   listings)
ORDER  BY 1;

문제 링크

WITH result
     AS (SELECT dep_id,
                Count(DISTINCT emp_id) AS "cnt"
         FROM   employees
         GROUP  BY 1)
SELECT emp_name AS "manager_name",
       r.dep_id
FROM   result r
       LEFT JOIN employees e
              ON r.dep_id = e.dep_id
WHERE  cnt = (SELECT Max(cnt)
              FROM   result)
       AND e.position = 'Manager'
ORDER  BY 2;

문제 링크

WITH result
     AS (SELECT student_id,
                assignment1 + assignment2 + assignment3 AS "total_score"
         FROM   scores)
SELECT Max(total_score) - Min(total_score) AS "difference_in_score"
FROM   result;

문제 링크

WITH result
     AS (SELECT user_id,
                Count(DISTINCT loan_type) AS "cnt"
         FROM   loans
         WHERE  loan_type IN ( 'Mortgage', 'Refinance' )
         GROUP  BY 1
         HAVING cnt >= 2)
SELECT user_id
FROM   result;

문제 링크
오랜만에 본 피벗테이블 문제.
난이도는 hard라고 되어있지만 차근히 따라가다 보면 크게 어려울 건 없다.

다만 CTE에서 랭킹을 매길 때
'total point가 동률이라면 winery 이름을 기준으로 오름차순하라'는
문제의 조건이 있었는데, 그걸 못 봐서 처음에 오답이 떴었다.
그 부분만 주의하면 될 듯?

WITH result
     AS (SELECT country,
                winery,
                Sum(points)                                AS "total_point",
                Rank()
                  OVER(
                    partition BY country
                    ORDER BY Sum(points) DESC, winery ASC) AS "ranking"
         FROM   wineries
         GROUP  BY 1,
                   2)
SELECT country,
       Max(CASE
             WHEN ranking = '1' THEN Concat(winery, " (", total_point, ")")
           END)                             AS "top_winery",
       Ifnull(Max(CASE
                    WHEN ranking = '2' THEN Concat(winery, " (", total_point,
                                            ")")
                  END), "No second winery") AS "second_winery",
       Ifnull(Max(CASE
                    WHEN ranking = '3' THEN Concat(winery, " (", total_point,
                                            ")")
                  END), "No third winery")  AS "third_winery"
FROM   result
GROUP  BY 1
ORDER  BY 1;