Selecting a value based on condition from other two columns

Question

I have the following table:

| beverage | country | units_a_sold | units_b_sold |
| -------- | ------- | ------------ | ------------ |
| fanta    | US      | 184          |          209 |
| fanta    | DE      | 0            |          400 |
| fanta    | UK      | 309          |          107 |
| pepsi    | US      | 0            |          890 |
| pepsi    | DE      | 0            |          345 |
| pepsi    | UK      | 0            |          193 |

I would like to get the country where the max units were sold for each beverage type. Giving priority to a. If there are no units sold for a, we take the country from the max units sold for b.

So for Fanta we would have UK, and for Pepsi US

I have something like this:

WITH temp AS (
    SELECT *
        , RANK() OVER (PARTITION BY app_id ORDER BY units_a_sold DESC) rnk_a
        , RANK() OVER (PARTITION BY app_id ORDER BY units_B_sold DESC) rnk_b
    FROM table
)

SELECT DISTINCT beverage
    , CASE 
        WHEN units_a_sold > 0 THEN (...)

FROM temp
WHERE rnk = 1;

Any ideas?

Answer 1

Use MAX() window function to check if there are valid values in units_a_sold for each beverage and FIRST_VALUE() window function to pick the proper country:

SELECT DISTINCT beverage,
       CASE 
         WHEN MAX(units_a_sold) OVER (PARTITION BY beverage) > 0 
           THEN FIRST_VALUE(country) OVER (PARTITION BY beverage ORDER BY units_a_sold DESC) 
         ELSE FIRST_VALUE(country) OVER (PARTITION BY beverage ORDER BY units_b_sold DESC)
       END country
FROM temp;

See the demo.