Interview Question Bank · SUM(SUM()) OVER (PARTITION BY) · share of group
4. Merchant share of category spend
MediumFintechQuestion #103
Concept: SUM(SUM()) OVER (PARTITION BY) · share of groupshow
A share of a group needs two levels at once: each row's total and its group's total. GROUP BY gives the row level, and a window over the grouped rows, SUM(SUM(x)) OVER (PARTITION BY group), adds the group total to each of them. Leave out PARTITION BY and you divide by the grand total instead.
Example (different data):
SELECT region, store_id, SUM(sales) AS store_sales, SUM(SUM(sales)) OVER (PARTITION BY region) AS region_sales FROM daily_sales GROUP BY region, store_id;
Your task
Tallyfin's partnerships team is picking merchants for cashback deals, based on how dominant each merchant is within its own category.
Return category, merchant, merchant_spend (total amount spent at the merchant) and category_share (the merchant's percentage of its category's total spend, ROUND(..., 1)) for every merchant in fin_purchases. Sort by category, category_share descending, then merchant.
Starting Postgres in your browser…
Checking your access…