DEV Community

[Comment from a deleted post]
Collapse
 
geraldew profile image
geraldew

Sorry to nitpick but you're leaving a gap between 3.99 and 4.00, which will thereby cascade into the ELSE clause and be miscategorised as 'low'.

The cascade of tests in a CASE express allow for each boundary to be given only once, which is the neatest way to avoid such a slip.

 SELECT
     game, 
     global, 
     CASE 
         WHEN global >= 4.00 THEN 'high' 
         WHEN global >= 2.00 THEN 'mid' 
         WHEN global >= 0.00 THEN 'low' 
         ELSE '!!!' 
         END AS 'Sales Range' 
 FROM
     sega_sales
 ;

And yes, I've also snuck in a common practice of mine, the ELSE that you don't really expect to ever occur. The idea is that when it does occur*, you will notice and you will then work out what you will need to do about presumptions being wrong. In this case it would pose the question: "are my ranges still meaningful if negative values show up". A similar argument could lead to adding an extra clause in front of the first:

         WHEN global >= 40.00 THEN '###' 

.. where I've arbitrarily applied a factor of 10 as "game changer" detector.

In a away, this is definitely me nit-picking some code, but, to be frank I also think this is a way of showing how useful CASE expressions are in regular data analysis work, and a good habit to ingrain early on.

Where * = Gerald's (half tongue in cheek) Law, that in data work, everything happens (sometime).