Calculate Monthly Revenue from Orders in 2023

orders

id	order_date	customer
1	2023-01-03	101
2	2023-02-14	102
3	2023-02-20	101

order_items

id	order_id	item	price
1	1	A	25.00
2	1	B	15.00
3	2	C	35.00

Scenario

Online assessment: SQL section with order and order_item data.

Question

Given tables orders and order_items, write a SQL query that returns each calendar month of 2023 and the corresponding total revenue (sum of price).

Hints

JOIN the two tables, filter by year, GROUP BY month extracted from order_date, ORDER BY month.

Monthly Revenue for 2023

You are given two tables, orders and order_items. Write a SQL query that returns each calendar month of 2023 (months 1 through 12) and the corresponding total revenue for that month, where revenue is defined as the sum of price from order_items joined to orders. The result should include months with zero revenue and be ordered by month ascending.

Table orders

column	type	constraints
id	INTEGER	PRIMARY KEY, NOT NULL
order_date	DATE	NOT NULL
customer	INTEGER	NOT NULL

Sample data:

customer	id	order_date
101	1	2023-01-03
102	2	2023-02-14
101	3	2023-02-20

Table order_items

column	type	constraints
id	INTEGER	PRIMARY KEY, NOT NULL
order_id	INTEGER	NOT NULL, FOREIGN KEY REFERENCES orders(id)
item	VARCHAR(50)	NOT NULL
price	DECIMAL(10,2)	NOT NULL

Sample data:

id	item	order_id	price
1	A	1	25
2	B	1	15
3	C	2	35

Expected output

One row per calendar month of 2023 with the total revenue computed from order_items joined to orders. Includes months with zero revenue. Ordered by month ascending.

month	total_revenue
1	40
2	35
3	0
4	0
5	0
6	0
7	0
8	0
9	0
10	0
11	0
12	0

Hints

1. JOIN orders to order_items on orders.id = order_items.order_id.
2. Filter rows to year 2023 using the order_date column.
3. GROUP BY the month extracted from order_date.
4. ORDER BY the month number ascending.
5. To include months with no orders, generate months 1–12 (e.g., using a recursive CTE or a numbers table) and LEFT JOIN the aggregated revenue.