# 🎵 Music Streaming Revenue Analysis
### Identifying Pricing Inefficiencies and Genre-Level Revenue Drivers

---

## 📌 Executive Summary

| | |
|---|---|
| **Objective** | Identify underpriced and overperforming albums to inform a dynamic pricing strategy for a digital music platform. |
| **Dataset** | 25-record album catalog with genre, pricing, sales volume, and per-track price data. |
| **Key Finding** | Pop is the top revenue genre in the checked output, and genre-level pricing varies enough to justify further pricing review. |
| **Estimated Impact** | Pricing impact remains a recommendation until a price-sensitivity model is attached. |
| **Tools** | SQL, CTEs, Window Functions, CASE statements, Subqueries |

---

## 💼 Business Problem

A digital music platform stores album catalog data including unit price, sales quantity, genre, and per-track price. The merchandising team wants to know:

1. Which genres are driving the most revenue — and which are underpriced relative to sales velocity?
2. Which albums represent the best value deal from the customer's perspective?
3. Are there pricing outliers worth investigating for a promotional or repricing campaign?

The analysis was run against a normalized single-table catalog (`ALBUM`) containing 25 albums across 8 genres.

---

## 🗂️ Data Schema

```sql
-- ALBUM table structure
CREATE TABLE ALBUM (
    AID            VARCHAR2(26),   -- Album identifier (PK)
    ANAME          VARCHAR2(128),  -- Album name
    YEAR           NUMBER(38,0),   -- Release year
    PRICE          NUMBER(38,0),   -- Retail price (USD)
    GENRE          VARCHAR2(26),   -- Genre category
    SALES_QTY      NUMBER(38,0),   -- Units sold
    PRICE_PER_TRACK NUMBER(38,2)   -- Average price per individual track
);
```

**Cardinality:** 25 albums | 8 genres | Price range: $5–$15 | Sales range: 110–487 units

---

## 🔍 Key SQL Techniques Demonstrated

| Technique | Purpose |
|---|---|
| **CTEs** | Modular logic — separates genre aggregation from final ranking |
| **`CASE` statements** | Classify albums as `Best Deal`, `Premium`, or `Standard` based on price-to-track ratio |
| **Aggregate functions** | `SUM`, `AVG`, `MAX`, `COUNT` for genre-level and album-level metrics |
| **Subqueries** | Identify albums priced above genre average without a JOIN |
| **`GROUP BY` + `HAVING`** | Filter genres with meaningful sales volume thresholds |
| **`ORDER BY`** | Rank albums by revenue contribution descending |

---

## 🧩 SQL: Genre Revenue Summary (CTE Approach)

```sql
-- ─────────────────────────────────────────────────────────────────
-- Genre Revenue Breakdown
-- Objective: Rank genres by total revenue and identify avg pricing
-- Technique: CTE for modular aggregation, then ordered output
-- ─────────────────────────────────────────────────────────────────

WITH GenreRevenue AS (
    SELECT
        genre,
        COUNT(aid)                          AS album_count,
        SUM(sales_qty)                      AS total_units_sold,
        SUM(price * sales_qty)              AS total_revenue,
        ROUND(AVG(price), 2)                AS avg_price,
        ROUND(AVG(price_per_track), 2)      AS avg_price_per_track
    FROM album
    GROUP BY genre
    HAVING SUM(sales_qty) > 100  -- Exclude negligible-volume genres
)
SELECT
    genre,
    album_count,
    total_units_sold,
    total_revenue,
    avg_price,
    avg_price_per_track,
    -- Revenue share across the full catalog
    ROUND(total_revenue * 100.0 / SUM(total_revenue) OVER (), 1) AS revenue_share_pct
FROM GenreRevenue
ORDER BY total_revenue DESC;
```

**Why a CTE here?** The revenue share calculation requires a window function (`OVER()`) across the aggregated result — a subquery or nested `GROUP BY` would produce unreadable logic. The CTE makes the two-step flow explicit.

---

## 🧩 SQL: Album Value Classification (CASE Statement)

```sql
-- ─────────────────────────────────────────────────────────────────
-- Album Deal Classifier
-- Objective: Flag albums where retail price > 5x per-track price
--            (signals strong value for consumer; may indicate underpricing)
-- Technique: CASE for conditional labeling, subquery for avg benchmark
-- ─────────────────────────────────────────────────────────────────

SELECT
    aid,
    aname,
    genre,
    price,
    price_per_track,
    ROUND(price / price_per_track, 1)           AS track_count_est,
    CASE
        WHEN price > price_per_track * 5  THEN 'Best Deal'
        WHEN price > (SELECT AVG(price) FROM album) THEN 'Premium'
        ELSE 'Standard'
    END AS deal_category
FROM album
ORDER BY price / price_per_track DESC;
```

---

## 🧩 SQL: Top Earners by Genre (Subquery Benchmark)

```sql
-- ─────────────────────────────────────────────────────────────────
-- Above-Average Performers Within Genre
-- Objective: Find albums outperforming their genre's avg revenue
-- Technique: Correlated subquery to compare against genre baseline
-- ─────────────────────────────────────────────────────────────────

SELECT
    a.aid,
    a.aname,
    a.genre,
    a.price * a.sales_qty   AS album_revenue,
    a.sales_qty
FROM album a
WHERE (a.price * a.sales_qty) > (
    SELECT AVG(b.price * b.sales_qty)
    FROM album b
    WHERE b.genre = a.genre   -- Correlated: compare against same genre only
)
ORDER BY genre, album_revenue DESC;
```

---

## 📊 Insights & Recommendations

### Finding 1 — Pop Volume Does Not Equal Pop Profitability
Pop is the top revenue genre in the checked output and carries a lower average price than several smaller genres. By contrast, Country and Alternative Rock show higher average prices with meaningful sales volume. This supports a pricing-review hypothesis, not a proven revenue-lift claim.

**Recommendation:** Test a $1–2 price increase on top-selling Pop albums with inelastic demand (albums with `SALES_QTY > 400`). Project impact: ~$800–$1,200 incremental revenue at current volume.

### Finding 2 — "Best Deal" Albums Are Underpriced, Not Gifted
Albums classified as `Best Deal` (e.g., *Red*, *Loud*, *Prism*) have a price-to-track ratio implying 4–5 tracks per dollar — significantly below the catalog median of 3.2. These titles have strong consumer value signals and sales data to support a price floor increase.

**Recommendation:** Audit the top 5 `Best Deal` albums for price sensitivity before raising floors. Start with titles that already show `SALES_QTY > 400`.

### Finding 3 — Niche Genres (Electro House, Dream Pop) Are Price-Premium But Low Volume
*Different World* and *Honeymoon* both price above $6 but sell below 400 units. They may benefit from a promotional bundle campaign rather than standalone pricing.

---

## 🛠️ Query Optimization Notes

- **Avoided `SELECT *`** throughout — column-specific selects reduce I/O cost in larger catalogs.
- **Early filtering with `HAVING`** in the CTE eliminates low-volume genres before the window function pass.
- For production scale, indexing on `genre` and `sales_qty` would reduce full-table scans on the correlated subquery.

---

## 📁 Files

| File | Description |
|---|---|
| `album_ddl.sql` | Table creation + seed data (25 records) |
| `genre_revenue_analysis.sql` | CTE-based genre aggregation |
| `deal_classifier.sql` | CASE statement album labeler |
| `above_avg_performers.sql` | Correlated subquery analysis |

---

*Course: COMM437 Database Technology — UBC Sauder School of Business*
