How's Order Volume Trending Month to Month?
Ops at Whetcode Supply is putting a month-over-month order-volume chart on the warehouse wall, and someone has to hand the charting tool a tidy series: one figure per calendar month.
Nothing in the database is stored per month. Every order carries a full calendar date, and the ten of them scatter across five different months of 2023, so the month is a thing you have to make out of the date before you can total anything by it. Bear in mind too that a month in which nobody ordered leaves no trace at all in a table of orders — there is no blank row waiting to be found.
orders — one row per order placed. order_date is the calendar date the order was placed, stored as text in YYYY-MM-DD form.
| 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, order_month and num_orders. order_month is the calendar month the order was placed in, as text in YYYY-MM form — 2023-04, not April and not 4. num_orders is how many orders fell in that month. There is one row for every month that saw at least one order; a month with no orders produces no row rather than a row reading 0. Rows come back sorted by order_month, earliest first.
Example output — shape only, on an invented year. Note the month format: 2021-08, not August and not 8.
| order_month | num_orders |
|---|---|
| 2021-08 | 4 |
| 2021-09 | 1 |
| 2021-11 | 7 |
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