import sqlalchemy
sqlalchemy.create_engine('sqlite:///chinook.db')
Engine(sqlite:///chinook.db)
%load_ext sql
%sql sqlite:///chinook.db
'Connected: @chinook.db'
%%sql
SELECT *
FROM sqlite_master
WHERE type IN ('table','view') ;
* sqlite:///chinook.db Done.
type | name | tbl_name | rootpage | sql |
---|---|---|---|---|
table | album | album | 2 | CREATE TABLE [album] ( [album_id] INTEGER PRIMARY KEY NOT NULL, [title] NVARCHAR(160) NOT NULL, [artist_id] INTEGER NOT NULL, FOREIGN KEY ([artist_id]) REFERENCES [artist] ([artist_id]) ON DELETE NO ACTION ON UPDATE NO ACTION ) |
table | artist | artist | 3 | CREATE TABLE [artist] ( [artist_id] INTEGER PRIMARY KEY NOT NULL, [name] NVARCHAR(120) ) |
table | customer | customer | 4 | CREATE TABLE [customer] ( [customer_id] INTEGER PRIMARY KEY NOT NULL, [first_name] NVARCHAR(40) NOT NULL, [last_name] NVARCHAR(20) NOT NULL, [company] NVARCHAR(80), [address] NVARCHAR(70), [city] NVARCHAR(40), [state] NVARCHAR(40), [country] NVARCHAR(40), [postal_code] NVARCHAR(10), [phone] NVARCHAR(24), [fax] NVARCHAR(24), [email] NVARCHAR(60) NOT NULL, [support_rep_id] INTEGER, FOREIGN KEY ([support_rep_id]) REFERENCES [employee] ([employee_id]) ON DELETE NO ACTION ON UPDATE NO ACTION ) |
table | employee | employee | 5 | CREATE TABLE [employee] ( [employee_id] INTEGER PRIMARY KEY NOT NULL, [last_name] NVARCHAR(20) NOT NULL, [first_name] NVARCHAR(20) NOT NULL, [title] NVARCHAR(30), [reports_to] INTEGER, [birthdate] DATETIME, [hire_date] DATETIME, [address] NVARCHAR(70), [city] NVARCHAR(40), [state] NVARCHAR(40), [country] NVARCHAR(40), [postal_code] NVARCHAR(10), [phone] NVARCHAR(24), [fax] NVARCHAR(24), [email] NVARCHAR(60), FOREIGN KEY ([reports_to]) REFERENCES [employee] ([employee_id]) ON DELETE NO ACTION ON UPDATE NO ACTION ) |
table | genre | genre | 6 | CREATE TABLE [genre] ( [genre_id] INTEGER PRIMARY KEY NOT NULL, [name] NVARCHAR(120) ) |
table | invoice | invoice | 7 | CREATE TABLE [invoice] ( [invoice_id] INTEGER PRIMARY KEY NOT NULL, [customer_id] INTEGER NOT NULL, [invoice_date] DATETIME NOT NULL, [billing_address] NVARCHAR(70), [billing_city] NVARCHAR(40), [billing_state] NVARCHAR(40), [billing_country] NVARCHAR(40), [billing_postal_code] NVARCHAR(10), [total] NUMERIC(10,2) NOT NULL, FOREIGN KEY ([customer_id]) REFERENCES [customer] ([customer_id]) ON DELETE NO ACTION ON UPDATE NO ACTION ) |
table | invoice_line | invoice_line | 8 | CREATE TABLE [invoice_line] ( [invoice_line_id] INTEGER PRIMARY KEY NOT NULL, [invoice_id] INTEGER NOT NULL, [track_id] INTEGER NOT NULL, [unit_price] NUMERIC(10,2) NOT NULL, [quantity] INTEGER NOT NULL, FOREIGN KEY ([invoice_id]) REFERENCES [invoice] ([invoice_id]) ON DELETE NO ACTION ON UPDATE NO ACTION, FOREIGN KEY ([track_id]) REFERENCES [track] ([track_id]) ON DELETE NO ACTION ON UPDATE NO ACTION ) |
table | media_type | media_type | 9 | CREATE TABLE [media_type] ( [media_type_id] INTEGER PRIMARY KEY NOT NULL, [name] NVARCHAR(120) ) |
table | playlist | playlist | 10 | CREATE TABLE [playlist] ( [playlist_id] INTEGER PRIMARY KEY NOT NULL, [name] NVARCHAR(120) ) |
table | playlist_track | playlist_track | 11 | CREATE TABLE [playlist_track] ( [playlist_id] INTEGER NOT NULL, [track_id] INTEGER NOT NULL, CONSTRAINT [pk_playlist_track] PRIMARY KEY ([playlist_id], [track_id]), FOREIGN KEY ([playlist_id]) REFERENCES [playlist] ([playlist_id]) ON DELETE NO ACTION ON UPDATE NO ACTION, FOREIGN KEY ([track_id]) REFERENCES [track] ([track_id]) ON DELETE NO ACTION ON UPDATE NO ACTION ) |
table | track | track | 13 | CREATE TABLE [track] ( [track_id] INTEGER PRIMARY KEY NOT NULL, [name] NVARCHAR(200) NOT NULL, [album_id] INTEGER, [media_type_id] INTEGER NOT NULL, [genre_id] INTEGER, [composer] NVARCHAR(220), [milliseconds] INTEGER NOT NULL, [bytes] INTEGER, [unit_price] NUMERIC(10,2) NOT NULL, FOREIGN KEY ([album_id]) REFERENCES [album] ([album_id]) ON DELETE NO ACTION ON UPDATE NO ACTION, FOREIGN KEY ([genre_id]) REFERENCES [genre] ([genre_id]) ON DELETE NO ACTION ON UPDATE NO ACTION, FOREIGN KEY ([media_type_id]) REFERENCES [media_type] ([media_type_id]) ON DELETE NO ACTION ON UPDATE NO ACTION ) |
view | ssa | ssa | 0 | CREATE VIEW ssa AS SELECT e.employee_id e_id, e.first_name || ' ' || e.last_name name, e.title, e.country e_country, c.country c_country, e.hire_date, i.total FROM employee e LEFT JOIN customer c ON c.support_rep_id=e.employee_id LEFT JOIN invoice i ON i.customer_id=c.customer_id WHERE title='Sales Support Agent' |
So if you’ re focusing on the method of the solution I first recommend removing their “outer branch” because it’ s only function is to make the query results look better and will make more intuitive sense at the end.
%%sql
WITH invoice_first_track AS
(
SELECT
invoice_id,
MIN(track_id) first_track_id
FROM invoice_line il
GROUP BY invoice_id
)
SELECT
ifs.*,
CASE
WHEN
(
SELECT t.track_id FROM track t
WHERE t.album_id = (
SELECT t2.album_id FROM track t2
WHERE t2.track_id = ifs.first_track_id
)
EXCEPT
SELECT il2.track_id FROM invoice_line il2
WHERE il2.invoice_id = ifs.invoice_id
) IS NULL
AND
(
SELECT il2.track_id FROM invoice_line il2
WHERE il2.invoice_id = ifs.invoice_id
EXCEPT
SELECT t.track_id FROM track t
WHERE t.album_id = (
SELECT t2.album_id FROM track t2
WHERE t2.track_id = ifs.first_track_id
)
) IS NULL
THEN "yes"
ELSE "no"
END AS "album_purchase"
FROM invoice_first_track ifs
* sqlite:///chinook.db Done.
invoice_id | first_track_id | album_purchase |
---|---|---|
1 | 1158 | yes |
2 | 201 | no |
3 | 2516 | no |
4 | 748 | no |
5 | 1986 | yes |
6 | 30 | no |
7 | 42 | no |
8 | 81 | no |
9 | 196 | no |
10 | 2663 | no |
11 | 610 | no |
12 | 92 | no |
13 | 2553 | no |
14 | 541 | no |
15 | 807 | no |
16 | 16 | no |
17 | 55 | no |
18 | 1027 | no |
19 | 105 | no |
20 | 60 | no |
21 | 13 | no |
22 | 383 | no |
23 | 1 | yes |
24 | 1146 | yes |
25 | 32 | no |
26 | 85 | no |
27 | 1280 | no |
28 | 36 | no |
29 | 479 | no |
30 | 33 | no |
31 | 2731 | yes |
32 | 38 | yes |
33 | 2542 | no |
34 | 610 | no |
35 | 6 | no |
36 | 153 | no |
37 | 698 | no |
38 | 154 | no |
39 | 184 | no |
40 | 108 | no |
41 | 153 | no |
42 | 13 | no |
43 | 918 | no |
44 | 3052 | yes |
45 | 102 | no |
46 | 2639 | yes |
47 | 8 | no |
48 | 58 | no |
49 | 146 | no |
50 | 1511 | no |
51 | 34 | no |
52 | 2664 | yes |
53 | 207 | no |
54 | 1773 | yes |
55 | 155 | no |
56 | 2254 | yes |
57 | 2948 | no |
58 | 1029 | no |
59 | 198 | no |
60 | 46 | no |
61 | 2609 | yes |
62 | 97 | no |
63 | 6 | no |
64 | 38 | yes |
65 | 539 | no |
66 | 442 | no |
67 | 9 | no |
68 | 61 | no |
69 | 85 | yes |
70 | 2307 | no |
71 | 2254 | yes |
72 | 30 | no |
73 | 2391 | yes |
74 | 1743 | no |
75 | 22 | no |
76 | 1020 | yes |
77 | 15 | no |
78 | 33 | no |
79 | 1902 | yes |
80 | 3400 | no |
81 | 545 | no |
82 | 54 | no |
83 | 1163 | no |
84 | 3455 | yes |
85 | 1298 | no |
86 | 2291 | no |
87 | 1129 | no |
88 | 1995 | no |
89 | 1784 | no |
90 | 2554 | yes |
91 | 94 | no |
92 | 1414 | yes |
93 | 20 | no |
94 | 50 | no |
95 | 27 | no |
96 | 453 | no |
97 | 16 | no |
98 | 419 | no |
99 | 2591 | yes |
100 | 996 | no |
101 | 83 | no |
102 | 33 | no |
103 | 1029 | no |
104 | 1508 | no |
105 | 751 | no |
106 | 2 | no |
107 | 1 | no |
108 | 58 | no |
109 | 340 | no |
110 | 81 | no |
111 | 48 | no |
112 | 1479 | yes |
113 | 134 | no |
114 | 1479 | yes |
115 | 84 | no |
116 | 21 | no |
117 | 4 | no |
118 | 3111 | no |
119 | 1556 | no |
120 | 17 | no |
121 | 32 | no |
122 | 28 | no |
123 | 2003 | yes |
124 | 470 | no |
125 | 45 | no |
126 | 42 | no |
127 | 2 | no |
128 | 836 | no |
129 | 12 | no |
130 | 1573 | no |
131 | 920 | no |
132 | 2234 | no |
133 | 37 | no |
134 | 204 | no |
135 | 1221 | no |
136 | 1157 | no |
137 | 43 | no |
138 | 2271 | yes |
139 | 781 | no |
140 | 468 | no |
141 | 2621 | yes |
142 | 23 | yes |
143 | 479 | no |
144 | 158 | no |
145 | 1422 | no |
146 | 1466 | yes |
147 | 56 | no |
148 | 24 | no |
149 | 28 | no |
150 | 1021 | no |
151 | 605 | no |
152 | 1030 | no |
153 | 2406 | yes |
154 | 2649 | no |
155 | 94 | no |
156 | 1882 | yes |
157 | 3336 | yes |
158 | 1050 | no |
159 | 153 | no |
160 | 36 | no |
161 | 55 | no |
162 | 899 | no |
163 | 768 | no |
164 | 913 | no |
165 | 59 | no |
166 | 56 | no |
167 | 484 | no |
168 | 58 | no |
169 | 18 | no |
170 | 157 | no |
171 | 1466 | yes |
172 | 1185 | no |
173 | 2285 | yes |
174 | 422 | no |
175 | 1755 | yes |
176 | 1114 | no |
177 | 1039 | no |
178 | 1102 | yes |
179 | 39 | no |
180 | 1150 | no |
181 | 1470 | no |
182 | 86 | no |
183 | 1033 | yes |
184 | 3409 | yes |
185 | 991 | no |
186 | 826 | no |
187 | 1037 | no |
188 | 1455 | yes |
189 | 2191 | no |
190 | 833 | no |
191 | 24 | no |
192 | 130 | no |
193 | 2548 | no |
194 | 1476 | no |
195 | 51 | no |
196 | 1325 | yes |
197 | 592 | no |
198 | 1121 | no |
199 | 2008 | no |
200 | 15 | yes |
201 | 3288 | yes |
202 | 1747 | no |
203 | 6 | no |
204 | 14 | no |
205 | 1829 | yes |
206 | 224 | no |
207 | 470 | no |
208 | 151 | no |
209 | 1495 | no |
210 | 18 | no |
211 | 39 | no |
212 | 1121 | yes |
213 | 2271 | yes |
214 | 80 | no |
215 | 1019 | no |
216 | 461 | no |
217 | 2554 | no |
218 | 894 | no |
219 | 897 | no |
220 | 1745 | no |
221 | 19 | no |
222 | 1029 | no |
223 | 2621 | yes |
224 | 20 | no |
225 | 32 | no |
226 | 1132 | no |
227 | 468 | no |
228 | 531 | no |
229 | 58 | no |
230 | 15 | yes |
231 | 154 | no |
232 | 6 | no |
233 | 9 | no |
234 | 2562 | no |
235 | 1129 | no |
236 | 164 | no |
237 | 2375 | yes |
238 | 2271 | no |
239 | 2254 | no |
240 | 1774 | no |
241 | 2289 | no |
242 | 61 | no |
243 | 54 | no |
244 | 1026 | no |
245 | 1015 | no |
246 | 55 | no |
247 | 2639 | yes |
248 | 152 | no |
249 | 1470 | no |
250 | 435 | no |
251 | 30 | no |
252 | 1 | no |
253 | 1121 | yes |
254 | 183 | no |
255 | 529 | no |
256 | 1491 | no |
257 | 2285 | yes |
258 | 1990 | no |
259 | 29 | no |
260 | 77 | no |
261 | 58 | no |
262 | 77 | yes |
263 | 62 | no |
264 | 1187 | no |
265 | 109 | no |
266 | 1170 | no |
267 | 1997 | no |
268 | 13 | no |
269 | 3144 | no |
270 | 23 | no |
271 | 2271 | yes |
272 | 61 | no |
273 | 1479 | yes |
274 | 402 | no |
275 | 268 | no |
276 | 3300 | yes |
277 | 128 | no |
278 | 1468 | no |
279 | 1184 | no |
280 | 3081 | yes |
281 | 1902 | yes |
282 | 891 | no |
283 | 95 | no |
284 | 194 | yes |
285 | 134 | no |
286 | 1748 | no |
287 | 2004 | no |
288 | 2 | no |
289 | 16 | no |
290 | 438 | no |
291 | 165 | no |
292 | 162 | no |
293 | 802 | no |
294 | 18 | no |
295 | 1491 | no |
296 | 1466 | yes |
297 | 2207 | no |
298 | 34 | no |
299 | 1042 | no |
300 | 51 | yes |
301 | 5 | no |
302 | 77 | no |
303 | 43 | no |
304 | 11 | no |
305 | 2178 | yes |
306 | 49 | no |
307 | 38 | no |
308 | 2964 | yes |
309 | 1094 | no |
310 | 29 | no |
311 | 32 | no |
312 | 58 | no |
313 | 1454 | no |
314 | 31 | no |
315 | 1445 | no |
316 | 1050 | no |
317 | 3288 | yes |
318 | 26 | no |
319 | 62 | no |
320 | 3319 | yes |
321 | 2607 | no |
322 | 13 | no |
323 | 1125 | no |
324 | 31 | no |
325 | 1943 | no |
326 | 135 | no |
327 | 6 | no |
328 | 481 | no |
329 | 3299 | no |
330 | 1133 | yes |
331 | 36 | no |
332 | 476 | no |
333 | 1006 | no |
334 | 1942 | yes |
335 | 2391 | yes |
336 | 2358 | no |
337 | 804 | no |
338 | 1523 | no |
339 | 696 | no |
340 | 1051 | no |
341 | 1520 | yes |
342 | 1679 | no |
343 | 161 | no |
344 | 1479 | yes |
345 | 35 | no |
346 | 151 | no |
347 | 16 | no |
348 | 9 | no |
349 | 1712 | no |
350 | 1017 | no |
351 | 26 | no |
352 | 2433 | no |
353 | 162 | no |
354 | 1387 | no |
355 | 13 | no |
356 | 51 | no |
357 | 469 | no |
358 | 93 | no |
359 | 35 | no |
360 | 2367 | no |
361 | 1991 | no |
362 | 695 | yes |
363 | 433 | no |
364 | 10 | no |
365 | 157 | no |
366 | 195 | no |
367 | 154 | no |
368 | 952 | no |
369 | 514 | no |
370 | 922 | no |
371 | 2410 | no |
372 | 529 | yes |
373 | 139 | no |
374 | 469 | no |
375 | 1268 | yes |
376 | 615 | no |
377 | 49 | no |
378 | 1056 | no |
379 | 2542 | yes |
380 | 475 | no |
381 | 1133 | yes |
382 | 43 | no |
383 | 486 | no |
384 | 152 | no |
385 | 1 | no |
386 | 45 | no |
387 | 337 | no |
388 | 999 | yes |
389 | 3064 | yes |
390 | 1988 | no |
391 | 80 | no |
392 | 1853 | yes |
393 | 80 | no |
394 | 15 | no |
395 | 757 | no |
396 | 822 | no |
397 | 781 | no |
398 | 2506 | yes |
399 | 1146 | yes |
400 | 40 | no |
401 | 13 | no |
402 | 1127 | no |
403 | 52 | no |
404 | 1174 | yes |
405 | 542 | no |
406 | 1460 | no |
407 | 2204 | no |
408 | 1043 | no |
409 | 1021 | no |
410 | 131 | no |
411 | 6 | no |
412 | 261 | no |
413 | 2690 | yes |
414 | 1 | yes |
415 | 29 | no |
416 | 1048 | no |
417 | 4 | no |
418 | 2755 | no |
419 | 33 | no |
420 | 999 | yes |
421 | 1 | no |
422 | 82 | no |
423 | 7 | no |
424 | 1495 | no |
425 | 1483 | no |
426 | 43 | no |
427 | 23 | yes |
428 | 1013 | no |
429 | 828 | no |
430 | 1788 | no |
431 | 160 | no |
432 | 94 | no |
433 | 27 | no |
434 | 27 | no |
435 | 426 | no |
436 | 77 | no |
437 | 301 | no |
438 | 151 | no |
439 | 423 | no |
440 | 88 | no |
441 | 1079 | no |
442 | 572 | no |
443 | 13 | no |
444 | 2229 | yes |
445 | 411 | no |
446 | 1 | no |
447 | 2285 | yes |
448 | 186 | no |
449 | 1479 | yes |
450 | 424 | no |
451 | 3044 | no |
452 | 572 | no |
453 | 9 | no |
454 | 1986 | no |
455 | 836 | no |
456 | 6 | no |
457 | 1562 | yes |
458 | 3473 | no |
459 | 226 | no |
460 | 51 | no |
461 | 160 | no |
462 | 6 | no |
463 | 38 | no |
464 | 38 | no |
465 | 149 | yes |
466 | 73 | no |
467 | 154 | no |
468 | 2149 | yes |
469 | 2232 | no |
470 | 94 | no |
471 | 2368 | no |
472 | 697 | no |
473 | 2690 | no |
474 | 479 | no |
475 | 32 | no |
476 | 12 | no |
477 | 346 | no |
478 | 1479 | yes |
479 | 419 | yes |
480 | 1033 | no |
481 | 475 | no |
482 | 27 | no |
483 | 21 | no |
484 | 1424 | no |
485 | 1434 | yes |
486 | 82 | no |
487 | 16 | no |
488 | 1902 | yes |
489 | 1865 | no |
490 | 794 | no |
491 | 2558 | no |
492 | 532 | no |
493 | 536 | no |
494 | 3288 | no |
495 | 1019 | no |
496 | 1 | no |
497 | 1141 | no |
498 | 1780 | no |
499 | 1009 | no |
500 | 46 | no |
501 | 1466 | yes |
502 | 158 | no |
503 | 409 | no |
504 | 4 | no |
505 | 3424 | yes |
506 | 1479 | no |
507 | 82 | no |
508 | 152 | no |
509 | 26 | no |
510 | 2285 | no |
511 | 149 | yes |
512 | 807 | yes |
513 | 93 | no |
514 | 1176 | no |
515 | 2696 | no |
516 | 1039 | no |
517 | 36 | no |
518 | 1551 | no |
519 | 477 | no |
520 | 16 | no |
521 | 992 | no |
522 | 800 | no |
523 | 79 | no |
524 | 444 | no |
525 | 6 | no |
526 | 436 | yes |
527 | 36 | no |
528 | 1479 | no |
529 | 3375 | yes |
530 | 2231 | no |
531 | 2650 | no |
532 | 63 | yes |
533 | 2165 | yes |
534 | 10 | no |
535 | 1033 | no |
536 | 419 | no |
537 | 1853 | yes |
538 | 426 | no |
539 | 3284 | no |
540 | 3467 | yes |
541 | 48 | no |
542 | 1121 | no |
543 | 707 | no |
544 | 421 | no |
545 | 225 | no |
546 | 150 | no |
547 | 901 | no |
548 | 4 | no |
549 | 61 | no |
550 | 1122 | no |
551 | 1121 | no |
552 | 904 | no |
553 | 2102 | no |
554 | 517 | no |
555 | 1415 | no |
556 | 2461 | yes |
557 | 608 | no |
558 | 103 | no |
559 | 900 | no |
560 | 1025 | no |
561 | 739 | no |
562 | 44 | no |
563 | 1617 | no |
564 | 1129 | no |
565 | 21 | no |
566 | 1788 | no |
567 | 432 | no |
568 | 17 | no |
569 | 6 | no |
570 | 1521 | no |
571 | 93 | no |
572 | 1134 | no |
573 | 21 | no |
574 | 85 | no |
575 | 897 | no |
576 | 9 | no |
577 | 23 | no |
578 | 2664 | yes |
579 | 3 | no |
580 | 79 | no |
581 | 3375 | yes |
582 | 891 | yes |
583 | 3467 | yes |
584 | 2962 | no |
585 | 49 | no |
586 | 20 | no |
587 | 94 | no |
588 | 18 | no |
589 | 2271 | yes |
590 | 1362 | yes |
591 | 162 | no |
592 | 465 | no |
593 | 57 | no |
594 | 476 | no |
595 | 46 | no |
596 | 12 | no |
597 | 46 | no |
598 | 1000 | no |
599 | 19 | no |
600 | 1006 | no |
601 | 14 | no |
602 | 164 | no |
603 | 481 | no |
604 | 1755 | yes |
605 | 1128 | no |
606 | 2003 | yes |
607 | 30 | no |
608 | 3060 | no |
609 | 1636 | no |
610 | 814 | no |
611 | 57 | no |
612 | 2204 | yes |
613 | 1126 | no |
614 | 2650 | no |
First things first Whenever you approach a problem like this you want to break it down into steps. So for this particular problem you could do it in the following
Ok so having these steps in mind what information do you need to make the information in steps 1&2 accessible to you... Well to acquire each track in an invoice all I need first is the invoice_id which gives me access to the invoice lines for that invoice_id. Then if an invoice is an album purchase the first track would suffice because for an album purchase to be considered as such all the tracks in the order would be from the same album.
This is where the view comes from it provides us with the base information needed. Also note this could be an iterative process so on some other problem in the future you might have to go back and modify the initial view you made.
%%sql
WITH invoice_first_track AS
(
SELECT
invoice_id,
MIN(track_id) first_track_id
FROM invoice_line il
GROUP BY invoice_id
)
select *
from invoice_first_track ;
* sqlite:///chinook.db Done.
invoice_id | first_track_id |
---|---|
1 | 1158 |
2 | 201 |
3 | 2516 |
4 | 748 |
5 | 1986 |
6 | 30 |
7 | 42 |
8 | 81 |
9 | 196 |
10 | 2663 |
11 | 610 |
12 | 92 |
13 | 2553 |
14 | 541 |
15 | 807 |
16 | 16 |
17 | 55 |
18 | 1027 |
19 | 105 |
20 | 60 |
21 | 13 |
22 | 383 |
23 | 1 |
24 | 1146 |
25 | 32 |
26 | 85 |
27 | 1280 |
28 | 36 |
29 | 479 |
30 | 33 |
31 | 2731 |
32 | 38 |
33 | 2542 |
34 | 610 |
35 | 6 |
36 | 153 |
37 | 698 |
38 | 154 |
39 | 184 |
40 | 108 |
41 | 153 |
42 | 13 |
43 | 918 |
44 | 3052 |
45 | 102 |
46 | 2639 |
47 | 8 |
48 | 58 |
49 | 146 |
50 | 1511 |
51 | 34 |
52 | 2664 |
53 | 207 |
54 | 1773 |
55 | 155 |
56 | 2254 |
57 | 2948 |
58 | 1029 |
59 | 198 |
60 | 46 |
61 | 2609 |
62 | 97 |
63 | 6 |
64 | 38 |
65 | 539 |
66 | 442 |
67 | 9 |
68 | 61 |
69 | 85 |
70 | 2307 |
71 | 2254 |
72 | 30 |
73 | 2391 |
74 | 1743 |
75 | 22 |
76 | 1020 |
77 | 15 |
78 | 33 |
79 | 1902 |
80 | 3400 |
81 | 545 |
82 | 54 |
83 | 1163 |
84 | 3455 |
85 | 1298 |
86 | 2291 |
87 | 1129 |
88 | 1995 |
89 | 1784 |
90 | 2554 |
91 | 94 |
92 | 1414 |
93 | 20 |
94 | 50 |
95 | 27 |
96 | 453 |
97 | 16 |
98 | 419 |
99 | 2591 |
100 | 996 |
101 | 83 |
102 | 33 |
103 | 1029 |
104 | 1508 |
105 | 751 |
106 | 2 |
107 | 1 |
108 | 58 |
109 | 340 |
110 | 81 |
111 | 48 |
112 | 1479 |
113 | 134 |
114 | 1479 |
115 | 84 |
116 | 21 |
117 | 4 |
118 | 3111 |
119 | 1556 |
120 | 17 |
121 | 32 |
122 | 28 |
123 | 2003 |
124 | 470 |
125 | 45 |
126 | 42 |
127 | 2 |
128 | 836 |
129 | 12 |
130 | 1573 |
131 | 920 |
132 | 2234 |
133 | 37 |
134 | 204 |
135 | 1221 |
136 | 1157 |
137 | 43 |
138 | 2271 |
139 | 781 |
140 | 468 |
141 | 2621 |
142 | 23 |
143 | 479 |
144 | 158 |
145 | 1422 |
146 | 1466 |
147 | 56 |
148 | 24 |
149 | 28 |
150 | 1021 |
151 | 605 |
152 | 1030 |
153 | 2406 |
154 | 2649 |
155 | 94 |
156 | 1882 |
157 | 3336 |
158 | 1050 |
159 | 153 |
160 | 36 |
161 | 55 |
162 | 899 |
163 | 768 |
164 | 913 |
165 | 59 |
166 | 56 |
167 | 484 |
168 | 58 |
169 | 18 |
170 | 157 |
171 | 1466 |
172 | 1185 |
173 | 2285 |
174 | 422 |
175 | 1755 |
176 | 1114 |
177 | 1039 |
178 | 1102 |
179 | 39 |
180 | 1150 |
181 | 1470 |
182 | 86 |
183 | 1033 |
184 | 3409 |
185 | 991 |
186 | 826 |
187 | 1037 |
188 | 1455 |
189 | 2191 |
190 | 833 |
191 | 24 |
192 | 130 |
193 | 2548 |
194 | 1476 |
195 | 51 |
196 | 1325 |
197 | 592 |
198 | 1121 |
199 | 2008 |
200 | 15 |
201 | 3288 |
202 | 1747 |
203 | 6 |
204 | 14 |
205 | 1829 |
206 | 224 |
207 | 470 |
208 | 151 |
209 | 1495 |
210 | 18 |
211 | 39 |
212 | 1121 |
213 | 2271 |
214 | 80 |
215 | 1019 |
216 | 461 |
217 | 2554 |
218 | 894 |
219 | 897 |
220 | 1745 |
221 | 19 |
222 | 1029 |
223 | 2621 |
224 | 20 |
225 | 32 |
226 | 1132 |
227 | 468 |
228 | 531 |
229 | 58 |
230 | 15 |
231 | 154 |
232 | 6 |
233 | 9 |
234 | 2562 |
235 | 1129 |
236 | 164 |
237 | 2375 |
238 | 2271 |
239 | 2254 |
240 | 1774 |
241 | 2289 |
242 | 61 |
243 | 54 |
244 | 1026 |
245 | 1015 |
246 | 55 |
247 | 2639 |
248 | 152 |
249 | 1470 |
250 | 435 |
251 | 30 |
252 | 1 |
253 | 1121 |
254 | 183 |
255 | 529 |
256 | 1491 |
257 | 2285 |
258 | 1990 |
259 | 29 |
260 | 77 |
261 | 58 |
262 | 77 |
263 | 62 |
264 | 1187 |
265 | 109 |
266 | 1170 |
267 | 1997 |
268 | 13 |
269 | 3144 |
270 | 23 |
271 | 2271 |
272 | 61 |
273 | 1479 |
274 | 402 |
275 | 268 |
276 | 3300 |
277 | 128 |
278 | 1468 |
279 | 1184 |
280 | 3081 |
281 | 1902 |
282 | 891 |
283 | 95 |
284 | 194 |
285 | 134 |
286 | 1748 |
287 | 2004 |
288 | 2 |
289 | 16 |
290 | 438 |
291 | 165 |
292 | 162 |
293 | 802 |
294 | 18 |
295 | 1491 |
296 | 1466 |
297 | 2207 |
298 | 34 |
299 | 1042 |
300 | 51 |
301 | 5 |
302 | 77 |
303 | 43 |
304 | 11 |
305 | 2178 |
306 | 49 |
307 | 38 |
308 | 2964 |
309 | 1094 |
310 | 29 |
311 | 32 |
312 | 58 |
313 | 1454 |
314 | 31 |
315 | 1445 |
316 | 1050 |
317 | 3288 |
318 | 26 |
319 | 62 |
320 | 3319 |
321 | 2607 |
322 | 13 |
323 | 1125 |
324 | 31 |
325 | 1943 |
326 | 135 |
327 | 6 |
328 | 481 |
329 | 3299 |
330 | 1133 |
331 | 36 |
332 | 476 |
333 | 1006 |
334 | 1942 |
335 | 2391 |
336 | 2358 |
337 | 804 |
338 | 1523 |
339 | 696 |
340 | 1051 |
341 | 1520 |
342 | 1679 |
343 | 161 |
344 | 1479 |
345 | 35 |
346 | 151 |
347 | 16 |
348 | 9 |
349 | 1712 |
350 | 1017 |
351 | 26 |
352 | 2433 |
353 | 162 |
354 | 1387 |
355 | 13 |
356 | 51 |
357 | 469 |
358 | 93 |
359 | 35 |
360 | 2367 |
361 | 1991 |
362 | 695 |
363 | 433 |
364 | 10 |
365 | 157 |
366 | 195 |
367 | 154 |
368 | 952 |
369 | 514 |
370 | 922 |
371 | 2410 |
372 | 529 |
373 | 139 |
374 | 469 |
375 | 1268 |
376 | 615 |
377 | 49 |
378 | 1056 |
379 | 2542 |
380 | 475 |
381 | 1133 |
382 | 43 |
383 | 486 |
384 | 152 |
385 | 1 |
386 | 45 |
387 | 337 |
388 | 999 |
389 | 3064 |
390 | 1988 |
391 | 80 |
392 | 1853 |
393 | 80 |
394 | 15 |
395 | 757 |
396 | 822 |
397 | 781 |
398 | 2506 |
399 | 1146 |
400 | 40 |
401 | 13 |
402 | 1127 |
403 | 52 |
404 | 1174 |
405 | 542 |
406 | 1460 |
407 | 2204 |
408 | 1043 |
409 | 1021 |
410 | 131 |
411 | 6 |
412 | 261 |
413 | 2690 |
414 | 1 |
415 | 29 |
416 | 1048 |
417 | 4 |
418 | 2755 |
419 | 33 |
420 | 999 |
421 | 1 |
422 | 82 |
423 | 7 |
424 | 1495 |
425 | 1483 |
426 | 43 |
427 | 23 |
428 | 1013 |
429 | 828 |
430 | 1788 |
431 | 160 |
432 | 94 |
433 | 27 |
434 | 27 |
435 | 426 |
436 | 77 |
437 | 301 |
438 | 151 |
439 | 423 |
440 | 88 |
441 | 1079 |
442 | 572 |
443 | 13 |
444 | 2229 |
445 | 411 |
446 | 1 |
447 | 2285 |
448 | 186 |
449 | 1479 |
450 | 424 |
451 | 3044 |
452 | 572 |
453 | 9 |
454 | 1986 |
455 | 836 |
456 | 6 |
457 | 1562 |
458 | 3473 |
459 | 226 |
460 | 51 |
461 | 160 |
462 | 6 |
463 | 38 |
464 | 38 |
465 | 149 |
466 | 73 |
467 | 154 |
468 | 2149 |
469 | 2232 |
470 | 94 |
471 | 2368 |
472 | 697 |
473 | 2690 |
474 | 479 |
475 | 32 |
476 | 12 |
477 | 346 |
478 | 1479 |
479 | 419 |
480 | 1033 |
481 | 475 |
482 | 27 |
483 | 21 |
484 | 1424 |
485 | 1434 |
486 | 82 |
487 | 16 |
488 | 1902 |
489 | 1865 |
490 | 794 |
491 | 2558 |
492 | 532 |
493 | 536 |
494 | 3288 |
495 | 1019 |
496 | 1 |
497 | 1141 |
498 | 1780 |
499 | 1009 |
500 | 46 |
501 | 1466 |
502 | 158 |
503 | 409 |
504 | 4 |
505 | 3424 |
506 | 1479 |
507 | 82 |
508 | 152 |
509 | 26 |
510 | 2285 |
511 | 149 |
512 | 807 |
513 | 93 |
514 | 1176 |
515 | 2696 |
516 | 1039 |
517 | 36 |
518 | 1551 |
519 | 477 |
520 | 16 |
521 | 992 |
522 | 800 |
523 | 79 |
524 | 444 |
525 | 6 |
526 | 436 |
527 | 36 |
528 | 1479 |
529 | 3375 |
530 | 2231 |
531 | 2650 |
532 | 63 |
533 | 2165 |
534 | 10 |
535 | 1033 |
536 | 419 |
537 | 1853 |
538 | 426 |
539 | 3284 |
540 | 3467 |
541 | 48 |
542 | 1121 |
543 | 707 |
544 | 421 |
545 | 225 |
546 | 150 |
547 | 901 |
548 | 4 |
549 | 61 |
550 | 1122 |
551 | 1121 |
552 | 904 |
553 | 2102 |
554 | 517 |
555 | 1415 |
556 | 2461 |
557 | 608 |
558 | 103 |
559 | 900 |
560 | 1025 |
561 | 739 |
562 | 44 |
563 | 1617 |
564 | 1129 |
565 | 21 |
566 | 1788 |
567 | 432 |
568 | 17 |
569 | 6 |
570 | 1521 |
571 | 93 |
572 | 1134 |
573 | 21 |
574 | 85 |
575 | 897 |
576 | 9 |
577 | 23 |
578 | 2664 |
579 | 3 |
580 | 79 |
581 | 3375 |
582 | 891 |
583 | 3467 |
584 | 2962 |
585 | 49 |
586 | 20 |
587 | 94 |
588 | 18 |
589 | 2271 |
590 | 1362 |
591 | 162 |
592 | 465 |
593 | 57 |
594 | 476 |
595 | 46 |
596 | 12 |
597 | 46 |
598 | 1000 |
599 | 19 |
600 | 1006 |
601 | 14 |
602 | 164 |
603 | 481 |
604 | 1755 |
605 | 1128 |
606 | 2003 |
607 | 30 |
608 | 3060 |
609 | 1636 |
610 | 814 |
611 | 57 |
612 | 2204 |
613 | 1126 |
614 | 2650 |
Now how can we obtain each track in the invoice lines of an invoice. Assuming invoice_first_track is in the FROM clause... we can get each track_id like so `
# SELECT il2.track_id FROM invoice_line il2
# WHERE il2.invoice_id = ifs.invoice_id
Next on to step 2 of the process and remember With invoice_first_track in the FROM clause
We can proceed in the following manner
Remember all we need is one track_id to determine whether this is an album purchase or not. So we use that track_id to find the album of the particular track then… with the album_id we can see which tracks share that same album_id and return that
We can proceed in the following manner
# (
# SELECT t.track_id FROM track t
# WHERE t.album_id = (
# SELECT t2.album_id FROM track t2
# WHERE t2.track_id = ifs.first_track_id
# )
So this is kind of whats going on each row of the view (just think about the first row of this view)
%%sql
WITH invoice_first_track AS
(
SELECT
invoice_id,
MIN(track_id) first_track_id
FROM invoice_line il
GROUP BY invoice_id
)
select *
from invoice_first_track ;
* sqlite:///chinook.db Done.
invoice_id | first_track_id |
---|---|
1 | 1158 |
2 | 201 |
3 | 2516 |
4 | 748 |
5 | 1986 |
6 | 30 |
7 | 42 |
8 | 81 |
9 | 196 |
10 | 2663 |
11 | 610 |
12 | 92 |
13 | 2553 |
14 | 541 |
15 | 807 |
16 | 16 |
17 | 55 |
18 | 1027 |
19 | 105 |
20 | 60 |
21 | 13 |
22 | 383 |
23 | 1 |
24 | 1146 |
25 | 32 |
26 | 85 |
27 | 1280 |
28 | 36 |
29 | 479 |
30 | 33 |
31 | 2731 |
32 | 38 |
33 | 2542 |
34 | 610 |
35 | 6 |
36 | 153 |
37 | 698 |
38 | 154 |
39 | 184 |
40 | 108 |
41 | 153 |
42 | 13 |
43 | 918 |
44 | 3052 |
45 | 102 |
46 | 2639 |
47 | 8 |
48 | 58 |
49 | 146 |
50 | 1511 |
51 | 34 |
52 | 2664 |
53 | 207 |
54 | 1773 |
55 | 155 |
56 | 2254 |
57 | 2948 |
58 | 1029 |
59 | 198 |
60 | 46 |
61 | 2609 |
62 | 97 |
63 | 6 |
64 | 38 |
65 | 539 |
66 | 442 |
67 | 9 |
68 | 61 |
69 | 85 |
70 | 2307 |
71 | 2254 |
72 | 30 |
73 | 2391 |
74 | 1743 |
75 | 22 |
76 | 1020 |
77 | 15 |
78 | 33 |
79 | 1902 |
80 | 3400 |
81 | 545 |
82 | 54 |
83 | 1163 |
84 | 3455 |
85 | 1298 |
86 | 2291 |
87 | 1129 |
88 | 1995 |
89 | 1784 |
90 | 2554 |
91 | 94 |
92 | 1414 |
93 | 20 |
94 | 50 |
95 | 27 |
96 | 453 |
97 | 16 |
98 | 419 |
99 | 2591 |
100 | 996 |
101 | 83 |
102 | 33 |
103 | 1029 |
104 | 1508 |
105 | 751 |
106 | 2 |
107 | 1 |
108 | 58 |
109 | 340 |
110 | 81 |
111 | 48 |
112 | 1479 |
113 | 134 |
114 | 1479 |
115 | 84 |
116 | 21 |
117 | 4 |
118 | 3111 |
119 | 1556 |
120 | 17 |
121 | 32 |
122 | 28 |
123 | 2003 |
124 | 470 |
125 | 45 |
126 | 42 |
127 | 2 |
128 | 836 |
129 | 12 |
130 | 1573 |
131 | 920 |
132 | 2234 |
133 | 37 |
134 | 204 |
135 | 1221 |
136 | 1157 |
137 | 43 |
138 | 2271 |
139 | 781 |
140 | 468 |
141 | 2621 |
142 | 23 |
143 | 479 |
144 | 158 |
145 | 1422 |
146 | 1466 |
147 | 56 |
148 | 24 |
149 | 28 |
150 | 1021 |
151 | 605 |
152 | 1030 |
153 | 2406 |
154 | 2649 |
155 | 94 |
156 | 1882 |
157 | 3336 |
158 | 1050 |
159 | 153 |
160 | 36 |
161 | 55 |
162 | 899 |
163 | 768 |
164 | 913 |
165 | 59 |
166 | 56 |
167 | 484 |
168 | 58 |
169 | 18 |
170 | 157 |
171 | 1466 |
172 | 1185 |
173 | 2285 |
174 | 422 |
175 | 1755 |
176 | 1114 |
177 | 1039 |
178 | 1102 |
179 | 39 |
180 | 1150 |
181 | 1470 |
182 | 86 |
183 | 1033 |
184 | 3409 |
185 | 991 |
186 | 826 |
187 | 1037 |
188 | 1455 |
189 | 2191 |
190 | 833 |
191 | 24 |
192 | 130 |
193 | 2548 |
194 | 1476 |
195 | 51 |
196 | 1325 |
197 | 592 |
198 | 1121 |
199 | 2008 |
200 | 15 |
201 | 3288 |
202 | 1747 |
203 | 6 |
204 | 14 |
205 | 1829 |
206 | 224 |
207 | 470 |
208 | 151 |
209 | 1495 |
210 | 18 |
211 | 39 |
212 | 1121 |
213 | 2271 |
214 | 80 |
215 | 1019 |
216 | 461 |
217 | 2554 |
218 | 894 |
219 | 897 |
220 | 1745 |
221 | 19 |
222 | 1029 |
223 | 2621 |
224 | 20 |
225 | 32 |
226 | 1132 |
227 | 468 |
228 | 531 |
229 | 58 |
230 | 15 |
231 | 154 |
232 | 6 |
233 | 9 |
234 | 2562 |
235 | 1129 |
236 | 164 |
237 | 2375 |
238 | 2271 |
239 | 2254 |
240 | 1774 |
241 | 2289 |
242 | 61 |
243 | 54 |
244 | 1026 |
245 | 1015 |
246 | 55 |
247 | 2639 |
248 | 152 |
249 | 1470 |
250 | 435 |
251 | 30 |
252 | 1 |
253 | 1121 |
254 | 183 |
255 | 529 |
256 | 1491 |
257 | 2285 |
258 | 1990 |
259 | 29 |
260 | 77 |
261 | 58 |
262 | 77 |
263 | 62 |
264 | 1187 |
265 | 109 |
266 | 1170 |
267 | 1997 |
268 | 13 |
269 | 3144 |
270 | 23 |
271 | 2271 |
272 | 61 |
273 | 1479 |
274 | 402 |
275 | 268 |
276 | 3300 |
277 | 128 |
278 | 1468 |
279 | 1184 |
280 | 3081 |
281 | 1902 |
282 | 891 |
283 | 95 |
284 | 194 |
285 | 134 |
286 | 1748 |
287 | 2004 |
288 | 2 |
289 | 16 |
290 | 438 |
291 | 165 |
292 | 162 |
293 | 802 |
294 | 18 |
295 | 1491 |
296 | 1466 |
297 | 2207 |
298 | 34 |
299 | 1042 |
300 | 51 |
301 | 5 |
302 | 77 |
303 | 43 |
304 | 11 |
305 | 2178 |
306 | 49 |
307 | 38 |
308 | 2964 |
309 | 1094 |
310 | 29 |
311 | 32 |
312 | 58 |
313 | 1454 |
314 | 31 |
315 | 1445 |
316 | 1050 |
317 | 3288 |
318 | 26 |
319 | 62 |
320 | 3319 |
321 | 2607 |
322 | 13 |
323 | 1125 |
324 | 31 |
325 | 1943 |
326 | 135 |
327 | 6 |
328 | 481 |
329 | 3299 |
330 | 1133 |
331 | 36 |
332 | 476 |
333 | 1006 |
334 | 1942 |
335 | 2391 |
336 | 2358 |
337 | 804 |
338 | 1523 |
339 | 696 |
340 | 1051 |
341 | 1520 |
342 | 1679 |
343 | 161 |
344 | 1479 |
345 | 35 |
346 | 151 |
347 | 16 |
348 | 9 |
349 | 1712 |
350 | 1017 |
351 | 26 |
352 | 2433 |
353 | 162 |
354 | 1387 |
355 | 13 |
356 | 51 |
357 | 469 |
358 | 93 |
359 | 35 |
360 | 2367 |
361 | 1991 |
362 | 695 |
363 | 433 |
364 | 10 |
365 | 157 |
366 | 195 |
367 | 154 |
368 | 952 |
369 | 514 |
370 | 922 |
371 | 2410 |
372 | 529 |
373 | 139 |
374 | 469 |
375 | 1268 |
376 | 615 |
377 | 49 |
378 | 1056 |
379 | 2542 |
380 | 475 |
381 | 1133 |
382 | 43 |
383 | 486 |
384 | 152 |
385 | 1 |
386 | 45 |
387 | 337 |
388 | 999 |
389 | 3064 |
390 | 1988 |
391 | 80 |
392 | 1853 |
393 | 80 |
394 | 15 |
395 | 757 |
396 | 822 |
397 | 781 |
398 | 2506 |
399 | 1146 |
400 | 40 |
401 | 13 |
402 | 1127 |
403 | 52 |
404 | 1174 |
405 | 542 |
406 | 1460 |
407 | 2204 |
408 | 1043 |
409 | 1021 |
410 | 131 |
411 | 6 |
412 | 261 |
413 | 2690 |
414 | 1 |
415 | 29 |
416 | 1048 |
417 | 4 |
418 | 2755 |
419 | 33 |
420 | 999 |
421 | 1 |
422 | 82 |
423 | 7 |
424 | 1495 |
425 | 1483 |
426 | 43 |
427 | 23 |
428 | 1013 |
429 | 828 |
430 | 1788 |
431 | 160 |
432 | 94 |
433 | 27 |
434 | 27 |
435 | 426 |
436 | 77 |
437 | 301 |
438 | 151 |
439 | 423 |
440 | 88 |
441 | 1079 |
442 | 572 |
443 | 13 |
444 | 2229 |
445 | 411 |
446 | 1 |
447 | 2285 |
448 | 186 |
449 | 1479 |
450 | 424 |
451 | 3044 |
452 | 572 |
453 | 9 |
454 | 1986 |
455 | 836 |
456 | 6 |
457 | 1562 |
458 | 3473 |
459 | 226 |
460 | 51 |
461 | 160 |
462 | 6 |
463 | 38 |
464 | 38 |
465 | 149 |
466 | 73 |
467 | 154 |
468 | 2149 |
469 | 2232 |
470 | 94 |
471 | 2368 |
472 | 697 |
473 | 2690 |
474 | 479 |
475 | 32 |
476 | 12 |
477 | 346 |
478 | 1479 |
479 | 419 |
480 | 1033 |
481 | 475 |
482 | 27 |
483 | 21 |
484 | 1424 |
485 | 1434 |
486 | 82 |
487 | 16 |
488 | 1902 |
489 | 1865 |
490 | 794 |
491 | 2558 |
492 | 532 |
493 | 536 |
494 | 3288 |
495 | 1019 |
496 | 1 |
497 | 1141 |
498 | 1780 |
499 | 1009 |
500 | 46 |
501 | 1466 |
502 | 158 |
503 | 409 |
504 | 4 |
505 | 3424 |
506 | 1479 |
507 | 82 |
508 | 152 |
509 | 26 |
510 | 2285 |
511 | 149 |
512 | 807 |
513 | 93 |
514 | 1176 |
515 | 2696 |
516 | 1039 |
517 | 36 |
518 | 1551 |
519 | 477 |
520 | 16 |
521 | 992 |
522 | 800 |
523 | 79 |
524 | 444 |
525 | 6 |
526 | 436 |
527 | 36 |
528 | 1479 |
529 | 3375 |
530 | 2231 |
531 | 2650 |
532 | 63 |
533 | 2165 |
534 | 10 |
535 | 1033 |
536 | 419 |
537 | 1853 |
538 | 426 |
539 | 3284 |
540 | 3467 |
541 | 48 |
542 | 1121 |
543 | 707 |
544 | 421 |
545 | 225 |
546 | 150 |
547 | 901 |
548 | 4 |
549 | 61 |
550 | 1122 |
551 | 1121 |
552 | 904 |
553 | 2102 |
554 | 517 |
555 | 1415 |
556 | 2461 |
557 | 608 |
558 | 103 |
559 | 900 |
560 | 1025 |
561 | 739 |
562 | 44 |
563 | 1617 |
564 | 1129 |
565 | 21 |
566 | 1788 |
567 | 432 |
568 | 17 |
569 | 6 |
570 | 1521 |
571 | 93 |
572 | 1134 |
573 | 21 |
574 | 85 |
575 | 897 |
576 | 9 |
577 | 23 |
578 | 2664 |
579 | 3 |
580 | 79 |
581 | 3375 |
582 | 891 |
583 | 3467 |
584 | 2962 |
585 | 49 |
586 | 20 |
587 | 94 |
588 | 18 |
589 | 2271 |
590 | 1362 |
591 | 162 |
592 | 465 |
593 | 57 |
594 | 476 |
595 | 46 |
596 | 12 |
597 | 46 |
598 | 1000 |
599 | 19 |
600 | 1006 |
601 | 14 |
602 | 164 |
603 | 481 |
604 | 1755 |
605 | 1128 |
606 | 2003 |
607 | 30 |
608 | 3060 |
609 | 1636 |
610 | 814 |
611 | 57 |
612 | 2204 |
613 | 1126 |
614 | 2650 |
%%sql
WITH invoice_first_track AS
(
SELECT
invoice_id,
MIN(track_id) first_track_id
FROM invoice_line il
GROUP BY invoice_id
)
select
il.track_id
FROM invoice_first_track ifs
inner join invoice_line il on il.invoice_id = ifs.invoice_id
where ifs.invoice_id = 1 ;
* sqlite:///chinook.db Done.
track_id |
---|
1158 |
1159 |
1160 |
1161 |
1162 |
1163 |
1164 |
1165 |
1166 |
1167 |
1168 |
1169 |
1170 |
1171 |
1172 |
1173 |
%%sql
WITH invoice_first_track AS
(
SELECT
invoice_id,
MIN(track_id) first_track_id
FROM invoice_line il
GROUP BY invoice_id
)
select
t.album_id
FROM invoice_first_track ifs
inner join track t on t.track_id = ifs.first_track_id
WHERE ifs.first_track_id = 1158
* sqlite:///chinook.db Done.
album_id |
---|
91 |
%%sql
WITH invoice_first_track AS
(
SELECT
invoice_id,
MIN(track_id) first_track_id
FROM invoice_line il
GROUP BY invoice_id
)
select
t.track_id
from track t
where t.album_id = (select
t.album_id
FROM invoice_first_track ifs
inner join track t on t.track_id = ifs.first_track_id
WHERE ifs.first_track_id = 1158)
* sqlite:///chinook.db Done.
track_id |
---|
1158 |
1159 |
1160 |
1161 |
1162 |
1163 |
1164 |
1165 |
1166 |
1167 |
1168 |
1169 |
1170 |
1171 |
1172 |
1173 |
Then we can just compare these 2 groups using the except keyword and if they are null then at least one group doesn’t have any additional tracks compared to the other. After we’ll just flip it around to check for the next group and connect these two together with an AND keyword. like so...
# (
# SELECT t.track_id FROM track t
# WHERE t.album_id = (
# SELECT t2.album_id FROM track t2
# WHERE t2.track_id = ifs.first_track_id
# )
# EXCEPT
# SELECT il2.track_id FROM invoice_line il2
# WHERE il2.invoice_id = ifs.invoice_id
# ) IS NULL
# AND
# (
# SELECT il2.track_id FROM invoice_line il2
# WHERE il2.invoice_id = ifs.invoice_id
# EXCEPT
# SELECT t.track_id FROM track t
# WHERE t.album_id = (
# SELECT t2.album_id FROM track t2
# WHERE t2.track_id = ifs.first_track_id
# )
# ) IS NULL
So if it is True that means both groups have the same exact tracks and if false then it’s not so.
So the rest should be self explanatory from the lesson\
%%sql
WITH invoice_first_track AS
(
SELECT
invoice_id,
MIN(track_id) first_track_id
FROM invoice_line il
GROUP BY invoice_id
)
SELECT
ifs.*,
CASE
WHEN
(
SELECT t.track_id FROM track t
WHERE t.album_id = (
SELECT t2.album_id FROM track t2
WHERE t2.track_id = ifs.first_track_id
)
EXCEPT
SELECT il2.track_id FROM invoice_line il2
WHERE il2.invoice_id = ifs.invoice_id
) IS NULL
AND
(
SELECT il2.track_id FROM invoice_line il2
WHERE il2.invoice_id = ifs.invoice_id
EXCEPT
SELECT t.track_id FROM track t
WHERE t.album_id = (
SELECT t2.album_id FROM track t2
WHERE t2.track_id = ifs.first_track_id
)
) IS NULL
THEN "yes"
ELSE "no"
END AS "album_purchase"
FROM invoice_first_track ifs
* sqlite:///chinook.db Done.
invoice_id | first_track_id | album_purchase |
---|---|---|
1 | 1158 | yes |
2 | 201 | no |
3 | 2516 | no |
4 | 748 | no |
5 | 1986 | yes |
6 | 30 | no |
7 | 42 | no |
8 | 81 | no |
9 | 196 | no |
10 | 2663 | no |
11 | 610 | no |
12 | 92 | no |
13 | 2553 | no |
14 | 541 | no |
15 | 807 | no |
16 | 16 | no |
17 | 55 | no |
18 | 1027 | no |
19 | 105 | no |
20 | 60 | no |
21 | 13 | no |
22 | 383 | no |
23 | 1 | yes |
24 | 1146 | yes |
25 | 32 | no |
26 | 85 | no |
27 | 1280 | no |
28 | 36 | no |
29 | 479 | no |
30 | 33 | no |
31 | 2731 | yes |
32 | 38 | yes |
33 | 2542 | no |
34 | 610 | no |
35 | 6 | no |
36 | 153 | no |
37 | 698 | no |
38 | 154 | no |
39 | 184 | no |
40 | 108 | no |
41 | 153 | no |
42 | 13 | no |
43 | 918 | no |
44 | 3052 | yes |
45 | 102 | no |
46 | 2639 | yes |
47 | 8 | no |
48 | 58 | no |
49 | 146 | no |
50 | 1511 | no |
51 | 34 | no |
52 | 2664 | yes |
53 | 207 | no |
54 | 1773 | yes |
55 | 155 | no |
56 | 2254 | yes |
57 | 2948 | no |
58 | 1029 | no |
59 | 198 | no |
60 | 46 | no |
61 | 2609 | yes |
62 | 97 | no |
63 | 6 | no |
64 | 38 | yes |
65 | 539 | no |
66 | 442 | no |
67 | 9 | no |
68 | 61 | no |
69 | 85 | yes |
70 | 2307 | no |
71 | 2254 | yes |
72 | 30 | no |
73 | 2391 | yes |
74 | 1743 | no |
75 | 22 | no |
76 | 1020 | yes |
77 | 15 | no |
78 | 33 | no |
79 | 1902 | yes |
80 | 3400 | no |
81 | 545 | no |
82 | 54 | no |
83 | 1163 | no |
84 | 3455 | yes |
85 | 1298 | no |
86 | 2291 | no |
87 | 1129 | no |
88 | 1995 | no |
89 | 1784 | no |
90 | 2554 | yes |
91 | 94 | no |
92 | 1414 | yes |
93 | 20 | no |
94 | 50 | no |
95 | 27 | no |
96 | 453 | no |
97 | 16 | no |
98 | 419 | no |
99 | 2591 | yes |
100 | 996 | no |
101 | 83 | no |
102 | 33 | no |
103 | 1029 | no |
104 | 1508 | no |
105 | 751 | no |
106 | 2 | no |
107 | 1 | no |
108 | 58 | no |
109 | 340 | no |
110 | 81 | no |
111 | 48 | no |
112 | 1479 | yes |
113 | 134 | no |
114 | 1479 | yes |
115 | 84 | no |
116 | 21 | no |
117 | 4 | no |
118 | 3111 | no |
119 | 1556 | no |
120 | 17 | no |
121 | 32 | no |
122 | 28 | no |
123 | 2003 | yes |
124 | 470 | no |
125 | 45 | no |
126 | 42 | no |
127 | 2 | no |
128 | 836 | no |
129 | 12 | no |
130 | 1573 | no |
131 | 920 | no |
132 | 2234 | no |
133 | 37 | no |
134 | 204 | no |
135 | 1221 | no |
136 | 1157 | no |
137 | 43 | no |
138 | 2271 | yes |
139 | 781 | no |
140 | 468 | no |
141 | 2621 | yes |
142 | 23 | yes |
143 | 479 | no |
144 | 158 | no |
145 | 1422 | no |
146 | 1466 | yes |
147 | 56 | no |
148 | 24 | no |
149 | 28 | no |
150 | 1021 | no |
151 | 605 | no |
152 | 1030 | no |
153 | 2406 | yes |
154 | 2649 | no |
155 | 94 | no |
156 | 1882 | yes |
157 | 3336 | yes |
158 | 1050 | no |
159 | 153 | no |
160 | 36 | no |
161 | 55 | no |
162 | 899 | no |
163 | 768 | no |
164 | 913 | no |
165 | 59 | no |
166 | 56 | no |
167 | 484 | no |
168 | 58 | no |
169 | 18 | no |
170 | 157 | no |
171 | 1466 | yes |
172 | 1185 | no |
173 | 2285 | yes |
174 | 422 | no |
175 | 1755 | yes |
176 | 1114 | no |
177 | 1039 | no |
178 | 1102 | yes |
179 | 39 | no |
180 | 1150 | no |
181 | 1470 | no |
182 | 86 | no |
183 | 1033 | yes |
184 | 3409 | yes |
185 | 991 | no |
186 | 826 | no |
187 | 1037 | no |
188 | 1455 | yes |
189 | 2191 | no |
190 | 833 | no |
191 | 24 | no |
192 | 130 | no |
193 | 2548 | no |
194 | 1476 | no |
195 | 51 | no |
196 | 1325 | yes |
197 | 592 | no |
198 | 1121 | no |
199 | 2008 | no |
200 | 15 | yes |
201 | 3288 | yes |
202 | 1747 | no |
203 | 6 | no |
204 | 14 | no |
205 | 1829 | yes |
206 | 224 | no |
207 | 470 | no |
208 | 151 | no |
209 | 1495 | no |
210 | 18 | no |
211 | 39 | no |
212 | 1121 | yes |
213 | 2271 | yes |
214 | 80 | no |
215 | 1019 | no |
216 | 461 | no |
217 | 2554 | no |
218 | 894 | no |
219 | 897 | no |
220 | 1745 | no |
221 | 19 | no |
222 | 1029 | no |
223 | 2621 | yes |
224 | 20 | no |
225 | 32 | no |
226 | 1132 | no |
227 | 468 | no |
228 | 531 | no |
229 | 58 | no |
230 | 15 | yes |
231 | 154 | no |
232 | 6 | no |
233 | 9 | no |
234 | 2562 | no |
235 | 1129 | no |
236 | 164 | no |
237 | 2375 | yes |
238 | 2271 | no |
239 | 2254 | no |
240 | 1774 | no |
241 | 2289 | no |
242 | 61 | no |
243 | 54 | no |
244 | 1026 | no |
245 | 1015 | no |
246 | 55 | no |
247 | 2639 | yes |
248 | 152 | no |
249 | 1470 | no |
250 | 435 | no |
251 | 30 | no |
252 | 1 | no |
253 | 1121 | yes |
254 | 183 | no |
255 | 529 | no |
256 | 1491 | no |
257 | 2285 | yes |
258 | 1990 | no |
259 | 29 | no |
260 | 77 | no |
261 | 58 | no |
262 | 77 | yes |
263 | 62 | no |
264 | 1187 | no |
265 | 109 | no |
266 | 1170 | no |
267 | 1997 | no |
268 | 13 | no |
269 | 3144 | no |
270 | 23 | no |
271 | 2271 | yes |
272 | 61 | no |
273 | 1479 | yes |
274 | 402 | no |
275 | 268 | no |
276 | 3300 | yes |
277 | 128 | no |
278 | 1468 | no |
279 | 1184 | no |
280 | 3081 | yes |
281 | 1902 | yes |
282 | 891 | no |
283 | 95 | no |
284 | 194 | yes |
285 | 134 | no |
286 | 1748 | no |
287 | 2004 | no |
288 | 2 | no |
289 | 16 | no |
290 | 438 | no |
291 | 165 | no |
292 | 162 | no |
293 | 802 | no |
294 | 18 | no |
295 | 1491 | no |
296 | 1466 | yes |
297 | 2207 | no |
298 | 34 | no |
299 | 1042 | no |
300 | 51 | yes |
301 | 5 | no |
302 | 77 | no |
303 | 43 | no |
304 | 11 | no |
305 | 2178 | yes |
306 | 49 | no |
307 | 38 | no |
308 | 2964 | yes |
309 | 1094 | no |
310 | 29 | no |
311 | 32 | no |
312 | 58 | no |
313 | 1454 | no |
314 | 31 | no |
315 | 1445 | no |
316 | 1050 | no |
317 | 3288 | yes |
318 | 26 | no |
319 | 62 | no |
320 | 3319 | yes |
321 | 2607 | no |
322 | 13 | no |
323 | 1125 | no |
324 | 31 | no |
325 | 1943 | no |
326 | 135 | no |
327 | 6 | no |
328 | 481 | no |
329 | 3299 | no |
330 | 1133 | yes |
331 | 36 | no |
332 | 476 | no |
333 | 1006 | no |
334 | 1942 | yes |
335 | 2391 | yes |
336 | 2358 | no |
337 | 804 | no |
338 | 1523 | no |
339 | 696 | no |
340 | 1051 | no |
341 | 1520 | yes |
342 | 1679 | no |
343 | 161 | no |
344 | 1479 | yes |
345 | 35 | no |
346 | 151 | no |
347 | 16 | no |
348 | 9 | no |
349 | 1712 | no |
350 | 1017 | no |
351 | 26 | no |
352 | 2433 | no |
353 | 162 | no |
354 | 1387 | no |
355 | 13 | no |
356 | 51 | no |
357 | 469 | no |
358 | 93 | no |
359 | 35 | no |
360 | 2367 | no |
361 | 1991 | no |
362 | 695 | yes |
363 | 433 | no |
364 | 10 | no |
365 | 157 | no |
366 | 195 | no |
367 | 154 | no |
368 | 952 | no |
369 | 514 | no |
370 | 922 | no |
371 | 2410 | no |
372 | 529 | yes |
373 | 139 | no |
374 | 469 | no |
375 | 1268 | yes |
376 | 615 | no |
377 | 49 | no |
378 | 1056 | no |
379 | 2542 | yes |
380 | 475 | no |
381 | 1133 | yes |
382 | 43 | no |
383 | 486 | no |
384 | 152 | no |
385 | 1 | no |
386 | 45 | no |
387 | 337 | no |
388 | 999 | yes |
389 | 3064 | yes |
390 | 1988 | no |
391 | 80 | no |
392 | 1853 | yes |
393 | 80 | no |
394 | 15 | no |
395 | 757 | no |
396 | 822 | no |
397 | 781 | no |
398 | 2506 | yes |
399 | 1146 | yes |
400 | 40 | no |
401 | 13 | no |
402 | 1127 | no |
403 | 52 | no |
404 | 1174 | yes |
405 | 542 | no |
406 | 1460 | no |
407 | 2204 | no |
408 | 1043 | no |
409 | 1021 | no |
410 | 131 | no |
411 | 6 | no |
412 | 261 | no |
413 | 2690 | yes |
414 | 1 | yes |
415 | 29 | no |
416 | 1048 | no |
417 | 4 | no |
418 | 2755 | no |
419 | 33 | no |
420 | 999 | yes |
421 | 1 | no |
422 | 82 | no |
423 | 7 | no |
424 | 1495 | no |
425 | 1483 | no |
426 | 43 | no |
427 | 23 | yes |
428 | 1013 | no |
429 | 828 | no |
430 | 1788 | no |
431 | 160 | no |
432 | 94 | no |
433 | 27 | no |
434 | 27 | no |
435 | 426 | no |
436 | 77 | no |
437 | 301 | no |
438 | 151 | no |
439 | 423 | no |
440 | 88 | no |
441 | 1079 | no |
442 | 572 | no |
443 | 13 | no |
444 | 2229 | yes |
445 | 411 | no |
446 | 1 | no |
447 | 2285 | yes |
448 | 186 | no |
449 | 1479 | yes |
450 | 424 | no |
451 | 3044 | no |
452 | 572 | no |
453 | 9 | no |
454 | 1986 | no |
455 | 836 | no |
456 | 6 | no |
457 | 1562 | yes |
458 | 3473 | no |
459 | 226 | no |
460 | 51 | no |
461 | 160 | no |
462 | 6 | no |
463 | 38 | no |
464 | 38 | no |
465 | 149 | yes |
466 | 73 | no |
467 | 154 | no |
468 | 2149 | yes |
469 | 2232 | no |
470 | 94 | no |
471 | 2368 | no |
472 | 697 | no |
473 | 2690 | no |
474 | 479 | no |
475 | 32 | no |
476 | 12 | no |
477 | 346 | no |
478 | 1479 | yes |
479 | 419 | yes |
480 | 1033 | no |
481 | 475 | no |
482 | 27 | no |
483 | 21 | no |
484 | 1424 | no |
485 | 1434 | yes |
486 | 82 | no |
487 | 16 | no |
488 | 1902 | yes |
489 | 1865 | no |
490 | 794 | no |
491 | 2558 | no |
492 | 532 | no |
493 | 536 | no |
494 | 3288 | no |
495 | 1019 | no |
496 | 1 | no |
497 | 1141 | no |
498 | 1780 | no |
499 | 1009 | no |
500 | 46 | no |
501 | 1466 | yes |
502 | 158 | no |
503 | 409 | no |
504 | 4 | no |
505 | 3424 | yes |
506 | 1479 | no |
507 | 82 | no |
508 | 152 | no |
509 | 26 | no |
510 | 2285 | no |
511 | 149 | yes |
512 | 807 | yes |
513 | 93 | no |
514 | 1176 | no |
515 | 2696 | no |
516 | 1039 | no |
517 | 36 | no |
518 | 1551 | no |
519 | 477 | no |
520 | 16 | no |
521 | 992 | no |
522 | 800 | no |
523 | 79 | no |
524 | 444 | no |
525 | 6 | no |
526 | 436 | yes |
527 | 36 | no |
528 | 1479 | no |
529 | 3375 | yes |
530 | 2231 | no |
531 | 2650 | no |
532 | 63 | yes |
533 | 2165 | yes |
534 | 10 | no |
535 | 1033 | no |
536 | 419 | no |
537 | 1853 | yes |
538 | 426 | no |
539 | 3284 | no |
540 | 3467 | yes |
541 | 48 | no |
542 | 1121 | no |
543 | 707 | no |
544 | 421 | no |
545 | 225 | no |
546 | 150 | no |
547 | 901 | no |
548 | 4 | no |
549 | 61 | no |
550 | 1122 | no |
551 | 1121 | no |
552 | 904 | no |
553 | 2102 | no |
554 | 517 | no |
555 | 1415 | no |
556 | 2461 | yes |
557 | 608 | no |
558 | 103 | no |
559 | 900 | no |
560 | 1025 | no |
561 | 739 | no |
562 | 44 | no |
563 | 1617 | no |
564 | 1129 | no |
565 | 21 | no |
566 | 1788 | no |
567 | 432 | no |
568 | 17 | no |
569 | 6 | no |
570 | 1521 | no |
571 | 93 | no |
572 | 1134 | no |
573 | 21 | no |
574 | 85 | no |
575 | 897 | no |
576 | 9 | no |
577 | 23 | no |
578 | 2664 | yes |
579 | 3 | no |
580 | 79 | no |
581 | 3375 | yes |
582 | 891 | yes |
583 | 3467 | yes |
584 | 2962 | no |
585 | 49 | no |
586 | 20 | no |
587 | 94 | no |
588 | 18 | no |
589 | 2271 | yes |
590 | 1362 | yes |
591 | 162 | no |
592 | 465 | no |
593 | 57 | no |
594 | 476 | no |
595 | 46 | no |
596 | 12 | no |
597 | 46 | no |
598 | 1000 | no |
599 | 19 | no |
600 | 1006 | no |
601 | 14 | no |
602 | 164 | no |
603 | 481 | no |
604 | 1755 | yes |
605 | 1128 | no |
606 | 2003 | yes |
607 | 30 | no |
608 | 3060 | no |
609 | 1636 | no |
610 | 814 | no |
611 | 57 | no |
612 | 2204 | yes |
613 | 1126 | no |
614 | 2650 | no |
Now we can add the original branch that we omitted as well to tailor the results to our use case
%%sql
WITH invoice_first_track AS
(
SELECT
il.invoice_id invoice_id,
MIN(il.track_id) first_track_id
FROM invoice_line il
GROUP BY 1
)
SELECT
album_purchase,
COUNT(invoice_id) number_of_invoices,
CAST(count(invoice_id) AS FLOAT) / (
SELECT COUNT(*) FROM invoice
) percent
FROM
(
SELECT
ifs.*,
CASE
WHEN
(
SELECT t.track_id FROM track t
WHERE t.album_id = (
SELECT t2.album_id FROM track t2
WHERE t2.track_id = ifs.first_track_id
)
EXCEPT
SELECT il2.track_id FROM invoice_line il2
WHERE il2.invoice_id = ifs.invoice_id
) IS NULL
AND
(
SELECT il2.track_id FROM invoice_line il2
WHERE il2.invoice_id = ifs.invoice_id
EXCEPT
SELECT t.track_id FROM track t
WHERE t.album_id = (
SELECT t2.album_id FROM track t2
WHERE t2.track_id = ifs.first_track_id
)
) IS NULL
THEN "yes"
ELSE "no"
END AS "album_purchase"
FROM invoice_first_track ifs
)
GROUP BY album_purchase;
* sqlite:///chinook.db Done.
album_purchase | number_of_invoices | percent |
---|---|---|
no | 500 | 0.8143322475570033 |
yes | 114 | 0.18566775244299674 |