MUSICSTORE_ANALYSIS.sql

/*	Question Set 1 - Easy */

/* Q1: Who is the senior most employee based on job title? */

SELECT 
	last_name,first_name,
	title,levels
FROM 
	employee
order by
	levels desc
limit 1;


/* Q2: Which countries have the most Invoices? */

SELECT 
	BILLING_COUNTRY,COUNT(*) AS TOTAL_INVOICES
FROM 
	INVOICE
GROUP BY 
	BILLING_COUNTRY
ORDER BY 
	TOTAL_INVOICES DESC;
	

/* Q3: What are top 3 values of total invoice? */

SELECT 
	total
FROM
	invoice
ORDER BY
	total desc
LIMIT 3;


/* Q4: Which city has the best customers? We would like to throw a promotional Music Festival in the city we made the most money. 
Write a query that returns one city that has the highest sum of invoice totals. 
Return both the city name & sum of all invoice totals */


SELECT 
	billing_city,SUM(total) AS sum_invoice
FROM
	invoice
GROUP BY 
	billing_city
ORDER BY
	sum_invoice DESC
LIMIT 1;


/* Q5: Who is the best customer? The customer who has spent the most money will be declared the best customer. 
Write a query that returns the person who has spent the most money.*/

SELECT 
	c.first_name AS first_name,
	c.last_name AS last_name,
	sum(i.total) AS total_spent
FROM 
	customer as c
JOIN
	invoice as i
ON
	c.customer_id=i.customer_id
GROUP BY
	first_name,last_name
ORDER BY 
	total_spent DESC
LIMIT 1;


/* Question Set 2 - Moderate */

/* Q1: Write query to return the email, first name, last name, & Genre of all Rock Music listeners. 
Return your list ordered alphabetically by email starting with A. */


/*Method 1 */

SELECT DISTINCT email,first_name, last_name
FROM customer
JOIN invoice ON customer.customer_id = invoice.customer_id
JOIN invoice_line ON invoice.invoice_id = invoice_line.invoice_id
WHERE track_id IN(
	SELECT track_id FROM track
	JOIN genre ON track.genre_id = genre.genre_id
	WHERE genre.name LIKE 'Rock'
)
ORDER BY email;


/* Method 2 */

SELECT DISTINCT email AS Email,first_name AS FirstName, last_name AS LastName, genre.name AS Name
FROM customer
JOIN invoice ON invoice.customer_id = customer.customer_id
JOIN invoiceline ON invoiceline.invoice_id = invoice.invoice_id
JOIN track ON track.track_id = invoiceline.track_id
JOIN genre ON genre.genre_id = track.genre_id
WHERE genre.name LIKE 'Rock'
ORDER BY email;


/* Q2: Let's invite the artists who have written the most rock music in our dataset. 
Write a query that returns the Artist name and total track count of the top 10 rock bands. */


SELECT artist.artist_id,artist.name,COUNT(artist.artist_id) as num_of_songs
FROM artist
JOIN album
ON artist.artist_id=album.artist_id
JOIN track
ON album.album_id = track.album_id
WHERE track.track_id IN 
	(
		SELECT track.track_id
		FROM track
		JOIN genre
		ON track.genre_id=genre.genre_id
		WHERE genre.name like 'Rock'
)
GROUP BY  artist.artist_id
ORDER BY num_of_songs DESC
LIMIT 10

/* Q3: Return all the track names that have a song length longer than the average song length. 
Return the Name and Milliseconds for each track. Order by the song length with the longest songs listed first. */

SELECT track.name,track.milliseconds
FROM track
where track.milliseconds > (SELECT AVG(track.milliseconds) FROM track)
ORDER BY milliseconds DESC;

/* 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 */

/* Steps to Solve: First, find which artist has earned the most according to the InvoiceLines. Now use this artist to find 
which customer spent the most on this artist. For this query, you will need to use the Invoice, InvoiceLine, Track, Customer, 
Album, and Artist tables. Note, this one is tricky because the Total spent in the Invoice table might not be on a single product, 
so you need to use the InvoiceLine table to find out how many of each product was purchased, and then multiply this by the price
for each artist. */


WITH best_selling_artist AS (
    SELECT artist.artist_id AS artist_id, artist.name AS artist_name, SUM(invoice_line.unit_price*invoice_line.quantity) AS total_sales
    FROM invoice_line
	JOIN track ON track.track_id = invoice_line.track_id
	JOIN album ON album.album_id = track.album_id
	JOIN artist ON artist.artist_id = album.artist_id
	GROUP BY 1
	ORDER BY 3 DESC
	LIMIT 1
)

SELECT c.customer_id, c.first_name, c.last_name, bsa.artist_name, SUM(il.unit_price*il.quantity) AS amount_spent
FROM customer as c
JOIN invoice as i ON c.customer_id = i.customer_id
JOIN invoice_line AS il ON i.invoice_id = il.invoice_id
JOIN track AS t ON il.track_id = t.track_id
JOIN album AS al ON t.album_id = al.album_id
JOIN best_selling_artist as bsa ON al.artist_id = bsa.artist_id
GROUP BY 1,2,3,4
ORDER BY 5


/* Q2: We want to find out the most popular music Genre for each country. We determine the most popular genre as the genre 
with the highest amount of purchases. Write a query that returns each country along with the top Genre. For countries where 
the maximum number of purchases is shared return all Genres. */

/* Steps to Solve:  There are two parts in question- first most popular music genre and second need data at country level. */


WITH popular_genre AS (
	SELECT c.country, g.name,g.genre_id,COUNT(il.quantity) AS purchase,
	ROW_NUMBER() OVER (PARTITION BY c.country ORDER BY COUNT(il.quantity) DESC) AS row_no
	FROM customer c 
	JOIN invoice i ON c.customer_id = i.customer_id
	JOIN invoice_line il ON i.invoice_id= il.invoice_id
	JOIN track t ON il.track_id = t.track_id
	JOIN genre g ON t.genre_id = g.genre_id
	GROUP BY 1,2,3
	ORDER BY 1 ASC, 4 DESC
)
SELECT * FROM popular_genre WHERE row_no <= 1


/* Q3: Write a query that determines the customer that has spent the most on music for each country. 
Write a query that returns the country along with the top customer and how much they spent. 
For countries where the top amount spent is shared, provide all customers who spent this amount. */

/* Steps to Solve:  Similar to the above question. There are two parts in question- 
first find the most spent on music for each country and second filter the data for respective customers. */

/* Method 1: using CTE */

WITH Customter_with_country AS (
		SELECT customer.customer_id,first_name,last_name,billing_country,SUM(total) AS total_spending,
	    ROW_NUMBER() OVER(PARTITION BY billing_country ORDER BY SUM(total) DESC) AS RowNo 
		FROM invoice
		JOIN customer ON customer.customer_id = invoice.customer_id
		GROUP BY 1,2,3,4
		ORDER BY 4 ASC,5 DESC)
SELECT * FROM Customter_with_country WHERE RowNo <= 1