This project performs a comprehensive SQL-based analysis of a digital music store's database.
The goal is to answer real-world business questions related to sales performance, customer behavior,
employee hierarchy, and music genre trends — helping the business make smarter, data-driven decisions.
The database consists of multiple related tables including employees, customers, invoices, tracks, albums, artists, and genres.
| Tool | Purpose |
|---|---|
| PostgreSQL | Relational database management system |
| pgAdmin 4 | GUI tool for writing & executing SQL queries |
| SQL | Query language used for all analysis |
music_store_analysis/
│
├── README.md ← Project documentation
├── music_store_database.sql ← Complete database dump
├── music_store_analysis.sql ← All SQL queries with solutions
├── schema_diagram.png ← Entity Relationship Diagram
└── questions.pdf ← Business questions document
| # | Question |
|---|---|
| 1 | Who is the senior most employee based on job title? |
| 2 | Which countries have the most invoices? |
| 3 | What are the top 3 values of total invoice? |
| 4 | Which city has the best customers? (Highest sum of invoice totals) |
| 5 | Who is the best customer? (Customer who has spent the most money) |
| # | Question |
|---|---|
| 1 | Return the email, first name, last name & genre of all Rock Music listeners (ordered alphabetically by email) |
| 2 | Which are the top 10 rock bands by total track count? |
| 3 | Return all track names longer than the average song length (ordered by length descending) |
| # | Question |
|---|---|
| 1 | How much amount has each customer spent on each artist? |
| 2 | What is the most popular music genre for each country? |
| 3 | Who is the top spending customer for each country? |
-
👔 Senior Most Employee: Madan Mohan — Senior General Manager
-
🌍 Top Countries by Invoice Count:
| Country | Invoices |
|---|---|
| USA | 131 |
| Canada | 76 |
| Brazil | 61 |
-
💰 Top 3 Invoice Values: $23.76 · $19.80 · $19.80
-
🏙️ Best City for Music Festival:
| City | Total Invoice Sum |
|---|---|
| Prague 🏆 | $273.24 |
| Mountain View | $169.29 |
| London | $166.32 |
💡 Recommendation: Host the promotional Music Festival in Prague — it generated the highest revenue!
- 🏆 Best Customer: R Madhav — Total Spent: $144.54
-
🎸 Total Rock Music Listeners Found: 49 customers
-
🎤 Top 10 Rock Bands by Track Count:
| Rank | Artist | 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 |
- ⏱️ Tracks Longer Than Average Song Length: 494 tracks
- 🎵 Top Customers by Artist Spending:
| Customer | Favourite Artist | Total Spent |
|---|---|---|
| Hugh O'Reilly | Queen | $27.72 |
| Niklas Schröder | Queen | $18.81 |
| François Tremblay | Queen | $17.82 |
💡 Queen is the top-earning artist across high-value customers!
- 🌎 Most Popular Genre by Country (Sample):
| Country | Top Genre |
|---|---|
| Argentina | Alternative & Punk |
| Australia | Rock |
| Austria | Rock |
- 👑 Top Spending Customer Per Country (Sample):
| Customer | Country | Total Spent |
|---|---|---|
| Diego Gutiérrez | Argentina | $39.60 |
| Mark Taylor | Australia | $81.18 |
| Astrid Gruber | Austria | $69.30 |
- Install PostgreSQL on your machine
- Open pgAdmin 4 or terminal
- Create a new database:
CREATE DATABASE music_store;
- Import the database:
psql -U postgres -d music_store -f music_store_database.sql
- Open
music_store_analysis.sqlin pgAdmin Query Tool - Run queries one by one to see results ✅
- 🎪 Host the Music Festival in Prague — highest revenue city at $273.24
- 🎸 Focus marketing on Rock genre — dominant across multiple countries
- 👑 Reward R Madhav — the store's best customer at $144.54 total spend
- 🎵 Partner with Queen — top-earning artist across premium customers
Tarun Kumar
