From SQL to Clarity

In this project, I explored the Chinook database a sample dataset representing a digital music store using SQL to extract meaningful insights. By writing complex queries, I analyzed sales trends, customer behavior, and employee performance.

Chinook Database

VIEW PROJECT [↗]

Challanges

Complex Joins & Aggregations
Time-Based & Geographic Filtering
Calculating Duration Metrics
Performance Optimization

Step 1: Understanding Business Context & Database.

1-Explored the Chinook Schema:Studied table relationships (e.g., artists → albums → tracks → invoice_items).Identified key fields for analysis (e.g., Total in invoices, Milliseconds in tracks).
2-Defined Business Goals:Focused on answering questions about sales performance, customer preferences, and employee contributions.
3-Validated Data Quality:Checked for missing values (e.g., NULL genres or invoices).Ensured consistency in units (e.g., converting milliseconds to minutes).
4-Prioritized Key Tables:Mapped frequently used tables (customers, invoices, employees) to analytical objectives.

Step 2: Data Cleaning.

1. Handled Missing Values:     - Identified and addressed NULL entries.   - Used COALESCE or CASE statements to fill gaps logically.
2. Standardized Formats:     - Unified inconsistent data   - Converted Milliseconds to minutes for readability in genre analysis.
3. Resolved Duplicates:     - Detected and merged duplicate artist/album entries (e.g., "Iron Maiden" vs. "Iron Maiden (Live)").
4. Validated Relationships:     - Ensured foreign key integrity.   - Removed orphaned records (e.g., tracks with no associated invoices).

Step 3: Data Analysis & Manipulation.

Step 4: Data Analysis & ManipulationTransformed raw data into actionable insights using SQL.
1. Advanced Queries & Aggregations   - Wrote complex JOIN operations across 5+ tables (e.g., linking `artists → invoices for sales trends).     - Used GROUP BY with SUM(), COUNT(), and ROUND() to calculate metrics like:       - Total sales by artist/country.       - Average invoice value per customer.
2. Time & Behavioral Analysis   - Analyzed listening habits by converting Milliseconds to minutes per genre.     - Identified peak sales periods with date functions (e.g., STRFTIME).
3. *Comparative Metrics   - Ranked top-performing sales agents using subqueries and ROW_NUMBER().     - Compared genre popularity by country with CASE statements.
4. Dynamic Filtering     - Applied WHERE/HAVING clauses to isolate specific segments (e.g., "Iron Maiden listeners in the USA").     - Used WITH (CTEs) for modular, reusable query logic.

Step 5: Creating Dashboard & Communicating Findings.
Transformed SQL insights into compelling visual stories using Tableau and PowerPoint:
Interactive Tableau Dashboard
Built a dynamic dashboard with:Sales Performance: Top 10 artists by revenue (bar charts).Geographic Trends: Country-wise listening habits for bands like Iron Maiden (maps).
Agent Leaderboard: Sales reps ranked by total invoices (sorted tables).Added filters (date, country, genre) for self-service exploration.
PowerPoint Report for Stakeholders
Structured findings into a clear, action-driven narrative:
Key Insight: "Despite Rock’s popularity, Classical music has 20% higher customer retention."Recommendation: "Target Classical listeners with loyalty programs."Used minimalist design with:Annotated charts (e.g., arrows highlighting spikes in sales).Side-by-side comparisons (e.g., genre popularity vs. revenue).Presentation Delivery
Tailored messaging for different audiences:Executives: Focused on ROI and strategic priorities.Sales Team: Highlighted agent performance benchmarks.Impact: Enabled data-driven decisions, such as reallocating marketing budgets to high-retention genres.