Join a fact to a dimension and average a metric across an acquisition cohort. First INNER JOIN of the path, tying order behavior back to how the customer was acquired.
I want to know whether the channel we acquire a customer through predicts how big their baskets are. Join fact_orders to dim_customers and average units per delivered order, grouped by acquisition_channel. Only delivered orders. Columns: acquisition_channel, avg_basket_size (2 decimals), order_count. ORDER BY avg_basket_size DESC, acquisition_channel ASC.
| Column | Type | Key |
|---|---|---|
| order_id | INT | PK |
| customer_id | INT | FK → dim_customers |
| merchant_id | INT | FK → dim_merchants |
| shopper_id | INT | FK → dim_shoppers |
| dispatch_id | INT | FK → fact_shopper_dispatches |
| order_ts | TEXT | |
| order_status | TEXT | |
| units | INT | |
| item_subtotal | REAL | |
| delivery_fee | REAL | |
| service_fee | REAL | |
| surge_fee | REAL | |
| surge_multiplier | REAL | |
| tip_amount | REAL | |
| customer_total | REAL | |
| shopper_take | REAL | |
| platform_take | REAL | |
| merchant_payout | REAL |
| Column | Type | Key |
|---|---|---|
| customer_id | INT | PK |
| TEXT | ||
| first_name | TEXT | |
| last_name | TEXT | |
| city | TEXT | |
| state | TEXT | |
| signup_date | TEXT | |
| acquisition_channel | TEXT |
Each hint you reveal reduces the XP you can earn. Try the query first.
INNER JOIN fact_orders to dim_customers on customer_id to reach acquisition_channel.
Restrict to order_status = 'delivered' so cancellations don't drag the average.
GROUP BY acquisition_channel, ROUND(AVG(units), 2), and COUNT(*) for order_count.