-- Aisha Lund — Chinook Sales Analysis

-- Question 1
SELECT Customer.FirstName, Customer.LastName, Customer.Email FROM chinook.Customer WHERE Customer.Country = 'USA';

-- Question 2
SELECT Track.Name, Track.UnitPrice FROM chinook.Track ORDER BY Track.UnitPrice DESC, Track.Name ASC;

-- Question 3
SELECT Name, Name, Name FROM Track JOIN Genre ON GenreId = GenreId JOIN MediaType ON MediaTypeId = MediaTypeId ORDER BY Name ASC, Name ASC;

-- Question 4
SELECT Artist.Name, COUNT(Album.AlbumId) AS AlbumCount FROM chinook.Artist LEFT JOIN chinook.Album ON Artist.ArtistId = Album.ArtistId GROUP BY Artist.ArtistId, Artist.Name ORDER BY AlbumCount DESC, Artist.Name ASC;

-- Question 5
SELECT Genre.Name, COUNT(Track.TrackId) AS TrackCount FROM chinook.Genre JOIN chinook.Track ON Genre.GenreId = Track.GenreId GROUP BY Genre.GenreId, Genre.Name HAVING COUNT(Track.TrackId) > 50 ORDER BY TrackCount DESC;

-- Question 6
SELECT Employee.FirstName, Employee.LastName, Employee.Title, SUM(Invoice.Total) AS TotalRevenue FROM chinook.Employee JOIN chinook.Customer ON Employee.EmployeeId = Customer.SupportRepId JOIN chinook.Invoice ON Customer.CustomerId = Invoice.CustomerId WHERE Employee.Title = 'Sales Support Agent' GROUP BY Employee.EmployeeId, Employee.FirstName, Employee.LastName, Employee.Title ORDER BY TotalRevenue DESC;

-- Question 7
-- (not attempted)

-- Question 8
SELECT Invoice.BillingCountry, COUNT(Invoice.InvoiceId) AS InvoiceCount, ROUND(SUM(Invoice.Total), 2) AS TotalRevenue, CASE WHEN SUM(Invoice.Total) > 100 THEN 'High' WHEN SUM(Invoice.Total) >= 50 THEN 'Medium' ELSE 'Low' END AS RevenueTier FROM chinook.Invoice GROUP BY Invoice.BillingCountry ORDER BY TotalRevenue DESC;
