MediumE-commerce10 min

Orders by month

Loading the editor…

Orders by month

Challenge

Count orders placed in each calendar month.

Business context

Operations wants order volume by the month the order was placed, including cancelled and pending orders. This is an intake count, not a revenue count.

Instructions

Return month as YYYY-MM and orders. Use ordered_at. Count every order. Sort by month.

Schema

customers

id INTEGER · primary key
name TEXT
email TEXT
city TEXT
country TEXT
created_at TEXT · YYYY-MM-DD

products

id INTEGER · primary key
name TEXT
category TEXT
price_cents INTEGER
stock INTEGER · nullable

orders

id INTEGER · primary key
customer_id INTEGER · customers.id
status TEXT · paid, shipped, pending, cancelled
ordered_at TEXT
shipped_at TEXT · nullable

order_items

id INTEGER · primary key
order_id INTEGER
product_id INTEGER
quantity INTEGER
unit_price_cents INTEGER

payments

id INTEGER · primary key
order_id INTEGER
method TEXT
amount_cents INTEGER
paid_at TEXT · nullable
status TEXT · succeeded, failed, refunded

Sample rows

customers
id	name	email	city	country	created_at
1	Alice Chen	alice@northwind.io	Berlin	Germany	2023-01-12
2	Ben Okonkwo	ben@okonkwo.co	Lagos	Nigeria	2023-03-02
3	Clara Singh	clara@singh.dev	London	United Kingdom	2022-11-20

products
id	name	category	price_cents	stock
1	Trail Bottle	Outdoors	2400	120
2	Desk Lamp	Home	4250	40
3	Noise-canceling Headphones	Electronics	18900	15

orders
id	customer_id	status	ordered_at	shipped_at
1	1	paid	2024-11-02	2024-11-04
2	1	paid	2024-12-14	2024-12-16
3	3	shipped	2025-01-09	2025-01-11

order_items
id	order_id	product_id	quantity	unit_price_cents
1	1	1	2	2400
2	1	4	1	1200
3	2	3	1	18900

payments
id	order_id	method	amount_cents	paid_at	status
1	1	card	6000	2024-11-02	succeeded
2	2	card	18900	2024-12-14	succeeded
3	3	paypal	22000	2025-01-09	succeeded

Draft saves automatically

Ctrl or Cmd + Enter runs the query. The database resets on every run.

Run the query to see rows from the sample database.

Next exercise: Bills paid after the due date