-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathnotes - SqlQueries - checks
196 lines (183 loc) · 5.71 KB
/
notes - SqlQueries - checks
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
-- 0
SELECT COUNT(*) FROM track_servie.catalog;
-- 0
SELECT COUNT(*) FROM track_servie.catalog_servies;
-- 99
SELECT COUNT(*) FROM track_servie.created_by;
-- 12864
SELECT COUNT(*) FROM track_servie.ecast;
-- 17779
SELECT COUNT(*) FROM track_servie.ecrew;
-- 7839
SELECT COUNT(*) FROM track_servie.episode;
-- 31006
SELECT COUNT(*) FROM track_servie.episode_cast;
-- 63004
SELECT COUNT(*) FROM track_servie.episode_crew;
-- 25
SELECT COUNT(*) FROM track_servie.genre;
-- 66199
SELECT COUNT(*) FROM track_servie.mcast;
-- 178237
SELECT COUNT(*) FROM track_servie.mcrew;
-- 1815
SELECT COUNT(*) FROM track_servie.movie;
-- 66199
SELECT COUNT(*) FROM track_servie.movie_cast;
-- 283
SELECT COUNT(*) FROM track_servie.movie_collection;
-- 178237
SELECT COUNT(*) FROM track_servie.movie_crew;
-- 109
SELECT COUNT(*) FROM track_servie.movies_bkp;
-- 102
SELECT COUNT(*) FROM track_servie.network;
-- 109
SELECT COUNT(*) FROM track_servie.origin_country;
-- 4
SELECT COUNT(*) FROM track_servie.permission;
-- 0
SELECT COUNT(*) FROM track_servie.person;
-- 1969
SELECT COUNT(*) FROM track_servie.production_company;
-- 48
SELECT COUNT(*) FROM track_servie.production_country;
-- 3
SELECT COUNT(*) FROM track_servie.role;
-- 7
SELECT COUNT(*) FROM track_servie.role_permissions;
-- 1386
SELECT COUNT(*) FROM track_servie.scast;
-- 424
SELECT COUNT(*) FROM track_servie.season;
-- 4712
SELECT COUNT(*) FROM track_servie.season_cast;
-- 109
SELECT COUNT(*) FROM track_servie.series;
-- 99
SELECT COUNT(*) FROM track_servie.series_createdby;
-- 221
SELECT COUNT(*) FROM track_servie.series_networks;
-- 1929
SELECT COUNT(*) FROM track_servie.servie;
-- 5381
SELECT COUNT(*) FROM track_servie.servie_genres;
-- 5877
SELECT COUNT(*) FROM track_servie.servie_production_companies;
-- 2463
SELECT COUNT(*) FROM track_servie.servie_production_countries;
-- 2849
SELECT COUNT(*) FROM track_servie.servie_spoken_languages;
-- 64
SELECT COUNT(*) FROM track_servie.spoken_language;
-- 0
SELECT COUNT(*) FROM track_servie.tmdb_movie_exports;
-- 2
SELECT COUNT(*) FROM track_servie.user;
-- 3536
SELECT COUNT(*) FROM track_servie.user_episode_data;
-- 2
SELECT COUNT(*) FROM track_servie.user_roles;
-- 214
SELECT COUNT(*) FROM track_servie.user_season_data;
-- 1561
SELECT COUNT(*) FROM track_servie.user_servie_data;
-- Need a better check for Series & CreatedBy
-- ProductionCompanies
SELECT SUM(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;
-- MUST BE EQUAL TO
SELECT COUNT(*) FROM track_servie.servie_production_companies;
-- ProductionCountries
SELECT SUM(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;
-- MUST BE EQUAL TO
SELECT COUNT(*) FROM track_servie.servie_production_countries;
-- SpokenLanguages
SELECT SUM(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;
-- MUST BE EQUAL TO
SELECT COUNT(*) FROM track_servie.servie_spoken_languages;
-- EpisodesCrew
SELECT SUM(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;
-- MUST BE EQUAL TO
SELECT COUNT(*) FROM track_servie.episode_crew;
-- EpisodesGuestStars
SELECT SUM(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;
-- MUST BE EQUAL TO
SELECT COUNT(*) FROM track_servie.episode_cast;
-- SeasonCasts
SELECT SUM(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;
-- MUST BE EQUAL TO
SELECT COUNT(*) FROM track_servie.season_cast;
-- MovieCasts
SELECT SUM(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;
-- MUST BE EQUAL TO
SELECT COUNT(*) FROM track_servie.movie_cast;
-- MovieCrew
SELECT SUM(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;
-- MUST BE EQUAL TO
SELECT COUNT(*) FROM track_servie.movie_crew;