Total Sales Per Customer
The graph shows the total sales of each customer.
What are the top 5 artists with the most track sales?
According to the graph, the artist with the most track sales is Iron Maiden with a total of 140 tracks sold followed by U2 with a total of 107 tracks sold. Metallica comes in third with a total of 91 tracks sold, followed by Led Zepplin (87 total tracks sold) and then Os Paralamas Do Sucesso (45 total tracks sold).
<visualization>
Which genre had the most track sales in 2012?
According to the graph, the genre with the most track sales in 2012 was “Rock”.
<visualization>
Which Support Rep. has had the highest sales (in USD)?
According to the graph, Customer Support Representative, Jane Peacock, had the highest sales at $833.04.
<visualization>
SQL Queries
/*Query 1 - query used for first insight */
SELECT c.customerid Customer_id, c.Firstname Firstname, c.Lastname, i.Total Total_Spent_Per_Customer
FROM Customer c
JOIN Invoice i
ON c.CustomerId=i.InvoiceId
GROUP BY c.customerid
ORDER BY i.Total DESC;
/*Query 2 - query used for second insight*/
SELECT a.name Artist_Name, SUM(i.quantity) Tracks_Sold
FROM Artist a
JOIN album al
ON a.artistid=al.ArtistId
JOIN Track t
ON al.albumid=t.AlbumId
JOIN InvoiceLine i
ON t.trackid=i.TrackId
GROUP BY a.Name
ORDER BY Tracks_Sold DESC
LIMIT 5;
/*Query 3 - query used for third insight*/
SELECT strftime('%Y', i.invoicedate) Invoice_Year, g.name Genre_Name, SUM(il.quantity) Quantity_Sold
FROM Track t
JOIN Genre g
ON t.genreid=g.GenreId
JOIN Invoiceline il
ON t.trackid=il.TrackId
JOIN Invoice i
ON il.invoiceid=i.InvoiceId
WHERE Invoice_Year='2012'
GROUP BY Genre_Name
ORDER BY Quantity_sold DESC
LIMIT 5;
/*Query 4 - query used for fourth insight*/
SELECT e.Employeeid, e.FirstName, e.LastName, SUM(i.total) Total_Sales
FROM Employee e
JOIN Customer c
ON e.EmployeeId=c.SupportRepId
JOIN Invoice i
ON c.CustomerId=i.CustomerId
GROUP BY e.EmployeeId
ORDER BY Total_Sales DESC;