lesson 14 Calculations & data quality · intermediate

Make decisions with CASE

Choose a result for each row using conditional logic.

the idea

CASE evaluates its WHEN conditions in order and returns the first matching result; ELSE covers everything else. Put the case for missing values first so an unknown temperature is not silently labelled by ELSE.

Dataset: Weather

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   Label days as wet or dry. Run this. CASE checks each row and returns wet when precipitation_mm is above zero, otherwise dry.

   step-1.sql Run

   sql›

   SELECT
     station_id,
     observation_date,
     CASE
       WHEN precipitation_mm > 0 THEN 'wet'
       ELSE 'dry'
     END AS day_type
   FROM daily_weather
   WHERE station_id = 'USW00094728'
   LIMIT 15;

2. 2

   Handle NULL before other labels. Run this. Put IS NULL first in CASE so missing snowfall is not mistaken for zero.

   step-2.sql Run

   sql›

   SELECT
     observation_date,
     snowfall_mm,
     CASE
       WHEN snowfall_mm IS NULL THEN 'not reported'
       WHEN snowfall_mm > 0 THEN 'snow'
       ELSE 'none'
     END AS snow
   FROM daily_weather
   WHERE station_id = 'USW00093197'
   LIMIT 15;

3. 3

   Put it together. The lesson's pattern in one query.the pattern

   example.sql Run

   sql›

   SELECT
     station_id,
     observation_date,
     CASE
       WHEN precipitation_mm > 0 THEN 'wet'
       ELSE 'dry'
     END AS day_type
   FROM daily_weather
   WHERE station_id = 'USW00094728';

common mistake

CASE WHEN tmax_c < 0 THEN 'freezing' ELSE 'above freezing' END labels a missing temperature as above freezing.