CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Quinn Eckart
Quinn Eckart

Reputation: 1

How to write a CASE statement that is inclusive of all conditions?

I have a case statement like:

case
    when invoice_net_amount <= 50 then '1. <= 50'
    when invoice_net_amount > 50 then '2. > 50'
    when invoice_net_amount > 250 then '3. > 250'
    when invoice_net_amount > 500 then '4. > 500'
end as user_revenue_tier

The problem is that all users fall in the first two buckets. I would like users with invoice_net_amount > 50 to show in buckets 2, 3, and 4 instead of just 2.

Is there a way to do this? I am using Snowflake if that matters.

Upvotes: 0

Views: 374

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175556

CASE expression is evaluated based on order of conditions and first match "wins":

case   
    when invoice_net_amount > 500 then '4. > 500'
    when invoice_net_amount > 250 then '3. > 250'
    when invoice_net_amount > 50  then '2. > 50'
    when invoice_net_amount <= 50 then '1. <= 50'
end as user_revenue_tier

By swapping the order you will get desired results.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You need to phrase the conditions like this:

(case when invoice_net_amount <= 50  then '1. <= 50'
      when invoice_net_amount <= 250 then '2. > 50'
      when invoice_net_amount <= 500 then '3. > 250'
      else '4. > 500'
 end) as user_revenue_tier

case expressions stop at the first matching condition. So, a value of 1000 matches > 50, so it goes to the second condition. As phrased in this answer, it falls through to the else.

Upvotes: 2

Related Questions