lesson 15 Calculations & data quality · intermediate

Conditional aggregation

Calculate several conditional metrics in one summary.

the idea

Conditional aggregation puts a CASE inside an aggregate so one grouped query can count several categories side by side. SUM(CASE WHEN condition THEN 1 ELSE 0 END) counts the rows that meet the condition.

Dataset: U.S. Flights

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

   See cancelled vs operated flights. Run this. Cancelled flights have NULL departure_delay; operated flights show a delay in minutes.

   step-1.sql Run

   sql›

   SELECT flight_id, airline_code, cancelled, departure_delay
   FROM flights
   WHERE flight_date = '2025-01-10'
   LIMIT 15;

2. 2

   Count diversions in each airline column. Run this. SUM(CASE WHEN …) adds one per diverted flight within each airline group.the pattern

   example.sql Run

   sql›

   SELECT
     airline_code,
     SUM(CASE WHEN diverted = 1 THEN 1 ELSE 0 END) AS diverted
   FROM flights
   GROUP BY airline_code;

common mistake

A cancelled flight has no departure delay. Treating NULL as on time overstates punctuality; restrict on-time counts to operated flights.