Orders per Month
The warehouse at Whetcode Supply is staffed a month at a time, and the rota is drawn up from history: how many orders came in during each month the shop has been trading, oldest month first so the shape reads left to right on a chart.
Nothing in the data records a month on its own. Every order carries a full date stored as text, so the month has to be recovered from it before anything can be counted per month.
orders — one row per order. customer_id is the id of the customer who placed it, product_id the id of the item bought, quantity how many units of that item, and order_date the day it was placed. An order row covers one item only.
| 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 and orders, one row per month in which at least one order was placed, oldest month first. month is the year and month as text in the form 2023-02, not a number and not a month name. orders is how many orders were placed in that month. A month with no orders does not appear as a zero — it does not appear at all.
Example output — shape only, on invented months. Note the YYYY-MM text and the gap: nothing was sold in the third month, and it is absent rather than zero:
| month | orders |
|---|---|
| 2019-01 | 19 |
| 2019-02 | 27 |
| 2019-04 | 14 |
| 2019-05 | 22 |
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