advance_questions.sql

/* Question Set 3 - Advance */

-- Q1. Find how much amount spent by each customer on artists? 
-- Write a query to return customer name, artist name and total spent.
WITH BEST_SELLING_ARTIST AS (
	SELECT ART.ARTIST_ID, ART.NAME, SUM(IVL.UNIT_PRICE * IVL.QUANTITY) AS TOTAL_AMOUNT
	FROM INVOICE_LINE AS IVL
	JOIN TRACK AS TRK ON IVL.TRACK_ID = TRK.TRACK_ID
	JOIN ALBUM AS ALB ON TRK.ALBUM_ID = ALB.ALBUM_ID
	JOIN ARTIST AS ART ON ALB.ARTIST_ID = ART.ARTIST_ID
	GROUP BY ART.ARTIST_ID
	ORDER BY TOTAL_AMOUNT DESC LIMIT 1
)
SELECT CUS.CUSTOMER_ID, CUS.FIRST_NAME, CUS.LAST_NAME, BSA.NAME AS ARTIST_NAME, SUM(IVL.UNIT_PRICE * IVL.QUANTITY) AS AMOUNT_SPENT
FROM INVOICE AS INV
JOIN CUSTOMER AS CUS ON INV.CUSTOMER_ID = CUS.CUSTOMER_ID
JOIN INVOICE_LINE AS IVL ON INV.INVOICE_ID = IVL.INVOICE_ID
JOIN TRACK AS TRK ON IVL.TRACK_ID = TRK.TRACK_ID
JOIN ALBUM AS ALB ON TRK.ALBUM_ID = ALB.ALBUM_ID
JOIN BEST_SELLING_ARTIST AS BSA ON ALB.ARTIST_ID = BSA.ARTIST_ID
GROUP BY CUS.CUSTOMER_ID, BSA.NAME;


-- Q2: We want to find out the most popular music Genre for each country. 
-- A. We determine the most popular genre as the genre with the highest amount of purchases. 
-- B. Write a query that returns each country along with the top Genre. 
-- C. For countries where the maximum number of purchases is shared return all Genres.
WITH POPULAR_GENRE AS (
	SELECT CU.COUNTRY, COUNT(IL.QUANTITY) AS TOTAL_PURCHASES, GE.NAME AS TOP_GENRE, GE.GENRE_ID,
		ROW_NUMBER() OVER(
			PARTITION BY CU.COUNTRY ORDER BY COUNT(IL.QUANTITY) DESC
		) AS ROW_NUM
	FROM CUSTOMER AS CU
	JOIN INVOICE AS IV ON CU.CUSTOMER_ID = IV.CUSTOMER_ID
	JOIN INVOICE_LINE AS IL ON IV.INVOICE_ID = IL.INVOICE_ID
	JOIN TRACK AS TR ON IL.TRACK_ID = TR.TRACK_ID
	JOIN GENRE AS GE ON TR.GENRE_ID = GE.GENRE_ID
	GROUP BY CU.COUNTRY, GE.NAME, GE.GENRE_ID
	ORDER BY CU.COUNTRY, TOTAL_PURCHASES DESC
)
SELECT COUNTRY, TOP_GENRE, TOTAL_PURCHASES
FROM POPULAR_GENRE WHERE ROW_NUM = 1;


-- Q3: Write a query that determines the customer that has spent the most on music for each country. 
-- A. Write a query that returns the country along with the top customer and how much they spent. 
-- B. For countries where the top amount spent is shared, provide all customers who spent this amount.
WITH CUSTOMER_WITH_COUNTRY AS (
	SELECT CU.COUNTRY, CU.CUSTOMER_ID, CU.FIRST_NAME, CU.LAST_NAME, SUM(IV.TOTAL) AS AMOUNT_SPENT,
		ROW_NUMBER() OVER(
			PARTITION BY CU.COUNTRY ORDER BY SUM(IV.TOTAL) DESC
		) AS ROW_NUM
	FROM INVOICE AS IV
	JOIN CUSTOMER AS CU ON IV.CUSTOMER_ID = CU.CUSTOMER_ID
	GROUP BY CU.COUNTRY, CU.CUSTOMER_ID, CU.FIRST_NAME, CU.LAST_NAME
	ORDER BY CU.COUNTRY, AMOUNT_SPENT DESC
)
SELECT COUNTRY, CUSTOMER_ID, FIRST_NAME, LAST_NAME, AMOUNT_SPENT
FROM CUSTOMER_WITH_COUNTRY
WHERE ROW_NUM = 1;