1341. Movie Rating
1. 문제
Table: Users
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| user_id | int |
| name | varchar |
+---------------+---------+
user_id is the primary key (column with unique values) for this table.
Table: MovieRating
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| movie_id | int |
| user_id | int |
| rating | int |
| created_at | date |
+---------------+---------+
(movie_id, user_id) is the primary key (column with unique values) for this table.
This table contains the rating of a movie by a user in their review.
created_at is the user's review date.
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.
2. 풀이
WITH user_movie_cnt as
(SELECT user_id, COUNT(DISTINCT movie_id) as movie_cnt
FROM MovieRating
GROUP BY user_id
),
movie_avg as
(SELECT movie_id, AVG(rating) as avg_rating
FROM MovieRating
GROUP BY movie_id
)
SELECT name as results
FROM Users
WHERE user_id =
(SELECT user_id
FROM user_movie_cnt
ORDER BY movie_cnt desc,
LIMIT 1
)
UNION
SELECT title as results
FROM Movies
WHERE movie_id =
(SELECT movie_id
FROM movie_avg
ORDER BY avg_rating desc
LIMIT 1)
직관적으로 UNION은 잘 떠올렸고..
근데 이렇게 한 row씩 선택해서 UNION 시킬거면 정렬조건이 선행되었어야 함 > 정렬조건 먹이려면 테이블 간 JOIN이 필수적임
(SELECT name as results
FROM Users u
JOIN MovieRating mr ON u.user_id = mr.user_id
GROUP BY u.user_id
ORDER BY COUNT(distinct mr.movie_id) DESC, u.name ASC
LIMIT 1)
UNION ALL
(SELECT title as results
FROM Movies m
JOIN MovieRating mr on m.movie_id = mr.movie_id
WHERE mr.created_at BETWEEN '2020-02-01' AND '2020-02-29'
GROUP BY m.movie_id
ORDER BY AVG(mr.rating) DESC, m.title ASC
LIMIT 1)
3. TIL
- ORDER BY에서 집계함수 쓰고 LIMIT 걸어서 최대/최솟값 뽑기
1321. Restaurant Growth
1. 문제
Table: Customer
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| customer_id | int |
| name | varchar |
| visited_on | date |
| amount | int |
+---------------+---------+
In SQL,(customer_id, visited_on) is the primary key for this table.
This table contains data about customer transactions in a restaurant.
visited_on is the date on which the customer with ID (customer_id) has visited the restaurant.
amount is the total paid by a customer.
You are the restaurant owner and you want to analyze a possible expansion (there will be at least one customer every day).
Compute the moving average of how much the customer paid in a seven days window (i.e., current day + 6 days before). average_amount should be rounded to two decimal places.
Return the result table ordered by visited_on in ascending order.
The result format is in the following example.
Example 1:
Input:
Customer table:
+-------------+--------------+--------------+-------------+
| 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 |
+--------------+--------------+----------------+
2. 풀이
SELECT DISTINCT visited_on,
SUM(amount) OVER w as amount,
ROUND(SUM(amount) OVER w)/7, 2) as average_amount
FROM Customer
WINDOW w as (
ORDER BY visited_on
RANGE BETWEEN interval 6 day PRECEDING AND current row
)
LIMIT 6, 999