Per-customer spend and each customer's share of revenue, excluding test and refunded orders
Table orders(customer_id, amount, status, is_test). Exclude test accounts (is_test = true) and refunded orders (status = 'refunded'). For each remaining customer return total spend and their share of overall revenue (percent of the filtered grand total). Shares must sum to 100%.
-- per customer: total spend and share of the filtered grand totalWrite the query.
Exclude test accounts and refunded orders in WHERE, sum spend per customer, then divide by the grand total: SUM(amount) / SUM(SUM(amount)) OVER () as the share — a window over the grouped rows. Apply the exclusions before both sums so shares add to 100%.
- ✗Dividing by a row count instead of the revenue total
- ✗Filtering refunded rows in HAVING rather than WHERE
- ✗Partitioning the grand-total window by customer_id
- →Why must the exclusions live in WHERE, not HAVING?
- →How would you also show a running cumulative share?
Filter first, group by customer, and compute the share against the grand total of the filtered revenue. The window SUM(SUM(amount)) OVER () sums the per-customer totals into one grand total available on every grouped row:
SELECT customer_id,
SUM(amount) AS spend,
ROUND(100.0 * SUM(amount) / SUM(SUM(amount)) OVER (), 2) AS pct_share
FROM orders
WHERE is_test = false
AND status <> 'refunded'
GROUP BY customer_id
ORDER BY spend DESC;The exclusions must sit in WHERE (they filter raw rows before grouping), not HAVING. An equivalent form divides by a scalar subquery:
... SUM(amount) / (SELECT SUM(amount) FROM orders
WHERE is_test = false AND status <> 'refunded') ...Because every customer's share uses the same filtered denominator, the percentages add up to 100%.
