# SQL Query Results

Run against `data/returns.db` (SQLite) — reproducible via `run_queries.py`.

### Q1_top_return_skus

```sql
-- Q1: Which SKUs drive the most returns, and what share of total volume do they represent?
SELECT
    sku,
    COUNT(*) AS return_count,
    ROUND(100.0 * COUNT(*) / (SELECT COUNT(*) FROM returns), 1) AS pct_of_total_returns,
    ROUND(SUM(refund_value_gbp), 2) AS total_refund_value_gbp
FROM returns
GROUP BY sku
ORDER BY return_count DESC
LIMIT 6;
```

**Result:**

| sku | return_count | pct_of_total_returns | total_refund_value_gbp |
|---|---|---|---|
| CM-4001 Foundation Shade 3 | 174 | 18.3 | 8016.62 |
| SK-1001 Retinol Serum | 156 | 16.4 | 7575.08 |
| EL-6001 Hair Dryer 2200W | 144 | 15.2 | 6928.99 |
| FR-3002 EDP 100ml | 45 | 4.7 | 2282.25 |
| FR-3001 EDT 50ml | 44 | 4.6 | 2112.59 |
| HC-2003 Dry Shampoo | 37 | 3.9 | 1978.54 |

### Q2_processing_time_by_channel

```sql
-- Q2: How does average processing time compare between online and in-store returns?
SELECT
    channel,
    COUNT(*) AS return_count,
    ROUND(AVG(days_to_process), 2) AS avg_days_to_process,
    MIN(days_to_process) AS fastest_days,
    MAX(days_to_process) AS slowest_days
FROM returns
GROUP BY channel
ORDER BY avg_days_to_process DESC;
```

**Result:**

| channel | return_count | avg_days_to_process | fastest_days | slowest_days |
|---|---|---|---|---|
| Online | 567 | 6.17 | 1 | 12 |
| In-store | 383 | 1.76 | 1 | 4 |

### Q3_top3_sku_reason_breakdown

```sql
-- Q3: For the top 3 highest-return SKUs, what are the leading return reasons?
WITH top_skus AS (
    SELECT sku FROM returns
    GROUP BY sku
    ORDER BY COUNT(*) DESC
    LIMIT 3
)
SELECT
    r.sku,
    r.return_reason,
    COUNT(*) AS reason_count
FROM returns r
JOIN top_skus t ON r.sku = t.sku
GROUP BY r.sku, r.return_reason
ORDER BY r.sku, reason_count DESC;
```

**Result:**

| sku | return_reason | reason_count |
|---|---|---|
| CM-4001 Foundation Shade 3 | Changed mind | 52 |
| CM-4001 Foundation Shade 3 | Wrong shade match | 51 |
| CM-4001 Foundation Shade 3 | Ordered wrong size/shade | 47 |
| CM-4001 Foundation Shade 3 | Faulty item | 11 |
| CM-4001 Foundation Shade 3 | Arrived damaged | 9 |
| CM-4001 Foundation Shade 3 | Not as described | 2 |
| CM-4001 Foundation Shade 3 | Duplicate order | 2 |
| EL-6001 Hair Dryer 2200W | Doesn't match description | 54 |
| EL-6001 Hair Dryer 2200W | Arrived damaged | 41 |
| EL-6001 Hair Dryer 2200W | Faulty item | 35 |
| EL-6001 Hair Dryer 2200W | Not as described | 6 |
| EL-6001 Hair Dryer 2200W | Changed mind | 4 |
| EL-6001 Hair Dryer 2200W | Ordered wrong size/shade | 3 |
| EL-6001 Hair Dryer 2200W | Duplicate order | 1 |
| SK-1001 Retinol Serum | Skin reaction / allergy | 54 |
| SK-1001 Retinol Serum | Changed mind | 46 |
| SK-1001 Retinol Serum | Seal broken on arrival | 36 |
| SK-1001 Retinol Serum | Faulty item | 5 |
| SK-1001 Retinol Serum | Duplicate order | 5 |
| SK-1001 Retinol Serum | Arrived damaged | 5 |
| SK-1001 Retinol Serum | Not as described | 4 |
| SK-1001 Retinol Serum | Ordered wrong size/shade | 1 |

### Q4_monthly_return_trend

```sql
-- Q4: Is return volume trending up, down, or seasonal across the year?
SELECT
    strftime('%Y-%m', return_date) AS return_month,
    COUNT(*) AS return_count,
    ROUND(SUM(refund_value_gbp), 2) AS refund_value_gbp
FROM returns
GROUP BY return_month
ORDER BY return_month;
```

**Result:**

| return_month | return_count | refund_value_gbp |
|---|---|---|
| 2025-10 | 85 | 4263.19 |
| 2025-11 | 81 | 3773.58 |
| 2025-12 | 88 | 4187.4 |
| 2026-01 | 77 | 3702.49 |
| 2026-02 | 63 | 3214.93 |
| 2026-03 | 92 | 4637.69 |
| 2026-04 | 62 | 2749.8 |
| 2026-05 | 92 | 4620.43 |
| 2026-06 | 75 | 3643.57 |
| 2026-07 | 87 | 4040.79 |
| 2026-08 | 76 | 3622.08 |
| 2026-09 | 72 | 3422.41 |

### Q5_region_channel_breakdown

```sql
-- Q5: Which regions have the highest online-return processing burden?
SELECT
    region,
    channel,
    COUNT(*) AS return_count,
    ROUND(AVG(days_to_process), 2) AS avg_days_to_process
FROM returns
WHERE channel = 'Online'
GROUP BY region
ORDER BY return_count DESC;
```

**Result:**

| region | channel | return_count | avg_days_to_process |
|---|---|---|---|
| Wales | Online | 110 | 6.25 |
| Midlands | Online | 103 | 6.17 |
| Scotland | Online | 96 | 5.96 |
| London | Online | 90 | 6.22 |
| South East | Online | 87 | 6.39 |
| North West | Online | 81 | 6.0 |

### Q6_high_value_refunds

```sql
-- Q6: What share of total refund value comes from top-3 SKUs vs everything else? (cost concentration check)
SELECT
    CASE
        WHEN sku IN (SELECT sku FROM returns GROUP BY sku ORDER BY COUNT(*) DESC LIMIT 3)
        THEN 'Top 3 problem SKUs'
        ELSE 'All other SKUs'
    END AS sku_group,
    COUNT(*) AS return_count,
    ROUND(SUM(refund_value_gbp), 2) AS total_refund_value_gbp,
    ROUND(100.0 * SUM(refund_value_gbp) / (SELECT SUM(refund_value_gbp) FROM returns), 1) AS pct_of_total_refund_value
FROM returns
GROUP BY sku_group;
```

**Result:**

| sku_group | return_count | total_refund_value_gbp | pct_of_total_refund_value |
|---|---|---|---|
| All other SKUs | 476 | 23357.67 | 50.9 |
| Top 3 problem SKUs | 474 | 22520.69 | 49.1 |