SQL Query

리트코드 SQL 문제 풀이

✅ 1907 Count Salary Categories

+-------------+------+
| Column Name | Type |
+-------------+------+
| account_id | int |
| income | int |
+-------------+------+
account_id is the primary key (column with unique values) for this table.
Each row contains information about the monthly income for one bank account.
Write a solution to calculate the number of bank accounts for each salary category. The salary categories are:
"Low Salary": All the salaries strictly less than $20000.
"Average Salary": All the salaries in the inclusive range [$20000, $50000].
"High Salary": All the salaries strictly greater than $50000.
The result table must contain all three categories. If there are no accounts in a category, return 0.
Return the result table in any order.
The result format is in the following example.

SELECT 'Low Salary' AS category, COUNT(IF(income < 20000, 1, NULL)) AS accounts_count FROM Accounts 
UNION ALL
SELECT 'Average Salary' AS category, COUNT(IF(income>=20000 AND income<=50000, 1, NULL)) AS accounts_count FROM Accounts
UNION ALL
SELECT 'High Salary' AS category, COUNT(IF(income > 50000, 1, NULL)) AS acoounts_count FROM Accounts

해당 문제의 핵심은, UNION ALL을 활용한 행 결합과 IF함수를 활용하여 값이 충족되지 않으면 NULL로 처리하는 과정이다.

✅ 626. Exchange Seats

SELECT
    id, 
    CASE
     WHEN MOD(id, 2) = 0 THEN LAG(student) OVER (ORDER BY id) 
     ELSE IFNULL(LEAD(student) OVER (ORDER BY id), student)
    END AS student
FROM Seat

id를 기준으로 연속적인 학생들의 자리를 바꿔준다.
만일, 끝 번호가 홀수인 경우 자리를 바꾸지 않는다.

위 조건을 만족할 수 있도록, CASE 문구를 활용해 조건을 활성화시킨다.
우선 짝수 인원에 대해 이전 id를 가진 학생의 이름을 가져오고,
홀수 인원의 경우 마지막 인원 처리를 위해 IFNULL을 활용한다.

✅ 1341. Movie Rating

Write a solution to:

• Find the name of the user who has rated the greatest number of movies. In case of a tie, return the lexicographically smaller user name.
• Find the movie name with the highest average rating in February 2020. In case of a tie, return the lexicographically smaller movie name.
  The result format is in the following example.

WITH CTE AS ((
    SELECT B.name AS name, COUNT(C.rating) as rating
    FROM Users B
    LEFT JOIN MovieRating C ON B.user_id = C.user_id 
    GROUP BY B.name
    ORDER BY rating DESC, name 
    LIMIT 1)
    UNION ALL 
    (SELECT A.title AS name, AVG(rating) as rating
    FROM Movies A
    LEFT JOIN MovieRating C ON A.movie_id = C.movie_id
    WHERE DATE_FORMAT(created_at, '%Y-%m') = '2020-02'
    GROUP BY A.title
    ORDER BY rating DESC, A.title
    LIMIT 1
))
SELECT name AS results
FROM CTE

3개의 테이블을 JOIN하여 영화 평가를 가장 많이 한 사람의 이름과, 2020년 2월 평점이 가장 높은 영화 이름을 출력하는 문제.

편리성을 높히기 위해 CTE 문구로 테이블을 새로 만들고, UNION ALL을 활용해 각각 구해야 하는 답을 테이블로 저장한다.

✅ 1321. Restaurant Growth

• Compute the moving average of how much the customer paid in a seven days window (today + 6 days before) average_amount should be rounded two decimal places.

SELECT 
    visited_on,
    (
        SELECT SUM(amount)
        FROM Customer
        WHERE visited_on BETWEEN DATE_SUB(A.visited_on, INTERVAL 6 DAY) AND A.visited_on
    ) AS amount,
    ROUND((
            SELECT SUM(amount) / 7
            FROM Customer
            WHERE visited_on BETWEEN DATE_SUB(A.visited_on, INTERVAL 6 DAY) AND A.visited_on
        ), 2) AS average_amount
FROM Customer AS A
WHERE visited_on >= (SELECT DATE_ADD(MIN(visited_on), INTERVAL 6 DAY) FROM Customer)
GROUP BY visited_on
ORDER BY visited_on;

핵심 function은 다음과 같다.
DATE_ADD/DATE_SUB(컬럼, INTERVAL 수 (일/주/월 등의 기간)
EX) DATE_SUB(A.visited_on, INTERVAL 6 DAY)
-> 6일 전의 날짜를 의미

최종적으로, 주어진 테이블 내, 7일 기간을 갖는 최소 날짜를 미리 선정하여 7일 전부터 현재 날짜까지의 amount 합과 평균 amount를 구하면 되는 문제.

주의할 사항으로는, 각 amount 통계치를 구할 때엔 (7일전 ~ 현재날짜) 기준이고,
최종적인 group by를 갖는 전체 where 절에는, 최소 날짜에서 7일 후를 더한 날짜부터 계산할 수 있도록 필터를 지정해주어야 한다.