I have a table in SQL that looks like this
Person brand brand_spend category category_spend 0 p1 b1 20 c1 100 1 p1 b2 50 c1 100 2 p2 b1 25 c2 40 3 p1 b3 30 c1 100 4 p1 b2 15 c2 70
I need to tag every customer based on percentage spend he has made at a brand based on total category spend where that brand is present.
So essentially I would want to tag Person
p1 for brand
b1' as percentage spend at 'b1 which should be calculated as 45/ 140
How this can be achieved. If I roll up on brand level to find total category spend then I think duplicates rows would add up.
I just want to find customer's Spend at a brand based on total spend at Brand for all categories where that brand is present.