AVG aggregation with a JOIN to a denormalized dim attribute. Practice using SUM/COUNT vs AVG (both produce the same answer here; AVG is idiomatic for the question). Reinforces the latest-snapshot + active filter pattern.
Pricing committee meets Thursday — I need ARPU (average revenue per subscriber) by plan as of the latest snapshot ('2026-03-31'), active state only. Two columns: display_name (from dim_plans), arpu (rounded to 2 decimals). Sort arpu descending, display_name ascending. ARPU should match the plan's monthly_price for active subs since one sub on plan X pays plan X's price — sanity check the output against dim_plans.monthly_price.
| Column | Type | Key |
|---|---|---|
| snapshot_date | TEXT | PK |
| subscription_id | INT | PKFK → fact_subscriptions |
| customer_id | INT | FK → dim_customers |
| state | TEXT | |
| plan_id | INT | FK → dim_plans |
| mrr | REAL | |
| paused_until | TEXT |
| Column | Type | Key |
|---|---|---|
| plan_id | INT | PK |
| plan_family | TEXT | |
| cadence | TEXT | |
| monthly_price | REAL | |
| display_name | TEXT |
Each hint you reveal reduces the XP you can earn. Try the query first.
AVG(s.mrr) computes ARPU directly. It will equal the plan's monthly_price exactly because every active sub on a given plan has mrr = monthly_price.
INNER JOIN dim_plans on plan_id to pull the display_name.
Same snapshot filter pattern as before: snapshot_date = '2026-03-31' AND state = 'active'.
ROUND(AVG(s.mrr)::numeric, 2) for clean output.