First look at fact_orders, the hub of the three-sided marketplace. Practice a single-table GROUP BY + COUNT to surface order volume per merchant. The foundational shape every quick-commerce analyst writes before any deeper question.
Welcome to the team. Before anything fancy, pull me a count of orders per merchant so I can see who our heavy hitters are. fact_orders has one row per order with a merchant_id on it. Two columns: merchant_id, order_count. ORDER BY order_count DESC, merchant_id ASC so reruns are stable. Just the raw counts, nothing weighted yet.
| 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 |
Each hint you reveal reduces the XP you can earn. Try the query first.
Everything you need is in fact_orders, no joins yet.
GROUP BY merchant_id and COUNT(*) the rows in each group.
Give the count an alias so you can sort by it, then add merchant_id as a tiebreaker so rows with equal counts always come back in the same order.