-
Notifications
You must be signed in to change notification settings - Fork 0
/
queries4.sql
94 lines (85 loc) · 2.56 KB
/
queries4.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
#Sheets workbook: https://drive.google.com/drive/folders/1RHOkeSM-bIRmkkIodKZw1Ek9BQelW91d?usp=sharing
#q1 What kinds of music does Chinook have?
SELECT g.Name genre_name, COUNT(*)
FROM Track t
JOIN Album a
ON t.AlbumId = a.AlbumId
JOIN Artist s
ON a.ArtistId = s.ArtistId
JOIN Genre g
ON t.GenreId = g.GenreId
GROUP BY 1
ORDER BY 2 DESC;
#q2 What kinds of music do the customers listen to?
SELECT c.FirstName||" "||c.LastName customer_name, g.Name genre_name, COUNT(g.Name) top_genres_by_tracks_purchased
FROM Customer c
JOIN Invoice i
ON c.CustomerId = i.CustomerId
JOIN InvoiceLine l
ON i.InvoiceId = l.InvoiceId
JOIN Track t
ON l.TrackId = t.TrackId
JOIN Genre g
ON t.GenreId = g.GenreId
GROUP BY 1,2;
#q3 What kinds do each customer listen to?
WITH q2 AS (SELECT c.FirstName||" "||c.LastName customer_name, g.Name genre_name, COUNT(g.Name) topcount
FROM Customer c
JOIN Invoice i
ON c.CustomerId = i.CustomerId
JOIN InvoiceLine l
ON i.InvoiceId = l.InvoiceId
JOIN Track t
ON l.TrackId = t.TrackId
JOIN Genre g
ON t.GenreId = g.GenreId
GROUP BY 1,2
ORDER BY 1, 3 DESC)
SELECT q2.genre_name, SUM(q2.topcount) genre_count
FROM q2
GROUP BY 1
ORDER BY 2 DESC;
#q4
WITH q1 AS (SELECT c.FirstName||" "||c.LastName customer_name, g.Name genre_name, COUNT(g.Name) top_genre_by_tracks_purchased
FROM Customer c
JOIN Invoice i
ON c.CustomerId = i.CustomerId
JOIN InvoiceLine l
ON i.InvoiceId = l.InvoiceId
JOIN Track t
ON l.TrackId = t.TrackId
JOIN Genre g
ON t.GenreId = g.GenreId
GROUP BY 1,2
ORDER BY 1,3 DESC)
SELECT *
FROM q1
GROUP BY 1
HAVING 3 = MAX(3)
ORDER BY 3 DESC;
==========================================================
The counts:
#music inventory
SELECT DISTINCT (SELECT COUNT(ArtistId) FROM Artist) AS num_artists, (SELECT COUNT(AlbumId) FROM Album) AS num_albums, (SELECT COUNT(TrackId) FROM Track) AS num_tracks
FROM Track t
JOIN Album a
ON t.AlbumId = a.AlbumId
JOIN Artist s
ON a.ArtistId = s.ArtistId;
#csr to customer
SELECT c.SupportRepId, e.FirstName||" "||e.LastName csr_name, COUNT(c.SupportRepId) csr_to_customer, e.Email
FROM Customer c
JOIN Employee e
ON c.SupportRepId = e.EmployeeId
GROUP BY 1
ORDER BY 3 DESC;
#employees
SELECT e.FirstName||" "||e.LastName employee_name
FROM Employee e;
#customers
SELECT (SELECT COUNT(CustomerId) customer_count FROM Customer) AS customer_count, (SELECT COUNT(*) FROM Employee) AS employee_count, (SELECT COUNT(DISTINCT(e.LastName))
FROM Customer c
JOIN Employee e
ON c.SupportRepId = e.EmployeeId) AS csr_customer
FROM Customer
GROUP BY csr_customer;