WITH US_customer AS ( SELECT c.customer_id AS ID, il.quantity as quantity FROM invoice_line as il INNER JOIN invoice AS i ON il.invoice_id = i.invoice_id INNER JOIN customer AS c ON c.customer_id = i.customer_id WHERE c.country = 'USA') SELECT g.name AS genre_name, COUNT(il.quantity) as absolute, COUNT(il.quantity) / (SELECT COUNT(quantity) FROM US_customer) AS percentage FROM US_customer AS US INNER JOIN invoice AS i ON US.ID = i.customer_id INNER JOIN invoice_line AS il ON i.invoice_id = il.invoice_id INNER JOIN track AS t ON il.track_id = t.track_id INNER JOIN genre AS g ON t.genre_id = g.genre_id GROUP BY 1 ORDER BY 2 DESC;
What I expected to happen:
The photo attached below is the correct answer from the solution.
What actually happened:
The photo attached below is the incorrect answer/output from my code.
The WITH clause (US_customer) that I created is to obtain customer’s ID that are from USA only so that in the main query, I can match invoice, invoice_line, track, genre using join constraints that are based on USA only.
I know that this is a redundant process as I could have just did it like the sample solution code where he only connected track and genre in the main query, but I am just curious what is the mistake that causes my code to produce incorrect output.
Replace this line with the output/error
<!--Enter other details below: -->