First two-fact aggregation. Practice computing two metrics from two different fact tables (subscription MRR from snapshots, one-time revenue from orders) and combining them into one output row. UNION ALL is the cleanest pattern; some analysts prefer scalar subqueries — both work.
Monthly close — give me 2026-03 blended revenue: subscription MRR (from latest snapshot in the month, active state only) plus one-time order revenue (orders dated in 2026-03). Four columns in one row: month ('2026-03'), mrr_revenue, one_time_revenue, total_revenue (sum of the two). Round all monetary columns to 2 decimals.
| 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 |
|---|---|---|
| order_id | INT | PK |
| customer_id | INT | FK → dim_customers |
| ordered_at | TEXT | |
| sku_id | INT | FK → dim_skus |
| qty | INT | |
| total_amount | REAL |
Each hint you reveal reduces the XP you can earn. Try the query first.
Two facts, one output row. Easiest pattern: scalar subqueries for each metric inside the SELECT.
MRR = SUM(mrr) from fact_subscription_snapshots WHERE snapshot_date = '2026-03-31' AND state = 'active'.
One-time = SUM(total_amount) from fact_one_time_orders WHERE ordered_at BETWEEN '2026-03-01' AND '2026-03-31'.
total_revenue = mrr_revenue + one_time_revenue. Do the addition once; don't recompute the subqueries.