-
Notifications
You must be signed in to change notification settings - Fork 0
/
SQL Queries.sql
294 lines (271 loc) · 6.65 KB
/
SQL Queries.sql
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
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
use final_project;
##Trend Analysis
#Average Movie Runtime change over the years
SELECT
startYear,
AVG(CAST(runtimeMinutes AS DECIMAL(10, 3))) AS AvgRuntimeMinutes
FROM
Dim_Movies
WHERE
startYear IS NOT NULL
AND runtimeMinutes !="\\N"
AND startYear > 0
GROUP BY
startYear
ORDER BY
startYear;
#Average Movie Rating over the Years
SELECT
m.startYear,
AVG(CAST(r.Avg_Rating AS DECIMAL(6, 3))) AS AverageRating
FROM
Dim_Movies m
JOIN
Fct_Ratings r ON m.MovieSK = r.MovieSK
WHERE
m.startYear !="\\N"
AND r.Avg_Rating IS NOT NULL
GROUP BY
m.startYear
ORDER BY
m.startYear;
#Number of movies over the years
SELECT
m.startYear AS ReleaseYear,
COUNT(*) AS NumberOfMoviesReleased
FROM
Dim_Movies m
WHERE
m.startYear IS NOT NULL AND m.startYear != '\\N'
GROUP BY
m.startYear
ORDER BY
m.startYear;
##Genre Analysis
#Genre Popularity over past decade
SELECT
g.Genre,
AVG(CAST(r.Avg_Rating AS DECIMAL(6, 1))) AS AverageRating
FROM
Dim_Genre g
JOIN
Bridge_MovieGenre bg ON g.GenreSK = bg.GenreSK
JOIN
Dim_Movies m ON bg.MovieSK = m.MovieSK
JOIN
Fct_Ratings r ON m.MovieSK = r.MovieSK
WHERE
m.startYear BETWEEN YEAR(CURDATE()) - 10 AND YEAR(CURDATE())
GROUP BY
g.Genre
ORDER BY
AverageRating DESC;
#Top 5 genres by gross earnings
SELECT
g.Genre,
SUM(CAST(REPLACE(REPLACE(r.Gross, '$', ''), ',', '') AS DECIMAL(18,0))) AS TotalGross
FROM
Dim_Genre g
JOIN
Bridge_MovieGenre bg ON g.GenreSK = bg.GenreSK
JOIN
Dim_Movies m ON bg.MovieSK = m.MovieSK
JOIN
Fct_MovieRevenue r ON m.MovieSK = r.MovieSK
GROUP BY
g.Genre
ORDER BY
TotalGross DESC
LIMIT 5;
##Performance Metrics
#Runtime vs Rating
SELECT
m.runtimeMinutes,
AVG(r.Avg_Rating) AS AverageRating
FROM
Dim_Movies m
JOIN
Fct_Ratings r ON m.MovieSK = r.MovieSK
WHERE
m.runtimeMinutes != "\\N"
GROUP BY
m.runtimeMinutes
ORDER BY
m.runtimeMinutes;
#Movie Runtime vs Average Gross
SELECT
m.runtimeMinutes,
CEILING(AVG(NULLIF(CAST(REPLACE(REPLACE(r.Gross, '$', ''), ',', '') AS DECIMAL(18,2)), 0))) AS AverageGross
FROM
Dim_Movies m
JOIN
Fct_MovieRevenue r ON m.MovieSK = r.MovieSK
WHERE
r.Gross IS NOT NULL AND r.Gross != ''
GROUP BY
m.runtimeMinutes
HAVING
AverageGross IS NOT NULL
ORDER BY
m.runtimeMinutes;
#Number of Votes vs Average Rating
select Avg_Rating, sum(NumVotes) from fct_ratings
group by Avg_Rating
order by Avg_Rating;
##Director Success Metrics
#Directors with most films rated over 7
SELECT
p.PrimaryName AS DirectorName,
COUNT(DISTINCT m.MovieSK) AS HighRatedMoviesCount
FROM
Dim_Person p
JOIN
Bridge_PersonProfession bpp ON p.PersonSK = bpp.PersonSK
JOIN
Dim_Profession dp ON bpp.ProfessionSK = dp.ProfessionSK
JOIN
Bridge_TitlePrincipal btp ON p.PersonSK = btp.PersonSK
JOIN
Dim_Movies m ON btp.MovieSK = m.MovieSK
JOIN
Fct_Ratings r ON m.MovieSK = r.MovieSK
WHERE
dp.Profession = 'Director' AND
r.Avg_Rating > 7
GROUP BY
p.PrimaryName
ORDER BY
HighRatedMoviesCount DESC;
#Directors with consistent grossing movies
SELECT
p.PrimaryName AS DirectorName,
COUNT(DISTINCT m.MovieSK) AS TotalFilmsDirected,
SUM(CAST(REPLACE(REPLACE(mr.Gross, '$', ''), ',', '') AS DECIMAL(10))) AS TotalGrossEarnings
FROM
Dim_Person p
INNER JOIN
Bridge_PersonProfession bpp ON p.PersonSK = bpp.PersonSK
INNER JOIN
Dim_Profession dp ON bpp.ProfessionSK = dp.ProfessionSK
INNER JOIN
Bridge_TitlePrincipal btp ON p.PersonSK = btp.PersonSK
INNER JOIN
Dim_Movies m ON btp.MovieSK = m.MovieSK
INNER JOIN
Fct_MovieRevenue mr ON m.MovieSK = mr.MovieSK
WHERE
dp.Profession = 'Director' AND
m.startYear IS NOT NULL AND m.startYear != '\\N' AND
mr.Gross IS NOT NULL AND mr.Gross != '\\N'
GROUP BY
p.PrimaryName
ORDER BY
TotalFilmsDirected DESC, TotalGrossEarnings DESC;
##Actor and Actress Film Records
# Top 10 Actors and Actresses based on Movie ratings between 4 & 7
SELECT
p.PrimaryName AS ActorActressName,
COUNT(DISTINCT m.MovieSK) AS NumberOfFilms
FROM
Dim_Person p
JOIN
Bridge_PersonProfession bpp ON p.PersonSK = bpp.PersonSK
JOIN
Dim_Profession dp ON bpp.ProfessionSK = dp.ProfessionSK
JOIN
Bridge_TitlePrincipal btp ON p.PersonSK = btp.PersonSK
JOIN
Dim_Movies m ON btp.MovieSK = m.MovieSK
JOIN
Fct_Ratings r ON m.MovieSK = r.MovieSK
WHERE
r.Avg_Rating BETWEEN 4 AND 7
AND dp.Profession IN ('Actor', 'Actress')
GROUP BY
p.PrimaryName
ORDER BY
NumberOfFilms DESC
LIMIT 10;
# Top 5 Actors and Actresses on Ratings
SELECT
p.PrimaryName,
prof.Profession,
AVG(r.Avg_Rating) AS AvgRating,
COUNT(DISTINCT m.MovieSK) AS NumberOfMovies
FROM
Dim_Person p
JOIN
Bridge_PersonProfession bpp ON p.PersonSK = bpp.PersonSK
JOIN
Dim_Profession prof ON bpp.ProfessionSK = prof.ProfessionSK
JOIN
Bridge_TitlePrincipal btp ON p.PersonSK = btp.PersonSK
JOIN
Dim_Movies m ON btp.MovieSK = m.MovieSK
JOIN
Fct_Ratings r ON m.MovieSK = r.MovieSK
WHERE
prof.Profession IN ('Actor', 'Actress')
GROUP BY
p.PrimaryName, prof.Profession
ORDER BY
AVG(r.Avg_Rating) DESC
LIMIT 5;
##Seasonal Analysis
#Movie Performance with Seasons
SELECT
d.Season,
SUM(CAST(REPLACE(REPLACE(mr.Gross, '$', ''), ',', '') AS DECIMAL(10))) AS TotalGrossEarnings
FROM
Fct_MovieRevenue mr
JOIN
Dim_Date d ON mr.DateSK = d.DateSK
GROUP BY
d.Season
ORDER BY
TotalGrossEarnings DESC;
# Top 3 Movies based on Seasons
SELECT
Season,
MovieTitle,
TotalGross
FROM (
SELECT
dd.Season AS Season,
dm.PrimaryTitle AS MovieTitle,
SUM(CAST(REPLACE(REPLACE(fmr.Gross, '$', ''), ',', '') AS DECIMAL(18, 2))) AS TotalGross,
ROW_NUMBER() OVER(PARTITION BY dd.Season ORDER BY SUM(CAST(REPLACE(REPLACE(fmr.Gross, '$', ''), ',', '') AS DECIMAL(18, 2))) DESC) AS RowNum
FROM
Dim_Date dd
JOIN
Fct_MovieRevenue fmr ON dd.DateSK = fmr.DateSK
JOIN
Dim_Movies dm ON fmr.MovieSK = dm.MovieSK
WHERE
dd.Season IN ('Spring', 'Summer', 'Fall')
AND dm.PrimaryTitle IS NOT NULL
AND fmr.Gross IS NOT NULL
GROUP BY
dd.Season,
dm.PrimaryTitle
) AS RankedMovies
WHERE
RowNum <= 3
ORDER BY
Season,
TotalGross DESC;
##Release Regions
#Movies with widest release across regions
SELECT
dm.primaryTitle,
COUNT(DISTINCT dr.Region) AS NumRegions
FROM
dim_movies dm join bridge_movieregion mr
on dm.MovieSK = mr.MovieSK
join
dim_region dr on mr.RegionSK = dr.RegionSK
GROUP BY
primaryTitle
ORDER BY
NumRegions DESC
LIMIT 5;