lesson 64 Database engineering · advanced

CTEs, CTAS & temporary tables

Choose between named queries, created tables and temporary results.

the idea

A CTE lives only within one statement. CREATE TABLE … AS SELECT (CTAS) stores a result as a new table, and CREATE TEMP TABLE stores it only for the current connection. Materialize an intermediate result when several later statements reuse it.

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

   Store daily counts in a TEMP table. CTAS groups flights by date once; the top five busiest days come from the stored table instead of scanning all flights again.the pattern

   example.sql Run

   sql›

   CREATE TEMP TABLE daily_flights AS
   SELECT flight_date, COUNT(*) AS flights
   FROM flights
   GROUP BY flight_date;
   SELECT *
   FROM daily_flights
   ORDER BY flights DESC
   LIMIT 5;

2. 2

   Answer two questions from one table. After the same TEMP build, MIN and MAX flights across days show quietest versus busiest without regrouping the source.

   step-2.sql Run

   sql›

   CREATE TEMP TABLE daily_flights AS
   SELECT flight_date, COUNT(*) AS flights
   FROM flights
   GROUP BY flight_date;
   SELECT MIN(flights) AS quietest_day, MAX(flights) AS busiest_day
   FROM daily_flights;

common mistake

A table built with CTAS does not update when its source changes; it is a copy at the time it was created.