The way I’m addressing this is by creating two views (the first is intended to isolate invoices from the USA, the second joins the genres table to the tracks table). Finally, the third block combines these, but grouping by genre, and then counting the number of tracks sold per genre. As I look around the discussions here, I am finding that others are finding different output for their versions of this work. I am getting much lower numbers. So, these queries “work” together, but they do not seem to produce the correct results (it’s also nearly impossible for me to figure out, at this point, how I’d go a step further and calculate percentages, but I’d at least try and get this right). Anyone see what I might be doing wrong?
My Code:
First View:
CREATE VIEW USA_Only AS
SELECT i.BillingCountry, i.invoiceid, it.trackid
FROM invoices AS i
INNER JOIN invoice_items AS it ON it.invoiceid = i.invoiceid
WHERE BillingCountry = 'USA';
Second View:
CREATE VIEW Tracks_and_genres AS
SELECT t.trackid, t.genreid, g.name
FROM tracks AS t
INNER JOIN genres AS g ON g.genreid = t.genreid
Combining these views:
SELECT tg.name, COUNT(USA.trackid) AS Tracks_Sold_Per_Genre
FROM Tracks_and_genres AS tg
INNER JOIN USA_Only AS USA ON USA.trackid = tg.trackID
GROUP BY 1
ORDER BY 2 DESC;
I suspect that you may be using a different version of the chinook.db file. Cause I had to make some corrections in the table and column name to make your code work.
CREATE VIEW USA_Only AS
SELECT i.Billing_Country, i.invoice_id, it.track_id
FROM invoice AS i
INNER JOIN invoice_line AS it ON it.invoice_id = i.invoice_id
WHERE Billing_Country = 'USA';
CREATE VIEW Tracks_and_genres AS
SELECT t.track_id, t.genre_id, g.name
FROM track AS t
INNER JOIN genre AS g ON g.genre_id = t.genre_id;
SELECT tg.name, COUNT(USA.track_id) AS Tracks_Sold_Per_Genre
FROM Tracks_and_genres AS tg
INNER JOIN USA_Only AS USA ON USA.track_id = tg.track_id
GROUP BY 1
ORDER BY 2 DESC;
Here is the output:
name
Tracks_Sold_Per_Genre
Rock
561
Alternative & Punk
130
Metal
124
R&B/Soul
53
Blues
36
Alternative
35
Latin
22
Pop
22
Hip Hop/Rap
20
Jazz
14
Easy Listening
13
Reggae
6
Electronica/Dance
5
Classical
4
Heavy Metal
3
Soundtrack
2
TV Shows
1
Please use the attached database file, and let me know how that goes: chinook.db (1.0 MB)
I am stuck this question. From your answer, where can we know this question part? I can see the category
ranking but I can’t see “recommendation for the three artists”.
・Makes a recommendation for the three artists whose albums we should purchase for the store