I'm working on a beastmode where I am trying to assign a label to individual orders based on their price_tier and their Product Name.

If an order's price_tier is equal to the MAX of the price_tier for a Product Name, I want it labeled "Sold". If an order's price tier is less than the max, I want it labeled "Discount". And if its 0, I want it labeled "Comp".

I attached a data sample of what my dataset looks like. It's the first table in the attached file. I ended up using the script below, but I'm running to an error with my "Discount" fields.

"Sold/Comp/Discount"

CASE WHEN price_tier = 0.00 then 'Comp'
WHEN price_tier = MAX(price_tier) OVER (PARTITION BY Product Name Consolidated) then 'Sold'
WHEN price_tier < MAX(price_tier) OVER (PARTITION BY Product Name Consolidated) AND price_tier > 0 then 'Discount'
- - else 'Discount'

end

In the file, the lower table represents the output I get when I try to count the "Sold"/"Comp"/"Discount" for each product. Because I have two different price_tier values under discount, they aren't aggregating like I want them too. Did I make an error in my beastmode? Or can someone help me understand why this is happening? I'm assuming it also might be the case that I just need to do this work in an ETL? But I'd like to know how it works.