Digital Music Store — SQL Data Analysis

A structured SQL analysis of a digital music store database (Chinook-style), exploring customer behavior, sales performance, and music trends across 11 relational tables.

---

Database Schema

The database contains 11 tables: employee · customer · invoice · invoice_line · track · album · artist · genre · playlist · playlist_track · media_type

---

Tools Used

• MySQL — querying and analysis
• Python (Pandas) — data verification and cross-checking results
• DB schema — designed and visualized using draw.io / MySQL Workbench

---

Project Structure

digital-music-store-sql-analysis/
│
├── README.md
├── Digital_Music_Database.sql     ← database + table creation script
├── queries/
│   ├── easy_questions.sql         ← Question Set 1 solutions
│   └── moderate_questions.sql     ← Question Set 2 solutions
├── data/
│   ├── album.csv
│   ├── artist.csv
│   ├── employee.csv
│   ├── genre.csv
│   ├── invoice.csv
│   ├── playlist.csv
│   └── track.csv
└── schema/
    ├── schema_diagram.png
    └── MusicDatabaseSchema.png

---

Question Set 1 — Easy

Q1. Who is the senior most employee based on job title?

SELECT first_name, last_name, title, levels
FROM employee
ORDER BY levels DESC
LIMIT 1;

Result:

first_name	last_name	title	levels
Mohan	Madan	Senior General Manager	L7

Insight: The employee hierarchy uses a levels column (L1–L7). Mohan Madan sits at the top as Senior General Manager.

---

Q2. Which countries have the most invoices?

SELECT billing_country, COUNT(*) AS invoice_count
FROM invoice
GROUP BY billing_country
ORDER BY invoice_count DESC;

Result (Top 5):

country	invoice_count
USA	131
Canada	76
Brazil	61
France	50
Germany	41

Insight: The USA dominates with nearly double the invoices of the second-largest market, Canada. North America and Europe account for the bulk of sales.

---

Q3. What are the top 3 values of total invoice?

SELECT invoice_id, total
FROM invoice
ORDER BY total DESC
LIMIT 3;

Result:

invoice_id	total
183	$23.76
31	$19.80
92	$19.80

---

Q4. Which city has the best customers?

We want to throw a promotional Music Festival in the city where we made the most money.

SELECT billing_city, ROUND(SUM(total), 2) AS total_revenue
FROM invoice
GROUP BY billing_city
ORDER BY total_revenue DESC
LIMIT 1;

Result:

city	total_revenue
Prague	$273.24

Insight: Prague leads all cities in total revenue, making it the ideal location for a promotional Music Festival. Mountain View ($169.29) and London ($166.32) follow.

---

Q5. Who is the best customer?

The customer who has spent the most money.

SELECT c.customer_id, c.first_name, c.last_name,
       ROUND(SUM(i.total), 2) AS total_spent
FROM customer c
JOIN invoice i ON c.customer_id = i.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY total_spent DESC
LIMIT 1;

Result:

customer_id	first_name	last_name	total_spent
5	R	Madhav	$144.54

Insight: Customer ID 5 is the highest-value customer, spending $144.54 — about 12% more than the second-highest spender.

---

Question Set 2 — Moderate

Q6. Find all Rock music listeners

Return email, first name, last name, and genre of all Rock music listeners — ordered alphabetically by email.

SELECT DISTINCT c.email, c.first_name, c.last_name, g.name AS genre
FROM customer c
JOIN invoice i        ON c.customer_id = i.customer_id
JOIN invoice_line il  ON i.invoice_id  = il.invoice_id
JOIN track t          ON il.track_id   = t.track_id
JOIN genre g          ON t.genre_id    = g.genre_id
WHERE g.name = 'Rock'
ORDER BY c.email ASC;

Insight: This query chains 5 tables to trace a customer's listening behavior from purchase history back to genre. DISTINCT ensures customers who bought multiple Rock tracks appear only once.

---

Q7. Top 10 rock artists by track count

Invite the artists who have written the most Rock music.

SELECT ar.name, COUNT(t.track_id) AS track_count
FROM track t
JOIN album  al ON t.album_id  = al.album_id
JOIN artist ar ON al.artist_id = ar.artist_id
JOIN genre  g  ON t.genre_id  = g.genre_id
WHERE g.name = 'Rock'
GROUP BY ar.name
ORDER BY track_count DESC
LIMIT 10;

Result:

rank	artist	rock_tracks
1	Led Zeppelin	114
2	U2	112
3	Deep Purple	92
4	Iron Maiden	81
5	Pearl Jam	54
6	Van Halen	52
7	Queen	45
8	The Rolling Stones	41
9	Creedence Clearwater Revival	40
10	Kiss	35

Insight: Led Zeppelin and U2 are nearly tied at the top. Classic rock acts dominate — all top 10 are legacy bands, suggesting the store's catalog skews toward classic rock.

---

Q8. Tracks longer than average song length

Return name and milliseconds for tracks above average duration, longest first.

SELECT name, milliseconds
FROM track
WHERE milliseconds > (SELECT AVG(milliseconds) FROM track)
ORDER BY milliseconds DESC;

Key Stats:

metric	value
Average track duration	393,599 ms (~6.6 min)
Tracks above average	494 out of 3,503

Top 5 longest tracks:

track name	duration
Occupation / Precipice	88.1 min
Through a Looking Glass	84.8 min
Greetings from Earth, Pt. 1	49.3 min
The Man With Nine Lives	49.3 min
Battlestar Galactica, Pt. 2	49.3 min

Insight: The top longest tracks are TV episode-length recordings (likely from Battlestar Galactica). Using a subquery for the average keeps this clean and efficient — no hardcoded values.

---

Key Findings

#	Finding
1	The USA is the largest market by far — 131 invoices vs 76 for Canada
2	Prague is the highest-revenue city despite not being in the top invoice-count countries
3	Led Zeppelin has the most Rock tracks (114), narrowly ahead of U2 (112)
4	Only 494 of 3,503 tracks (14%) are longer than the average duration
5	The top 3 longest tracks are all TV show episode recordings, skewing the average upward

---

What I Learned

• How to use multi-table JOINs to answer real business questions from normalized data
• Using subqueries inside WHERE clauses (e.g. WHERE ms > (SELECT AVG(ms) FROM track))
• The difference between COUNT(*) and COUNT(column) when dealing with NULLs
• How GROUP BY + ORDER BY + LIMIT work together for ranking queries
• Importance of DISTINCT when joining one-to-many relationships to avoid duplicate rows

---

How to Run

1. Clone this repo
2. Run Digital_Music_Database.sql in MySQL Workbench to create the schema
3. Import the CSVs from the /data folder into the respective tables
4. Run queries from /queries/easy_questions.sql and /queries/moderate_questions.sql

---

Connect

Made by Rudra Mulay · LinkedIn