Is it bad practice to put a middle tier value as an ELSE for case

Screen Link:

Your Code:

       WHEN sum( < 40 THEN 'small spender'
       WHEN sum( > 100 THEN 'big spender'
       ELSE 'regular'
       AS customer_category

What i have been told before is that for logic, it is best to set each value tier to cover for every scenario, e.g.

< 40 - small spender
<=40 <= 100 - regular spender
>100 - big spender

but why is this different here for this question, or is it just how SQL works?


Other than the discrepancy between regular and regular spender, I don’t see how the alternative you provided would be any different.

hey @willx

consider it this way, you have a business scenario, which tells you to classify customers in three distinct categories:
small spender - ones who shell out < 40
big spender - ones who shell out > 100
and regular!

Assume for “regular” no definition has been given.
The business scenario hasn’t explicitly told you it’s between 40 and 100.

Now if you apply this logic technically anything between $0 and $39/ $40 goes as small.
Anything beyond $100 goes as big. (price would be a positive float/ int)

So regardless of, if you make a third case or put an else clause, any value which falls outside the two brackets should be considered “regular”.

Considering DQ wants us to understand the complete syntax of CASE statements, they have given two examples of WHEN and an example of the ELSE clause, using this business scenario.

In a real-world scenario, it would be like you are taking two important classifications and any value that cannot be classified in either of the two takes a default value.

hope this helps.
(too long answer sorry)


yes, sure i think i get where both of you are saying that it is logically correct. just that normally when i see tutorial instructions, it goes something like this
e.g. for a classification for exam grading
< 20 - F
< 20 but less than 40 - E
< 40 but less than 60 - C
< 60 but less than 80 - B

it doesn’t sound intuitive to put the ELSE somewhere in between. am i making sense here, or is it something different about SQL which i have to accept at this point? just trying to piece together which am still finding it very tough here. :slight_smile:

hey @willx

It’s not just SQL if you come across any other language such as Python, JAVA, C#, C++, PLSQL etc. “ELSE” forms the last part of any CASE/ SWITCH or IF block statement.

It’s like the MCQ’s:

“This is the query”. Choose an option.
option A = A
option B = B
option C = C
option D = None of the above

ELSE always represents the option D.

Both these example consider a range, but what if we didn’t have a range:

We had something like:

       WHEN color_Red THEN Apple
       WHEN color_Orange THEN Tangerine
       WHEN color_Green THEN Avocado
#I don't know any other color or/and any other fruit!!
       ELSE "mystery fruit"  # (regardless of the color just classify as mystery)

I hope I haven’t confused you more. :thought_balloon:

1 Like

i see. thanks for the detailed explaination @Rucha :slight_smile:
so it is immaterial then what we put for ELSE, as long as it is not caught by any of the earlier cases.

1 Like