GP: Answering Business Questions using SQL/ Please give me an advice for my answer

Q&A DQ Courses
, ,
#1

Hi everyone,

I am a new member and I am trying with the guide project below.
Screen Link: https://app.dataquest.io/m/191/guided-project%3A-answering-business-questions-using-sql/3/selecting-albums-to-purchase

My Code:

SELECT 
    g.name genre_USA,
    COUNT(t.track_id) track_per_genre
FROM customer c 
     INNER JOIN invoice i ON i.customer_id = c.customer_id
     INNER JOIN invoice_line il ON il.invoice_id = i.invoice_id
     INNER JOIN track t ON t.track_id = il.track_id
     INNER JOIN genre g ON g.genre_id = t.genre_id
WHERE c.country = 'USA'
GROUP BY 1;

The codes above return a table which include 2 columns (genre_USA, track_per_genre) and 17 rows. The numbers shown in the table are same with the number on solution.

My next task is creating an additional column that show the percentages, but I am stuck here.
My plan:

  • put the code above into a subquery
  • write the main query as following.
WITH ABCD AS 
    (SELECT 
          g.name genre_USA,
          COUNT(t.track_id) track_per_genre
    FROM customer c 
         INNER JOIN invoice i ON i.customer_id = c.customer_id
         INNER JOIN invoice_line il ON il.invoice_id = i.invoice_id
         INNER JOIN track t ON t.track_id = il.track_id
         INNER JOIN genre g ON g.genre_id = t.genre_id
    WHERE c.country = 'USA'
    GROUP BY 1)
    
SELECT
    ABCD.*,
    ABCD.track_per_genre/SUM(ABCD.track_per_genre) percentage
FROM ABCD
ORDER BY 2 DESC;

However, it returns a table with only 1 row (the last row of the subquery)
genre_USA || track_per_genre || percentage
TV Shows || 1 || 0

After reading the solution & other discussions here, then I found that my code is different.
The solution’s code (and other discussions here) are to join some tables first, then join the created subquery with some other tables.

While I joined all relating tables together (and use WITH to create a subquery) then calculated on the subquery. I often do that way for not only this guide project but also other missions before.

This time it doesn’t work well so I wonder where I miss some points.
Please help to give me some advices.