Merge repeated price rows into periods

Hard

Question

The price_log table records the price of each product. A row says that from the day changed_on the product costs price, until a later row of the same product. The shop also adds a row when the price is only confirmed again, so the same price can appear in many rows in a row. A product never has two rows on the same day.

Turn the log into periods. A period is a group of rows of one product that follow each other and have the same price. If a product goes from 50 to 55 and then back to 50, that is three periods, not two. Return product, price, from_date (the changed_on of the first row of the period) and to_date (the day before the first day of the next period of the same product). The last period of a product has no next period, so its to_date is NULL.

Sort by product, then by from_date.

The order of the rows matters for this question.

gaps and islands
window function
lag
lead
running total
date
cte
scd

Read first: Lesson 49, SQL patterns for time: streaks, sessions, cohorts, funnels and rolling windows

Tables

1 table

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_log4 columns · 12 rowsnot run yet

CREATE TABLE price_log (id INTEGER PRIMARY KEY, product TEXT NOT NULL, changed_on TEXT NOT NULL, price INTEGER NOT NULL);

Example rows of the price_log table
id	product	changed_on	price
1	Rice	2026-01-01	50
2	Rice	2026-02-01	50
3	Rice	2026-03-01	55
4	Rice	2026-04-01	50
5	Rice	2026-05-01	50
6	Dal	2026-01-15	120
7	Dal	2026-03-10	120
8	Dal	2026-03-20	120
9	Oil	2026-01-01	200
10	Oil	2026-01-20	210
11	Oil	2026-02-10	210
12	Oil	2026-02-25	190

About this SQL

Grouping by product and price alone would put the first and the last period of Rice (both 50) together, because GROUP BY ignores the order of the rows. First mark the rows where the price is not the same as in the previous row (LAG), add up the marks to get a period number, and group by that number. date(x, '-1 day') moves a text date back by one day and gives NULL when x is NULL, so the last period gets NULL without extra code. LAG and LEAD need SQLite 3.25 or newer.

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