Monthly Order Count
Whetcode Supply's founder wants a simple trend line for the year: how many orders landed in each calendar month, earliest month at the top, so the shape of the year is readable at a glance.
order_date is not a date type — SQLite does not have one. It is plain text in YYYY-MM-DD form, which is a nuisance and a gift at the same time. A nuisance, because the month has to be pulled out of a string before anything can be bundled by it. A gift, because ISO dates are zero-padded and written biggest-unit-first, so comparing them as text is already comparing them as time.
orders — one row per order placed this year. order_date is an ISO date string.
| id | customer_id | product_id | quantity | order_date |
|---|---|---|---|---|
| 1 | 1 | 1 | 2 | 2023-02-01 |
| 2 | 1 | 4 | 1 | 2023-02-01 |
| 3 | 2 | 2 | 1 | 2023-02-10 |
| 4 | 3 | 3 | 3 | 2023-03-05 |
| 5 | 4 | 5 | 5 | 2023-03-11 |
| 6 | 1 | 3 | 1 | 2023-04-02 |
| 7 | 5 | 1 | 4 | 2023-05-20 |
| 8 | 2 | 5 | 2 | 2023-05-22 |
| 9 | 3 | 4 | 1 | 2023-06-01 |
| 10 | 5 | 2 | 1 | 2023-06-15 |
The table already exists in the database — there is nothing to create or load.
Task: Write a query returning two columns, month (in YYYY-MM form) and num_orders, with one row per calendar month that saw at least one order, earliest month first.
Example output — shape only, on an invented year. Note the month format: 2021-08, not August and not 8.
| month | num_orders |
|---|---|
| 2021-08 | 4 |
| 2021-09 | 1 |
| 2021-11 | 6 |
Sign in to solve this problem
Reading problems is free for everyone — solving them (Run, Submit, and tracking what you've solved) needs an account.
Sign in