Marketplace Orders per Month in 2024 — SQL Dates Problem
For the annual review, the leadership team wants the order count of each month of calendar year 2024, leaving out cancelled orders.
- Difficulty: Easy
- Topics: Dates, Aggregation
- Dialect: MySQL
- Problem: #103
Problem statement
For the annual review, the leadership team wants the order count of each month of calendar year 2024, leaving out cancelled orders. Orders placed in 2023 or 2025 are not part of it.
Return the columns month — the month as text in the form YYYY-MM — and orders, the number of non-cancelled orders placed in that month. Months with no such order are not listed. Return the rows ordered by month.
Tables
Table: orders
| Column | Type |
|---|---|
| order_id | int |
| customer_id | int |
| placed_at | datetime |
| status | enum(placed, packed, shipped, delivered, cancelled) |
Primary key: order_id.
One row per order; placed_at is when the customer paid or confirmed it.
Examples
Example 1
orders
| order_id | customer_id | placed_at | status |
|---|---|---|---|
| 1 | 21 | 2023-12-31 23:58:10 | delivered |
| 2 | 22 | 2024-01-01 00:04:45 | delivered |
| 3 | 23 | 2024-01-19 14:22:00 | cancelled |
| 4 | 21 | 2024-01-28 09:15:30 | shipped |
| 5 | 24 | 2024-03-02 18:40:00 | delivered |
| 6 | 25 | 2024-12-31 22:10:05 | placed |
| 7 | 26 | 2025-01-01 00:00:01 | delivered |
Output
| month | orders |
|---|---|
| 2024-01 | 2 |
| 2024-03 | 1 |
| 2024-12 | 1 |
How to solve Marketplace Orders per Month in 2024
Bucket each order by the month it was placed, count per bucket. DATE_FORMAT(placed_at, '%Y-%m') produces the bucket label directly; because the format is zero-padded and year-first, the labels also sort chronologically, so ORDER BY month gives January first.
The filter has two parts and both belong in WHERE, before grouping: the year is 2024, and the order is not cancelled. The year test can be written with YEAR(placed_at) = 2024 or as a half-open range placed_at >= '2024-01-01' AND placed_at < '2025-01-01'. The half-open range is the one to prefer on a real table: it can use an index on placed_at, and it gets the boundaries right — an order at 22:10 on 31 December is inside, while BETWEEN '2024-01-01' AND '2024-12-31' would cut it off at midnight.
Months without orders simply produce no group. Building the label from YEAR and MONTH with LPAD is another way to the same text.
Reference solution (MySQL)
SELECT DATE_FORMAT(placed_at, '%Y-%m') AS month, COUNT(*) AS orders
FROM orders
WHERE YEAR(placed_at) = 2024 AND status <> 'cancelled'
GROUP BY DATE_FORMAT(placed_at, '%Y-%m')
ORDER BY monthAnother way
SELECT LEFT(placed_at, 7) AS month, COUNT(*) AS orders FROM orders WHERE placed_at >= '2024-01-01' AND placed_at < '2025-01-01' AND status IN ('placed', 'packed', 'shipped', 'delivered') GROUP BY LEFT(placed_at, 7) ORDER BY monthAnother way
SELECT month, SUM(CASE WHEN status = 'cancelled' THEN 0 ELSE 1 END) AS orders FROM (SELECT CONCAT(YEAR(placed_at), '-', LPAD(MONTH(placed_at), 2, '0')) AS month, status FROM orders WHERE YEAR(placed_at) = 2024) t GROUP BY month HAVING SUM(CASE WHEN status = 'cancelled' THEN 0 ELSE 1 END) > 0 ORDER BY month← Coupon Redemption Summary per Code · Cart Shipping Fee and Amount Payable →