First window function — SUM() OVER () for percent-of-total. Practice computing each marketplace's share of total gross revenue using a window function instead of a subquery, and producing the concentration-of-revenue output the strategy team uses for diversification planning.
Strategy offsite next week. Pull all-time gross revenue and percent-of-total per marketplace. Three columns: marketplace_name, gross_revenue (rounded to 2 decimals), pct_of_total (rounded to 1 decimal). Use SUM(...) OVER () for the total denominator — cleaner than a scalar subquery. Sort gross_revenue descending, marketplace_name ascending. Concentration above 60% on any single marketplace is the strategic-risk question for diversification planning.
| Column | Type | Key |
|---|---|---|
| order_id | INT | PK |
| listing_id | INT | FK → dim_listings |
| marketplace_id | INT | FK → dim_marketplaces |
| order_ts | TEXT | |
| units | INT | |
| gross_amount | REAL | |
| refunded | INT |
| Column | Type | Key |
|---|---|---|
| marketplace_id | INT | PK |
| marketplace_name | TEXT | |
| marketplace_fee_pct | REAL | |
| fba_supported | INT |
Each hint you reveal reduces the XP you can earn. Try the query first.
JOIN dim_marketplaces on marketplace_id.
ROUND(SUM(o.gross_amount), 2) AS gross_revenue per marketplace.
Window aggregate over no PARTITION (i.e. OVER ()) gives the grand-total denominator inline — pct_of_total = revenue / grand_total * 100.
GROUP BY marketplace_name and order DESC by revenue so the dominant platform leads.