lesson 34 Dates, strings & subqueries · intermediate

Date boundaries & monthly buckets

Define date ranges and monthly groups without boundary mistakes.

the idea

A period filter should be half-open: start <= t AND t < next start. It includes every time on the last day and never double-counts the boundary. To count per month, derive a month label and group; COUNT(DISTINCT x) counts different values within each bucket.

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

   Count flights on one calendar day. Run this. Half-open bounds from midnight to next midnight include the whole day.the pattern

   example.sql Run

   sql›

   SELECT COUNT(*) AS new_year_flights
   FROM flights
   WHERE scheduled_departure >= '2025-01-01 00:00'
     AND scheduled_departure < '2025-01-02 00:00';

2. 2

   Group flights and aircraft by month. Run this. substr on flight_date buckets rows; COUNT(DISTINCT tail_number) counts unique planes.

   step-2.sql Run

   sql›

   SELECT
     substr(flight_date, 1, 7) AS month,
     COUNT(*) AS flights,
     COUNT(DISTINCT tail_number) AS aircraft
   FROM flights
   GROUP BY month;

common mistake

date(scheduled_departure) = '2025-02-28' is correct but cannot use a plain index on scheduled_departure; a half-open range can.