Tools: SQL (SQLite / PostgreSQL / MySQL compatible) | Database: Chinook Sample Database
Author: Ranim | Focus: Business Insights through SQL
This project explores the Chinook digital music store database to uncover actionable business insights using SQL. The analysis simulates real-world tasks a Data Analyst might perform — from revenue tracking to customer behaviour and employee performance.
The Chinook database models a digital media store, including tables for artists, albums, tracks, invoices, customers, and employees.
| Table | Description |
|---|---|
Customer |
Customer info including country and support rep |
Invoice |
Sales transactions with totals and dates |
InvoiceLine |
Line items per invoice (track + quantity + price) |
Track |
Song details including genre, media type, composer |
Album |
Albums linked to artists |
Artist |
Artist names |
Genre |
Music genres |
Employee |
Staff info including title and reporting structure |
Playlist |
Curated playlists |
- What is the total revenue generated?
- Which countries generate the most sales?
- What are the monthly sales trends?
- Which invoice had the highest total?
- Which genres are the most purchased?
- What are the top 10 best-selling tracks?
- Which media type drives the most revenue?
- Who are the top 10 customers by spend?
- Which customers have made only one purchase?
- How much has each customer spent on average per invoice?
- Which sales support agent has generated the most revenue?
- How many customers does each employee manage?
chinook-sql-portfolio/
│
├── README.md ← You are here
├── chinook_analysis.sql ← All queries, organised by theme
└── schema_diagram.png ← (Optional) Entity Relationship Diagram
-
Download the Chinook database:
- SQLite version: chinook.db
- PostgreSQL / MySQL scripts also available at the same repo
-
Open with your preferred tool:
- DB Browser for SQLite (free, beginner-friendly)
- DBeaver (works with all databases)
- pgAdmin (for PostgreSQL)
-
Open
chinook_analysis.sqland run any query section
Here are some of the most interesting findings from the analysis:
- 🇺🇸 USA leads in total sales, followed by Canada and France
- 🎸 Rock is the dominant genre, accounting for the majority of track purchases
- 💡 Top 3 customers each spent over $40 USD in the store
- 📅 Sales show a relatively consistent monthly pattern, with minor seasonal peaks
- 🏆 Jane Peacock (Sales Support Agent) leads in total revenue among employees
This portfolio project was built as part of my transition into a Data Analyst role. It demonstrates:
- Writing clean, readable SQL with comments
- Thinking in business questions, not just queries
- Organising and presenting analysis professionally