Help isolating genres in chinook

Hi, I am working from this portion of the mission:

Screen Link: https://app.dataquest.io/m/191/guided-project%3A-answering-business-questions-using-sql/3/selecting-albums-to-purchase

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;
1 Like

Hi @Willyjgolden,

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)

Best,
Sahil

1 Like

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