241129_TIL

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

SQL 문제풀이 복습

Leetcode

문제 링크
having절 안에 서브쿼리를 길게 넣기가 애매해서
cte를 만들어서 해결한 문제.
문제 자체는 그렇게 어렵지 않다.

WITH result
     AS (SELECT v.candidate,
                Sum(vote) AS "total_vote"
         FROM   Votes v
                JOIN (SELECT voter,
                             1 / Count(*) AS "vote"
                      FROM   Votes
                      WHERE  candidate IS NOT NULL
                      GROUP  BY 1) a
                  ON v.voter = a.voter
         GROUP  BY 1)
SELECT candidate
FROM   result
WHERE  total_vote = (SELECT Max(total_vote)
                     FROM   result)
ORDER  BY 1;

문제 링크

SELECT u.user_id,
       u.name,
       SUM(IFNULL(distance, 0)) AS "traveled distance"
FROM   Users u
       LEFT JOIN Rides r
              ON u.user_id = r.user_id
GROUP  BY 1
ORDER  BY 1;

문제 링크
Engineering 따로, Marketing 따로 구해서 풀 수도 있지만,
window함수를 쓰면 한 번에 깔끔하게 해결 가능.

SELECT Max(salary) - Min(salary) AS "salary_difference"
FROM   (SELECT department,
               salary,
               RANK()
                 OVER(
                   partition BY department
                   ORDER BY salary DESC) AS "ranking"
        FROM   Salaries
        WHERE  department IN ( 'Engineering', 'Marketing' )) result
WHERE  ranking = 1;