This repository was archived by the owner on Apr 6, 2025. It is now read-only.
-
Notifications
You must be signed in to change notification settings - Fork 1
Expand file tree
/
Copy path1341.sql
More file actions
95 lines (72 loc) · 2.16 KB
/
Copy path1341.sql
File metadata and controls
95 lines (72 loc) · 2.16 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
USE leetcode;
# Tables: Movies, Users, MovieRating
CREATE TABLE IF NOT EXISTS Movies
(
movie_id INT,
title VARCHAR(30)
);
CREATE TABLE IF NOT EXISTS Users
(
user_id INT,
name VARCHAR(30)
);
CREATE TABLE IF NOT EXISTS MovieRating
(
movie_id INT,
user_id INT,
rating INT,
created_at DATE
);
TRUNCATE TABLE Movies;
INSERT INTO Movies (movie_id, title)
VALUES ('1', 'Avengers');
INSERT INTO Movies (movie_id, title)
VALUES ('2', 'Frozen 2');
INSERT INTO Movies (movie_id, title)
VALUES ('3', 'Joker');
TRUNCATE TABLE Users;
INSERT INTO Users (user_id, name)
VALUES ('1', 'Daniel');
INSERT INTO Users (user_id, name)
VALUES ('2', 'Monica');
INSERT INTO Users (user_id, name)
VALUES ('3', 'Maria');
INSERT INTO Users (user_id, name)
VALUES ('4', 'James');
TRUNCATE TABLE MovieRating;
INSERT INTO MovieRating (movie_id, user_id, rating, created_at)
VALUES ('1', '1', '3', '2020-01-12');
INSERT INTO MovieRating (movie_id, user_id, rating, created_at)
VALUES ('1', '2', '4', '2020-02-11');
INSERT INTO MovieRating (movie_id, user_id, rating, created_at)
VALUES ('1', '3', '2', '2020-02-12');
INSERT INTO MovieRating (movie_id, user_id, rating, created_at)
VALUES ('1', '4', '1', '2020-01-01');
INSERT INTO MovieRating (movie_id, user_id, rating, created_at)
VALUES ('2', '1', '5', '2020-02-17');
INSERT INTO MovieRating (movie_id, user_id, rating, created_at)
VALUES ('2', '2', '2', '2020-02-01');
INSERT INTO MovieRating (movie_id, user_id, rating, created_at)
VALUES ('2', '3', '2', '2020-03-01');
INSERT INTO MovieRating (movie_id, user_id, rating, created_at)
VALUES ('3', '1', '3', '2020-02-22');
INSERT INTO MovieRating (movie_id, user_id, rating, created_at)
VALUES ('3', '2', '4', '2020-02-25');
# Solution
(SELECT name AS results
FROM Users u
INNER JOIN MovieRating mr
USING (user_id)
GROUP BY user_id, name
ORDER BY COUNT(movie_id) DESC, name
LIMIT 1)
UNION ALL
(SELECT title AS results
FROM MovieRating mr
INNER JOIN Movies
USING (movie_id)
WHERE MONTH(created_at) = 2
AND YEAR(created_at) = 2020
GROUP BY movie_id, title
ORDER BY AVG(rating) DESC, title
LIMIT 1);