Queries.txt

/*Query 1 to answer the question on "What is the most Media Type do we have?"*/
SELECT m.Name, COUNT(t.MediaTypeId) AS MediaTypeCount
FROM MediaType m
JOIN Track t
ON m.MediaTypeId = t.MediaTypeId
GROUP BY m.Name
ORDER BY 2 DESC;

/*Query 2 to answer the question on "Who are our best 3 Customers?" */
SELECT (c.FirstName || " " || c.LastName) AS CustomerName,
	SUM(i.total) AS TotalSales
FROM Customer c
JOIN Invoice i
ON c.CustomerId = i.CustomerId
GROUP BY 1
ORDER BY 2 DESC
LIMIT 3;

/* Query 3 to answer the question on "Who is our best performing Support Rep?"*/
SELECT (e.FirstName || " " || e.LastName) AS RepName,
		COUNT(c.SupportRepId) AS CustomersSupported
FROM Employee e
JOIN Customer c
ON e.EmployeeId = c.SupportRepId
GROUP BY 1
ORDER BY 2;

/* Query 4 to answer the question on "What are the Top 10 selling Tracks?"*/
SELECT t. Name, SUM(i.Quantity) AS QuantityOrdered
FROM Track t
JOIN InvoiceLine i
ON t.TrackId = i.TrackId
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10;