lesson 32 Dates, strings & subqueries · intermediate

Dates & monthly analysis

Group events into months and analyze dates.

the idea

SQLite stores these dates as ISO text. strftime formats dates: %Y-%m extracts a year and month. Group by that expression to build a monthly summary. Other SQL dialects use different date functions.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

orders6 columns · 12 rows

idinteger · key	customer_idinteger	product_idinteger	quantityinteger	statustext	ordered_attext
101	1	1	2	paid	2025-01-01
102	2	3	1	paid	2025-01-02
103	1	2	1	paid	2025-01-05
104	3	5	2	pending	2025-01-08
105	4	4	5	paid	2025-02-01

showing 5 of 12 rows

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

example.sql

SELECT strftime('%Y-%m', ordered_at) AS month, COUNT(*) AS order_count
FROM orders
GROUP BY month;

Watch for: Grouping only by month number mixes years.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1
Read the order dates. Each order has one timestamp. The next step groups those timestamps into year-month buckets.
step-1.sql
sql›
```
SELECT ordered_at
FROM orders;
```
2
Run the reference. SQLite stores these dates as ISO text. strftime formats dates: %Y-%m extracts a year and month. Group by that expression to build a monthly summary. Other SQL dialects use different date functions.
step-2.sql
sql›
```
SELECT strftime('%Y-%m', ordered_at) AS month, COUNT(*) AS order_count
FROM orders
GROUP BY month;
```