Explored a digital music store's transactional database using SQL to uncover which genres, countries, and customers drive the most revenue.
Type: #SQL #EDA | Tools: #SQLite #SQL | Period: 2009 – 2013
1. Rock dominates revenue across all genres — Rock tracks account for the largest share of total sales by a significant margin, making it the single most commercially important genre in the catalogue. Playlist and catalogue decisions should weight Rock heavily.
2. The USA is the top-grossing country by a wide margin — The US leads total revenue, order count, and average order value. International markets collectively contribute but no single country comes close to the US volume.
3. The top 20% of customers by lifetime spend are highly concentrated — A small subset of customers drive a disproportionate share of revenue. These high-value customers span multiple countries, suggesting retention efforts should be global, not US-centric.
The Chinook database is a sample dataset modelling a digital music store — covering artists, albums, tracks, invoices, and customers across multiple countries from 2009 to 2013. The goal of this project was to practise structured SQL analysis against a realistic relational schema: moving from exploratory queries that validate data quality, to focused analytical queries that answer specific business questions. The analysis identified Rock as the dominant revenue genre, the USA as the primary market, and surfaced the top 20% of customers by lifetime value using window functions.
| Field | Details |
|---|---|
| Source | Chinook sample SQLite database (public dataset) |
| Format | SQLite (.sqlite) |
| Size | ~1 MB · 5 core tables |
| Period | 2009 – 2013 |
| Key fields | CustomerId, BillingCountry, InvoiceDate, Total, GenreId, TrackId |
- The Chinook database is a sample/synthetic dataset — findings describe the dataset, not a real business.
- No customer demographics or acquisition channel data — customer segmentation is spending-only.
- Revenue figures cover 2009–2013; the monthly revenue analysis is scoped to 2013 only.
- No cost or margin data — all "revenue" figures are gross sales, not profit.
| File | Description |
|---|---|
queries/exploratory/discovery_chinook.sql |
Initial table inspection: row counts, date range, NULLs, country distribution |
queries/exploratory/investigate.sql |
Data quality investigation: reconciling a revenue discrepancy in 2013 figures |
queries/final/genre_revenue.sql |
Revenue and track sales ranked by music genre |
queries/final/revenue_by_country.sql |
Top 5 countries by total revenue, order count, and average order value |
queries/final/monthly_revenue.sql |
Month-by-month revenue and order count for 2013 |
queries/final/customer_top20pct.sql |
Top 20% of customers by lifetime spend, using NTILE window function |
data/raw/Chinook_Sqlite.sqlite |
Source database |
Author: jack2000-dev | Last updated: April 2026