114번

문제

모든 연속된 두 학생씩 seat id를 서로 바꾼다. 학생 수가 홀수이면 마지막 학생의 id는 바꾸지 않는다.

결과 테이블은 id 오름차순으로 정렬해서 반환한다.

결과 형식은 아래 예시와 같다.

Input

id	student
1	Abbot
2	Doris
3	Emerson
4	Green
5	Jeames

Output

id	student
1	Doris
2	Abbot
3	Green
4	Emerson
5	Jeames

---

코드를 이것저것 만져보다가 LAG과 LEAD를 사용하면 해결할 수 있을 것 같았다.
거기에 IF를 추가하고 (잘 사용하지 않지만 이번에 사용해봤다.) COALESCE로 null이 되는 것을 방지했다.

SELECT id, IF(id%2 = 1, COALESCE(lead(student) over(), student), lag(student) over()) as student
FROM Seat

115번

문제

가장 많은 영화에 평점을 남긴 사용자의 이름을 찾아라. 만약 동점이 있으면, 사전순으로 더 앞선 이름을 반환한다.

2020년 2월에 평균 평점이 가장 높은 영화 이름을 찾아라. 만약 동점이 있으면, 사전순으로 더 앞선 영화 이름을 반환한다.

결과 형식은 아래 예시와 같다.

Input

Movies

movie_id	title
1	Avengers
2	Frozen 2
3	Joker

Users

user_id	name
1	Daniel
2	Monica
3	Maria
4	James

MovieRating

movie_id	user_id	rating	created_at
1	1	3	2020-01-12
1	2	4	2020-02-11
1	3	2	2020-02-12
1	4	1	2020-01-01
2	1	5	2020-02-17
2	2	2	2020-02-01
2	3	2	2020-03-01
3	1	3	2020-02-22
3	2	4	2020-02-25

Output

results
Daniel
Frozen 2

---

문제 핵심은 UNION ALL을 사용한다는 것인데, 두 가지 쿼리를 하나로 반환하는 것이 필요했다.

WITH a AS(
    SELECT u.name, count(m.user_id) as cnt
    FROM MovieRating m
    JOIN Users u
    ON m.user_id = u.user_id
    GROUP BY m.user_id
    ORDER BY cnt DESC, u.name ASC LIMIT 1
)
SELECT name AS results
FROM a
UNION ALL
SELECT title
FROM
(
    SELECT mv.title, AVG(mr.rating) as avg_rate
    FROM MovieRating mr
    JOIN Movies mv
    ON mr.movie_id = mv.movie_id
    WHERE created_at BETWEEN '2020-02-01' AND '2020-02-29'
    GROUP BY mr.movie_id
    ORDER BY avg_rate DESC, mv.title ASC LIMIT 1
) AS b

답이 조금 지저분한데, 이건 ORDER BY절에 AVG(), COUNT()를 사용하는 방법을 알았으면 더 깔끔하게 풀 수 있었다.
ORDER BY절에도 GROUP BY가 있으면 집계함수 사용이 가능하다는 점을 새로 배웠다.

116번

문제

당신은 식당 주인이고, 매장 확장을 고려하기 위해 데이터를 분석하려고 한다 (매일 최소 한 명의 손님은 있다고 가정한다).

손님이 지불한 금액에 대해 7일간 이동 평균을 계산하라. (즉, 해당 일자 + 이전 6일)
average_amount는 소수점 둘째 자리까지 반올림해야 한다.

결과 테이블은 visited_on을 기준으로 오름차순 정렬하여 반환한다.

결과 형식은 아래 예시와 같다.

Input

Customer

customer_id	name	visited_on	amount
1	Jhon	2019-01-01	100
2	Daniel	2019-01-02	110
3	Jade	2019-01-03	120
4	Khaled	2019-01-04	130
5	Winston	2019-01-05	110
6	Elvis	2019-01-06	140
7	Anna	2019-01-07	150
8	Maria	2019-01-08	80
9	Jaze	2019-01-09	110
1	Jhon	2019-01-10	130
3	Jade	2019-01-10	150

Output

visited_on	amount	average_amount
2019-01-07	860	122.86
2019-01-08	840	120
2019-01-09	840	120
2019-01-10	1000	142.86

---

너무 어려워서 구글링을 통해 겨우 꾸역꾸역 답안을 작성...

WITH day AS (
    SELECT visited_on, SUM(amount) AS total
    FROM Customer
    GROUP BY visited_on
)
, window AS (
    SELECT
        visited_on,
        SUM(total) OVER (
            ORDER BY visited_on
            RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW
        ) AS amount,
        COUNT(*) OVER (
            ORDER BY visited_on
            RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW
        ) AS day_window
    FROM day
)
SELECT
    visited_on,
    amount,
    ROUND(amount / 7, 2) AS average_amount
FROM window
WHERE day_window = 7
ORDER BY visited_on