lesson 23 Joins & sets · intermediate

CROSS JOIN & a complete date grid

Build every combination of dates and entities.

the idea

CROSS JOIN pairs every row of one table with every row of another. Pairing stations with calendar dates builds a complete grid of expected station-days; a LEFT JOIN from that grid to the observations then shows which expected days have no data.

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

   Pair every date with one station. Run this. CROSS JOIN builds every combination of station and calendar date in the filter.the pattern

   example.sql Run

   sql›

   SELECT s.station_id, c.date
   FROM stations s
   CROSS JOIN calendar c
   WHERE s.station_id = 'USW00094728'
     AND c.date BETWEEN '2024-01-01' AND '2024-01-03';

2. 2

   Compare expected days to reported days. Run this. LEFT JOIN observations onto the grid; COUNT(d.observation_date) counts days with data.

   step-2.sql Run

   sql›

   SELECT
     s.station_id,
     COUNT(*) AS expected_days,
     COUNT(d.observation_date) AS reported_days
   FROM stations s
   CROSS JOIN calendar c
   LEFT JOIN daily_weather d
     ON d.station_id = s.station_id
     AND d.observation_date = c.date
   GROUP BY s.station_id
   ORDER BY reported_days
   LIMIT 5;

common mistake

Grouping or averaging only the observations hides missing days entirely: a station that did not report looks the same as one that did.