🎵 Chinook Music Store Database Analysis (SQL Project)

📌 Project Overview

This project analyzes the Chinook music store database, a relational database that models a digital music store similar to iTunes.
The analysis answers real-world business questions using SQL to extract insights related to sales performance, customers, employees, and music content.

This project demonstrates:

• Strong understanding of relational database design
• Ability to write complex SQL queries
• Real-life business analytics use cases
• Portfolio-ready SQL project structure

---

🗄️ Database Description

The Chinook database represents a digital music store and contains multiple interconnected tables that support music sales operations.

Core Tables

• Artist – Stores music artists and bands
• Album – Albums released by artists
• Track – Individual songs available for purchase
• Genre – Music genres (Rock, Jazz, Pop, etc.)
• MediaType – Audio file formats

Sales & Business Tables

• Customer – Customers who purchase music
• Invoice – Purchase transactions
• InvoiceLine – Individual tracks purchased per invoice

Operational Tables

• Employee – Employees and sales support representatives
• Playlist – User-created playlists
• PlaylistTrack – Many-to-many relationship between playlists and tracks

---

🔗 Entity Relationships Overview

• One Artist can have many Albums
• One Album can contain many Tracks
• One Customer can make many Invoices
• One Invoice can include many InvoiceLines
• Each Customer is assigned to a Support Employee
• Employees report to other employees (self-referencing hierarchy)
• Tracks belong to Genres and Media Types
• Playlists and Tracks have a many-to-many relationship

---

🎯 Project Objectives

The main objectives of this project are to:

• Analyze sales and revenue distribution
• Identify high-value customers and top-performing employees
• Understand customer purchasing behavior
• Evaluate music popularity by genre, artist, and track
• Demonstrate SQL skills such as joins, aggregations, subqueries, and self-joins

---

📊 Selected Business Questions & SQL Queries

### 1️⃣ Countries With the Highest Sales
SELECT c.Country,
       SUM(i.Total) AS TotalSales
FROM Invoice i
JOIN Customer c ON i.CustomerId = c.CustomerId
GROUP BY c.Country
ORDER BY TotalSales DESC;

2️⃣ Top-Spending Customers (Customer Lifetime Value)
SELECT c.CustomerId,
       c.FirstName,
       c.LastName,
       SUM(i.Total) AS TotalSpent
FROM Customer c
JOIN Invoice i ON c.CustomerId = i.CustomerId
GROUP BY c.CustomerId, c.FirstName, c.LastName
ORDER BY TotalSpent DESC;

3️⃣ Employees Managing the Highest Revenue Customers
SELECT e.EmployeeId,
       e.FirstName,
       e.LastName,
       SUM(i.Total) AS ManagedRevenue
FROM Employee e
JOIN Customer c ON e.EmployeeId = c.SupportRepId
JOIN Invoice i ON c.CustomerId = i.CustomerId
GROUP BY e.EmployeeId, e.FirstName, e.LastName
ORDER BY ManagedRevenue DESC;

4️⃣ Most Popular Music Genres
SELECT g.Name AS Genre,
       SUM(il.Quantity) AS UnitsSold
FROM Genre g
JOIN Track t ON g.GenreId = t.GenreId
JOIN InvoiceLine il ON t.TrackId = il.TrackId
GROUP BY g.Name
ORDER BY UnitsSold DESC;

5️⃣ Highest Revenue-Generating Artists
SELECT ar.Name AS Artist,
       SUM(il.UnitPrice * il.Quantity) AS Revenue
FROM Artist ar
JOIN Album a ON ar.ArtistId = a.ArtistId
JOIN Track t ON a.AlbumId = t.AlbumId
JOIN InvoiceLine il ON t.TrackId = il.TrackId
GROUP BY ar.Name
ORDER BY Revenue DESC;

6️⃣ Employee–Manager Hierarchy
SELECT e.FirstName AS Employee,
       m.FirstName AS Manager
FROM Employee e
LEFT JOIN Employee m ON e.ReportsTo = m.EmployeeId;

7️⃣ Customers With No Purchases
SELECT c.*
FROM Customer c
LEFT JOIN Invoice i ON c.CustomerId = i.CustomerId
WHERE i.InvoiceId IS NULL;

8️⃣ Tracks Longer Than 5 Minutes
SELECT Name,
       Milliseconds
FROM Track
WHERE Milliseconds > 300000;

🧠 Key Insights

• A small number of countries contribute a large portion of total revenue
• Certain employees manage significantly higher-value customers
• Rock and similar genres dominate music sales
• A small group of artists generates most of the revenue
• Customer spending behavior varies greatly across regions
• Employee hierarchy is effectively modeled using self-referencing relationships

🛠️ Tools & Technologies Used

• Database: MySQL
• Query Language: SQL
• Concepts Applied:
  • INNER JOIN, LEFT JOIN, SELF JOIN
  • Aggregation functions (SUM, AVG, COUNT)
  • Subqueries
  • Grouping and sorting
  • Many-to-many relationships -Hierarchical data modeling

🏁 Conclusion

This project demonstrates practical SQL skills applied to a real-world business scenario. It highlights the ability to analyze structured data, extract insights, and communicate results effectively which are key skills for data analyst and business intelligence roles.