Marketplace Orders per Month in 2024

Easy
103.

Marketplace Orders per Month in 2024

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

ColumnTypeValues
order_idint
customer_idint
placed_atdatetime
statusenumplaced, packed, shipped, delivered, cancelled

Primary key: order_id.

One row per order; placed_at is when the customer paid or confirmed it.

Example

The rows must come back in the order shown.

orders

order_idcustomer_idplaced_atstatus
1212023-12-31 23:58:10delivered
2222024-01-01 00:04:45delivered
3232024-01-19 14:22:00cancelled
4212024-01-28 09:15:30shipped
5242024-03-02 18:40:00delivered
6252024-12-31 22:10:05placed
7262025-01-01 00:00:01delivered

Output

monthorders
2024-012
2024-031
2024-121
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
order_idint
customer_idint
placed_atdatetime
statusenum(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_idcustomer_idplaced_atstatus
1212023-12-31 23:58:10delivered
2222024-01-01 00:04:45delivered
3232024-01-19 14:22:00cancelled
4212024-01-28 09:15:30shipped
5242024-03-02 18:40:00delivered
6252024-12-31 22:10:05placed
7262025-01-01 00:00:01delivered

Output

monthorders
2024-012
2024-031
2024-121

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 month

Another 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 month

Another 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 →