-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathnotes - SqlQueries - status
170 lines (164 loc) · 6.86 KB
/
notes - SqlQueries - status
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
-- Series - CreatedBy Frequency
SELECT s.tmdb_id, s.title, cb.CountOfCreatedBy
FROM track_servie.servie s
JOIN track_servie.series tv
ON tv.childtype = s.childtype AND tv.tmdb_id = s.tmdb_id
LEFT JOIN (SELECT scb.tmdb_id, scb.childtype, COUNT(*) AS CountOfCreatedBy
FROM track_servie.series_createdby scb
JOIN track_servie.created_by cb
ON scb.credit_id = cb.credit_id
GROUP BY scb.tmdb_id) AS cb
ON tv.tmdb_id = cb.tmdb_id AND tv.childtype = cb.childtype
GROUP BY s.tmdb_id
ORDER BY s.title;
--------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------
-- Servie - ProductionCompanies Freq
SELECT s.tmdb_id, s.title, pc.CountOfProdCompanies
FROM track_servie.servie s
LEFT JOIN (SELECT spc.tmdb_id, spc.childtype, COUNT(*) AS CountOfProdCompanies
FROM track_servie.servie_production_companies spc
JOIN track_servie.production_company pc
ON spc.production_company_id = pc.id
GROUP BY spc.tmdb_id) AS pc
ON s.tmdb_id = pc.tmdb_id AND s.childtype = pc.childtype
GROUP BY s.tmdb_id
ORDER BY s.title;
-- Servie - ProductionCountries Freq
SELECT s.tmdb_id, s.title, pc.CountOfProdCountries
FROM track_servie.servie s
LEFT JOIN (SELECT spc.tmdb_id, spc.childtype, COUNT(*) AS CountOfProdCountries
FROM track_servie.servie_production_countries spc
JOIN track_servie.production_country pc
ON spc.iso = pc.iso_3166_1
GROUP BY spc.tmdb_id) AS pc
ON s.tmdb_id = pc.tmdb_id AND s.childtype = pc.childtype
GROUP BY s.tmdb_id
ORDER BY s.title;
-- Servie - SpokenLanguages Frequency
SELECT s.tmdb_id, s.title, sl.CountOfSpokenLanguages
FROM track_servie.servie s
LEFT JOIN (SELECT spl.tmdb_id, spl.childtype, COUNT(*) AS CountOfSpokenLanguages
FROM track_servie.servie_spoken_languages spl
JOIN track_servie.spoken_language sl
ON spl.iso = sl.iso_639_1
GROUP BY spl.tmdb_id) AS sl
ON s.tmdb_id = sl.tmdb_id AND s.childtype = sl.childtype
GROUP BY s.tmdb_id
ORDER BY s.title;
-- Servie - ProductionCompanies Freq
-- Servie - SpokenLanguages Frequency
-- Servie - ProductionCountries Freq
SELECT s.tmdb_id, s.title, company.CountOfProdCompanies, country.CountOfProdCountries, lang.CountOfSpokenLanguages
FROM track_servie.servie s
LEFT JOIN (SELECT spc.tmdb_id, spc.childtype, COUNT(*) AS CountOfProdCompanies
FROM track_servie.servie_production_companies spc
JOIN track_servie.production_company pc
ON spc.production_company_id = pc.id
GROUP BY spc.tmdb_id) AS company
ON s.tmdb_id = company.tmdb_id AND s.childtype = company.childtype
LEFT JOIN (SELECT spc.tmdb_id, spc.childtype, COUNT(*) AS CountOfProdCountries
FROM track_servie.servie_production_countries spc
JOIN track_servie.production_country pc
ON spc.iso = pc.iso_3166_1
GROUP BY spc.tmdb_id) AS country
ON s.tmdb_id = country.tmdb_id AND s.childtype = country.childtype
LEFT JOIN (SELECT spl.tmdb_id, spl.childtype, COUNT(*) AS CountOfSpokenLanguages
FROM track_servie.servie_spoken_languages spl
JOIN track_servie.spoken_language sl
ON spl.iso = sl.iso_639_1
GROUP BY spl.tmdb_id) AS lang
ON s.tmdb_id = lang.tmdb_id AND s.childtype = lang.childtype
GROUP BY s.tmdb_id
ORDER BY s.title;
--------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------
-- Episodes - Crew Frequency
SELECT e.tmdb_id, s.title, e.season_no, e.episode_no, crew.CountOfCrew
FROM track_servie.episode e
JOIN track_servie.servie s
ON s.tmdb_id = e.tmdb_id
LEFT JOIN (SELECT ec.episode_id, COUNT(*) AS CountOfCrew
FROM track_servie.episode_crew ec
JOIN track_servie.ecrew ecw
ON ecw.credit_id = ec.credit_id
GROUP BY ec.episode_id) AS crew
ON e.id = crew.episode_id
GROUP BY e.tmdb_id, e.season_no, e.episode_no
ORDER BY s.title, e.season_no, e.episode_no;
-- Episodes - GuestStars Frequency
SELECT e.tmdb_id, s.title, e.season_no, e.episode_no, cast.CountOfGuestStars
FROM track_servie.episode e
JOIN track_servie.servie s
ON s.tmdb_id = e.tmdb_id
LEFT JOIN (SELECT ec.episode_id, COUNT(*) AS CountOfGuestStars
FROM track_servie.episode_cast ec
JOIN track_servie.ecast ecw
ON ecw.credit_id = ec.credit_id
GROUP BY ec.episode_id) AS cast
ON e.id = cast.episode_id
GROUP BY e.tmdb_id, e.season_no, e.episode_no
ORDER BY s.title, e.season_no, e.episode_no;
-- Episodes - GuestStars Frequency
-- Episodes - Crew Frequency
SELECT e.tmdb_id, s.title, e.season_no, e.episode_no, cast.CountOfGuestStars, crew.CountOfCrew
FROM track_servie.episode e
JOIN track_servie.servie s
ON s.tmdb_id = e.tmdb_id
LEFT JOIN (SELECT ec.episode_id, COUNT(*) AS CountOfGuestStars
FROM track_servie.episode_cast ec
JOIN track_servie.ecast ecw
ON ecw.credit_id = ec.credit_id
GROUP BY ec.episode_id) AS cast
ON e.id = cast.episode_id
LEFT JOIN (SELECT ec.episode_id, COUNT(*) AS CountOfCrew
FROM track_servie.episode_crew ec
JOIN track_servie.ecrew ecw
ON ecw.credit_id = ec.credit_id
GROUP BY ec.episode_id) AS crew
ON e.id = crew.episode_id
GROUP BY e.tmdb_id, e.season_no, e.episode_no
ORDER BY s.title, e.season_no, e.episode_no;
--------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------
-- Season - Cast Frequency
SELECT ser.tmdb_id, ser.title, s.season_no, cast.CountOfCast
FROM track_servie.season s
JOIN track_servie.servie ser
ON s.tmdb_id = ser.tmdb_id
LEFT JOIN (SELECT sc.season_id, COUNT(*) AS CountOfCast
FROM track_servie.season_cast sc
JOIN track_servie.scast sct
ON sct.credit_id = sc.credit_id
GROUP BY sc.season_id) AS cast
ON s.id = cast.season_id
GROUP BY ser.tmdb_id, s.season_no
ORDER BY ser.title, s.season_no;
--------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------
-- Movie - Cast Frequency
SELECT s.tmdb_id, s.title, cast.CountOfCasts
FROM track_servie.movie m
JOIN track_servie.servie s
ON m.tmdb_id = s.tmdb_id
LEFT JOIN (SELECT mc.tmdb_id, COUNT(*) AS CountOfCasts
FROM track_servie.movie_cast mc
JOIN track_servie.mcast mct
ON mct.credit_id = mc.credit_id
GROUP BY mc.tmdb_id) AS cast
ON m.tmdb_id = cast.tmdb_id
GROUP BY s.tmdb_id
ORDER BY s.title;
-- Movie - Crew Frequency -> LITTLE PROBLEM OF DISTINCT
SELECT s.tmdb_id, s.title, crew.CountOfCrew
FROM track_servie.movie m
JOIN track_servie.servie s
ON m.tmdb_id = s.tmdb_id
LEFT JOIN (SELECT mc.tmdb_id, COUNT(*) AS CountOfCrew
FROM track_servie.movie_crew mc
JOIN track_servie.mcrew mct
ON mct.credit_id = mc.credit_id
GROUP BY mc.tmdb_id) AS crew
ON m.tmdb_id = crew.tmdb_id
GROUP BY s.tmdb_id
ORDER BY s.title;