Table of Contents
1. Database Schema & Architecture
Core Tables Structure
CREATE TABLE tracks (
track_id INT PRIMARY KEY,
name VARCHAR(200),
album_id INT,
media_type_id INT,
genre_id INT,
unit_price DECIMAL(10,2)
);Entity Relationships
2. Key SQL Queries
Top Selling Genres
SELECT g.name AS genre,
COUNT(*) AS total_tracks_sold,
SUM(il.unit_price * il.quantity) AS total_sales
FROM genre g
JOIN track t ON g.genre_id = t.genre_id
JOIN invoice_line il ON t.track_id = il.track_id
GROUP BY g.name
ORDER BY total_sales DESC
LIMIT 5;3. Customer Spending Analysis
| Customer Tier | % of Total Revenue | Average Order Value |
|---|---|---|
| Top 10% | 42% | $18.75 |
| Middle 50% | 35% | $9.20 |
4. Genre-Based Sales Trends
Monthly Growth Analysis
WITH monthly_sales AS (
SELECT EXTRACT(MONTH FROM invoice_date) AS month,
genre_id,
SUM(total) AS genre_total
FROM invoice
GROUP BY month, genre_id
)
SELECT month,
genre_id,
LAG(genre_total) OVER (PARTITION BY genre_id ORDER BY month)
AS previous_month,
genre_total AS current_month
FROM monthly_sales;5. Artist Performance Metrics
SELECT a.name AS artist,
COUNT(DISTINCT t.track_id) AS total_tracks,
SUM(il.quantity) AS total_sales,
RANK() OVER (ORDER BY SUM(il.quantity) DESC) AS sales_rank
FROM artist a
JOIN album al ON a.artist_id = al.artist_id
JOIN track t ON al.album_id = t.album_id
JOIN invoice_line il ON t.track_id = il.track_id
GROUP BY a.name
ORDER BY total_sales DESC
LIMIT 10;6. Advanced JOIN Operations
Customer Full Purchase History
SELECT c.first_name, c.last_name,
t.name AS track, i.invoice_date
FROM customer c
LEFT JOIN invoice i ON c.customer_id = i.customer_id
LEFT JOIN invoice_line il ON i.invoice_id = il.invoice_id
LEFT JOIN track t ON il.track_id = t.track_id
ORDER BY c.last_name, i.invoice_date DESC;7. Sales Forecasting Models
8. Actionable Business Insights
Inventory Optimization
- 20% of tracks account for 80% of sales
- Rock genre generates 35% of total revenue
Marketing Strategy
- Top 5% customers generate 30% of revenue
- Email campaign ROI: 58% conversion rate