Embedded Files
Overview
Overview
In this project, I queried the Chinook Database, which holds information about a music store. I assisted the Chinook team with understanding the media in their store, their customers and employees, and their invoice information.
The schema for the Chinook Database is as follows:
Insight 1:
Insight 1:
Question: Which employee has the most customers?
SQL query:
SELECT
e.EmployeeId,
e.FirstName,
e.LastName,
COUNT(c.CustomerId) Number_of_customers
FROM Employee e
LEFT JOIN Customer c
ON e.EmployeeId = c.SupportRepId
GROUP BY 1
ORDER BY COUNT(c.CustomerId) DESC
Insight 2
Insight 2
Question: Which music genre is most popular among customers?
SQL query:
SELECT
g.Name Genre_name,
SUM(il.UnitPrice * il.Quantity) amount_spent
FROM Genre g
JOIN Track t
ON g.GenreId = t.GenreId
JOIN InvoiceLine il
ON t.TrackId = il.TrackId
GROUP BY 1
ORDER BY amount_spent DESC
Insight 3
Insight 3
Question: What level of total dollar amount has every artist earned?
SQL query:
SELECT
a.Name Artist_name,
g.Name Genre_name,
SUM(il.UnitPrice * il.Quantity) amount_earned,
CASE
WHEN SUM(il.UnitPrice * il.Quantity) > 50 THEN 'high'
WHEN SUM(il.UnitPrice * il.Quantity) > 20 THEN 'medium'
ELSE 'low'
END AS Level
FROM Artist a
JOIN Album al
ON a.ArtistId = al.ArtistId
JOIN Track t
ON t.AlbumId = al.AlbumId
JOIN Genre g
ON g.GenreId = t.GenreId
JOIN InvoiceLine il
ON il.TrackId = t.TrackId
JOIN Invoice i
ON i.InvoiceId = il.InvoiceId
GROUP BY 1
ORDER BY amount_earned DESC
Insight 4
Insight 4
Question: Who are the top 10 customers who spent most on U2?
SQL query:
SELECT
a.Name Artist_name,
c.CustomerId,
c.FirstName,
c.LastName,
SUM(il.UnitPrice * il.Quantity) amount_spent
FROM Artist a
JOIN Album al
ON a.ArtistId = al.ArtistId
JOIN Track t
ON t.AlbumId = al.AlbumId
JOIN InvoiceLine il
ON il.TrackId = t.TrackId
JOIN Invoice i
ON i.InvoiceId = il.InvoiceId
JOIN Customer c
ON c.CustomerId = i.CustomerId
WHERE a.Name = 'U2'
GROUP BY c.CustomerId
ORDER BY amount_spent DESC
LIMIT 10
Page updated
Google Sites
Report abuse