Fulfledge

Analyse real data

INPUT · Slides

Laying it out by month to see change

01 / 04

The date is stored as text

ordered_on holds text like 2026-05-06. Year, month and day come in that order, so ORDER BY on it as it is already puts things in time order.

But to gather by month you have to pull just the month part out of the text.

02 / 04

Pulling the month out with strftime

strftime is the function that takes whatever part of a date you want. %Y is the year, %m the month.

Write strftime('%Y-%m', ordered_on) and 2026-05-06 comes back as 2026-05.

SELECT ordered_on,       strftime('%Y-%m', ordered_on)         AS month  FROM orders  ORDER BY id LIMIT 3;

Result

2026-05-06 | 2026-05
2026-05-11 | 2026-05
2026-05-18 | 2026-05

03 / 04

Gather on the month you pulled out

Name it with AS and you can reuse that name in both GROUP BY and ORDER BY.

With anything over time, sorting by month is the default. Sort by the number of orders instead and you can no longer read whether it went up or down.

SELECT strftime('%Y-%m', ordered_on)         AS month,       COUNT(*) AS times  FROM orders  GROUP BY month  ORDER BY month;

Result

2026-05 | 5
2026-06 | 6
2026-07 | 4

04 / 04

Change only shows up in a line

One month's number on its own cannot be called "a lot" or "a little". Only against the month next to it can you tell it rose or fell.

Orders, units and money all move separately. Things like "fewer orders but more money" happen, so lay all three out. Let us write some.