JOIN customer dimension with purchases for geographic analysis
We're budgeting for regional campaigns. Can you show me the top 5 states by total purchase revenue? I need the state, total revenue, and number of purchases. Positive purchases only.
Each hint you reveal reduces the XP you can earn. Try the query first.
JOIN `dim_customers` to `fact_purchases` on `customer_id` to connect state info with purchase data
Filter `WHERE p.amount > 0`, GROUP BY `c.state`, then `SUM(p.amount)` for revenue and `COUNT(*)` for purchases
Use `ROUND(SUM(p.amount)::numeric, 2) AS total_revenue` and `ORDER BY total_revenue DESC LIMIT 5`