Question

The Chinook team would like to throw a promotional Music Festival for their top 10 customers who have spent the most in a single invoice. Write a query that returns the first name, last name, and invoice total for the top 10 invoices ordered by invoice total descending.

MediaType Artist Album Track Artistld Albumld MediaTypeld Trackld Title Name Name Name Artistld Albumld MediaTypeld Genreld Composer Genre Playlist Track Playlist Milliseconds Genreld Playlistld Playlistld Name Bytes Name Trackld UnitPrice InvoiceLine Employee InvoiceLineld Employeeld Invoiceld LastName Customer Trackld Customerld UnitPrice FirstName Title FirstName Quantity ReportsTo LastName Birth Date Company Invoice Address Invoiceld HireDate Address Customerld City City State InvoiceDate BillingAddress State Country BillingCity Country PostalCode BillingState PostalCode Phone Phone BillingCountry BillingPostalCode Fax Email Fax Email Total SupportRepld

Answer 1

Please find the SQL query below.

SELECT c.FirstName, c.LastName, sum(il.UnitPrice * il.Quantity) as InvoiceTotal FROM Customer c JOIN Invoice i ON c.CustomerId = i.CustomerId JOIN InvoiceLine il ON i.InvoiceId = il.InvoiceId GROUP BY i.CustomerId ORDER BY InvoiceTotal DESC LIMIT 10;