Chinook-Music-Analysis

Chinook Music Analysis

Comprehensive SQL analysis of the Chinook Digital Media Store database.

📊 Project Overview

This project demonstrates advanced SQL querying to extract business insights from customer sales, inventory, and behavior data. It includes detailed analysis of top-selling products, customer segmentation, regional market trends, and customer churn patterns.

🎯 Key Analyses Included

1. Data Quality Assessment — Identifies null values, duplicates, and data integrity issues
2. Sales Performance — Top-selling tracks and artists in USA market
3. Customer Demographics — Age, gender, and location breakdown
4. Revenue Analytics — Total revenue and invoices by country, state, and city
5. Top Customers — Top 5 customers by revenue in each country
6. Customer Insights — Identifies best-selling track for each customer
7. Purchase Behavior Patterns — Frequency, average order value, and spending trends
8. Churn Analysis — Customer retention rates and churn metrics
9. Cross-Selling Opportunities — Genre, artist, and album pairing analysis
10. Schema Modifications — Demonstrates ALTER TABLE operations
11. Geographic Analysis — Average spend and purchase patterns by country

📁 Files Included

• SQL/queries.sql — 11 comprehensive SQL queries with detailed comments
• DOCS/Chinook_Mitali.docx — Full analysis documentation and findings
• PRESENTATION/Chinook_Analytics_Mitali_1.pptx — Visual presentation with charts and insights

🛠️ Technologies Used

• Database: MySQL
• SQL Features: CTEs (Common Table Expressions), Window Functions, Aggregations, Subqueries, Date Functions
• Analysis Type: Business Intelligence, Customer Analytics, Sales Analysis

📈 Key Insights

• 🎵 Analyzed 370+ tracks across 80+ artists
• 👥 60+ customer segments across different countries
• 💰 $523K+ in total revenue tracked
• 📍 Regional market variations and churn rates identified
• ⚠️ Customer risk profiles and high-churn segments documented
• 💡 Cross-selling patterns and product recommendations discovered

🗄️ Database Schema

Main Tables:

• Album, Artist, Customer, Employee
• Genre, Invoice, Playlist, Track
• InvoiceLine, PlaylistTrack, MediaType

Modified Schema:

• Added ReleaseYear column to Album table

🚀 How to Use

1. Download the Chinook Sample Database
2. Import the database into MySQL
3. Run queries from SQL/queries.sql sequentially
4. Review DOCS/ for detailed findings and analysis
5. Check PRESENTATION/ for visual insights and charts

📊 Query Highlights

• Complex CTEs for customer segmentation
• Window functions for ranking and partitioning
• Subqueries for nested data analysis
• Date functions for temporal analysis
• JOIN operations across multiple tables
• Aggregation and GROUP BY operations

11 Queries Covered

1. ✅ Data quality checks (nulls & duplicates)
2. ✅ Top-selling tracks & artists (USA market)
3. ✅ Customer demographic breakdown
4. ✅ Revenue by geography (country, state, city)
5. ✅ Top 5 customers per country
6. ✅ Best-selling track per customer
7. ✅ Purchase behavior patterns
8. ✅ Customer churn rate calculation
9. ✅ Cross-selling opportunities
10. ✅ Schema modification (ALTER TABLE)
11. ✅ Average spend by country

👤 Author

Mitali

📄 License

MIT License - Feel free to use this for learning and reference.

---

⭐ If you found this helpful, please star the repository!