lesson 32 Dates, strings & subqueries · intermediate

Dates & monthly analysis

Group events into months and analyze dates.

the idea

strftime formats a timestamp; strftime('%Y-%m', pickup_at) turns each timestamp into a month label you can group by. Grouping by a derived label changes the grain to one row per period. Real data can contain records outside the expected period.

Dataset: Mobility

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

   Pull month from each timestamp. Run this. strftime('%Y-%m', pickup_at) labels each trip with its pickup month.

   step-1.sql Run

   sql›

   SELECT trip_id, pickup_at, strftime('%Y-%m', pickup_at) AS month
   FROM trips
   LIMIT 10;

2. 2

   Count trips by weekday. Run this. strftime('%w', pickup_at) gives 0–6 for Sunday–Saturday for grouping.the pattern

   example.sql Run

   sql›

   SELECT strftime('%w', pickup_at) AS weekday, COUNT(*) AS trips
   FROM trips
   GROUP BY weekday;

common mistake

The extract has one trip whose pickup timestamp is in December 2024. Grouping shows it; a filter decides whether it belongs in the analysis.