BLACK FRIDAY EXTRA SAVINGS EVENT - EXTENDED
START FREE

CASE condition confusion in SQL

Screen Link:

My Code:

SELECT CASE
        WHEN Sample_size < 200 THEN 'Small'
        WHEN 200 <= Sample_size < 1000 THEN 'Medium'
        ELSE 'Large'
        END AS Sample_category
    FROM recent_grads

What I expected to happen:
Create a new Sample_category column and filter the result as condition

What actually happened:

Wrong answer. Checked and found that the new column only contains small and medium value. No 'Large' value.

The only difference between my answer and correct answer is the second WHEN clause. The correct answer is
SELECT CASE
WHEN Sample_size < 200 THEN ‘Small’
WHEN Sample_size < 1000 THEN ‘Medium’
ELSE ‘Large’
END AS Sample_category
FROM recent_grads;
But why is that? I thought adding a 200<= Sample_size would not affect the answer.

Hello @gt1143,

Referring to the instruction on the course page:

  • Medium if Sample_size is equal to or higher than 200, and smaller than 1000.`

When working in a range, you should explicitly state the conditions such as

WHEN 200 <= Sample_size AND Sample_size < 1000 THEN 'Medium'

So is Dataquest wrong here? Their answer seems to suggest WHEN Sample_size < 1000 Then 'Medium' is the correct answer, even though it does not explicitly suggest that it should be greater than or equal to 200 as well.

I just tried running both doyinsolamiolaye’s answer and Dataquest’s supplied answer, and they both work, oddly enough. I’m not sure why it would work without having both conditions in there.

The reason it works without both conditions is because CASE will return a value once a condition has been met, otherwise it continues to the next WHEN statement. Therefore, if our WHEN statements are:

WHEN Sample_size < 200 THEN 'Small'
WHEN Sample_size < 1000 THEN 'Medium'

then if Sample_size is not labelled as ‘Small’ we know it must be greater than or equal to 200 and our query continues on to the next WHEN statement to see if that condition has been met. This is why it is very important to order your WHEN statements carefully.

For example, if the above statements were reversed:

WHEN Sample_size < 1000 THEN 'Medium'
WHEN Sample_size < 200 THEN 'Small'

then nothing would ever be labelled as ‘Small’ because anything less than 200 is automatically less than 1000 which will be labelled as ‘Medium’.