SQL_Project_queries.txt

/*Query1 - query used for first insight*/
SELECT g.Name Genre_type, COUNT(*) Quantity_Of_Sold_Tracks
FROM Track t
JOIN Genre g
ON t.GenreId = g.GenreId
JOIN InvoiceLine l
ON l.trackid = t.trackid
GROUP BY 1
ORDER BY 2 DESC
LIMIT 5


/*Query2 - query used for second  insight*/
SELECT i.billingcountry Country, COUNT(*) Quantity_Of_Sold_Tracks
FROM Track t
JOIN Genre g
ON t.GenreId = g.GenreId
JOIN InvoiceLine l
ON l.trackid = t.trackid
JOIN invoice i
ON l.invoiceid = i.invoiceid
WHERE g.name LIKE 'Metal'
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10


/*Query3 - query used for third insight*/
SELECT a.name Artist_name, COUNT(*) Apear_in_Playlist
FROM artist a
JOIN album m
ON m.ArtistId = a.ArtistId
JOIN Track t
ON t.AlbumId = m.AlbumId
JOIN PlaylistTrack p
ON p.TrackId = t.TrackId
JOIN Playlist s
ON p.PlaylistId = s.PlaylistId
GROUP BY 1
ORDER BY 2 DESC
LIMIT 5


/*Query4 - query used for fourth insight*/
SELECT C.CustomerId Customer_id, c.FirstName Name, c.lastname Surname, SUM(i.total) Total_spend_dollars, i.BillingCountry Country 
FROM Invoice i
JOIN customer c
ON i.customerid = c.CustomerId
GROUP BY 1
ORDER BY 4 DESC
LIMIT 5