Revenue at the price in force on the order day

Hard

Question

The price_changes table records each price of a product and the day it starts: a price is valid from its valid_from day (including that day) until the day before the next valid_from of the same product. The newest price has no end.

The orders table has the product_id, the day of the order (ordered_on) and the qty. The money for an order is qty times the price that was valid on the day of the order. An order made before the first price of its product, or for a product that has no price at all, has no price: it brings no revenue, but it must be counted.

For every month that has at least one order, return month (like 2026-02), revenue (the sum of the money of the orders of the month, or 0 when no order of the month has a price) and unpriced_orders (how many orders of the month have no price). Sort by month.

The order of the rows matters for this question.

as of join
scd
correlated subquery
lead
range join
left join
coalesce
strftime

Read first: Lesson 48, SQL patterns that interviews repeat

Tables

2 tables

These are the example rows. Run uses only these rows. Submit also tests your query on a second set of rows that you cannot see.

price_changes3 columns · 6 rowsnot run yet

CREATE TABLE price_changes (product_id INTEGER NOT NULL, valid_from TEXT NOT NULL, price INTEGER NOT NULL, PRIMARY KEY (product_id, valid_from));

Example rows of the price_changes table
product_id	valid_from	price
1	2026-01-01	100
1	2026-02-10	120
1	2026-03-15	90
2	2026-01-20	500
2	2026-03-01	550
3	2026-02-01	40

orders4 columns · 11 rowsnot run yet

CREATE TABLE orders (id INTEGER PRIMARY KEY, product_id INTEGER NOT NULL, ordered_on TEXT NOT NULL, qty INTEGER NOT NULL);

Example rows of the orders table
id	product_id	ordered_on	qty
1	1	2026-01-05	2
2	1	2026-02-10	1
3	1	2026-02-09	3
4	2	2026-01-10	1
5	2	2026-01-25	2
6	2	2026-03-01	1
7	1	2026-03-20	4
8	3	2026-01-31	5
9	3	2026-02-28	10
10	1	2026-03-14	2
11	1	2025-12-20	1

About this SQL

This is an "as of" join: for each order you want the one price row with the biggest valid_from that is not after the order day. Some databases have an ASOF JOIN or LATERAL for this; SQLite has neither. Two common ways are a correlated subquery with ORDER BY ... DESC LIMIT 1, or LEAD(valid_from) to give each price an end day and then a range join. SUM skips NULL, but SUM of only NULLs is NULL, not 0. ISO dates compare correctly as text.

SQLite

Opening the code editor…

Result

Run shows what your query gives on the example rows.Submit checks it on the example rows and on a hidden set of rows.

SQL · ready