/* Query 1 - Query used for the first problem */

WITH t1 AS (select AVG(Bytes)*0.000001 ave FROM Track)

SELECT t.Name track_name, g.name genre_name, t.Bytes*0.000001 sizeMB
FROM Track t
JOIN Genre g
ON t.GenreId=g.GenreId
WHERE genre_name='Jazz' AND sizeMB < (SELECT ave FROM t1)
ORDER BY sizeMB 


/* Query 2 - Query used for the second problem */


WITH t1 AS (SELECT i.BillingCountry country1, COUNT(t.Name) count_track
FROM Track t
JOIN InvoiceLine il
ON t.TrackId=il.TrackId
JOIN Invoice i
ON il.InvoiceId=i.InvoiceId
GROUP BY 1
ORDER BY 2 DESC 
LIMIT 1)

SELECT i.BillingCountry country, STRFTIME('%d', i.InvoiceDate) day, COUNT(t.Name) count_track1
FROM Track t
JOIN InvoiceLine il
ON t.TrackId=il.TrackId
JOIN Invoice i
ON il.InvoiceId=i.InvoiceId
GROUP BY 1,2
HAVING country=(SELECT t1.country1 FROM t1)
ORDER BY 1,2


/* Query 3 - Query used for the third problem */

WITH t1 AS (SELECT e.EmployeeId emp_id, c.FirstName, c.LastName, i.InvoiceId 
invoice_id, SUM(i.total) total
FROM Employee e
JOIN Customer c
ON e.EmployeeId=c.SupportRepId
JOIN Invoice i
ON c.CustomerId=i.CustomerId
GROUP BY 2,3,4
ORDER BY 1),

t2 AS (SELECT emp_id, SUM(total) 
FROM t1
GROUP BY 1
ORDER BY SUM(total) DESC 
LIMIT 1)

SELECT e.EmployeeId, i.BillingCountry
FROM Employee e
JOIN Customer c
ON e.EmployeeId=c.SupportRepId
JOIN Invoice i
ON c.CustomerId=i.CustomerId
WHERE e.EmployeeId=(SELECT t2.emp_id FROM t2)
ORDER BY 2

/* Query 4 - Query used for the fourth problem */

WITH t1 AS (SELECT a.name artist,COUNT(a.Name) count
FROM artist a
JOIN Album al
ON al.ArtistId=a.ArtistId
JOIN Track t
ON al.AlbumId=t.AlbumId
GROUP BY artist
ORDER BY count DESC
LIMIT 1),

t2 AS (SELECT a.Name, STRFTIME('%m',i.InvoiceDate) month
FROM Artist a
JOIN Album al
ON a.ArtistId=al.ArtistId
JOIN Track t
ON al.AlbumId=t.AlbumId
JOIN InvoiceLine il
ON t.TrackId=il.TrackId
JOIN Invoice i
ON il.InvoiceId=i.InvoiceId
WHERE a.Name=(SELECT t1.artist FROM t1)
ORDER BY month)

SELECT COUNT(*) count, CASE WHEN t2.month='01' THEN 'January'
WHEN month='02' THEN 'February'
WHEN month='03' THEN 'March'
WHEN month='04' THEN 'April'
WHEN month='05' THEN 'May'
WHEN month='06' THEN 'June'
WHEN month='07' THEN 'July'
WHEN month='08' THEN 'August'
WHEN month='09' THEN 'September'
WHEN month='10' THEN 'October'
WHEN month='11' THEN 'November'
ELSE 'December' END AS Month_Name
FROM t2
GROUP BY Month_Name
ORDER BY month