lesson 31 Dates, strings & subqueries · intermediate

Readable queries with CTEs

Break a query into named, readable steps.

the idea

WITH name AS (query) defines a common table expression: a named intermediate result the main query can read like a table. CTEs let you build an answer in steps, each at a clear grain, and refer to an earlier step more than once.

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

   Define a step with WITH. Run this. The CTE names daily flight counts; the outer query filters busy days.

   step-1.sql Run

   sql›

   WITH daily AS (
     SELECT flight_date, COUNT(*) AS flights
     FROM flights
     GROUP BY flight_date
   )
   SELECT *
   FROM daily
   WHERE flights > 1500;

2. 2

   Summarize delay by route. Run this. Group operated flights by route_id and average departure_delay.

   step-2.sql Run

   sql›

   SELECT
     route_id,
     COUNT(*) AS operated,
     ROUND(AVG(departure_delay), 1) AS avg_delay
   FROM flights
   WHERE cancelled = 0
   GROUP BY route_id
   ORDER BY operated DESC
   LIMIT 10;

3. 3

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

   example.sql Run

   sql›

   WITH daily AS (
     SELECT flight_date, COUNT(*) AS flights
     FROM flights
     GROUP BY flight_date
   )
   SELECT *
   FROM daily
   WHERE flights > 1500;

common mistake

Averaging departure_delay over all flights includes NULLs for cancellations only implicitly. Filter to operated flights so the population is explicit.