Day over day change in orders

Medium

Question

The daily_orders table has one row per calendar day with the number of orders on that day. The rows are stored in no particular order.

For every day, return day, orders and change. The change is the number of orders on that day minus the number of orders on the day before. A drop gives a negative number, and no change gives 0.

The first day has no day before it, so its change must be NULL. Sort by day from old to new.

The order of the rows matters for this question.

window function
lag
day over day
null

Read first: Lesson 47, SQL window functions: ranks, running totals, LAG and frames

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.

daily_orders2 columns · 7 rowsnot run yet

CREATE TABLE daily_orders (day TEXT PRIMARY KEY, orders INTEGER NOT NULL);

Example rows of the daily_orders table
day	orders
2026-06-04	128
2026-06-01	120
2026-06-06	90
2026-06-03	128
2026-06-02	135
2026-06-07	95
2026-06-05	150

About this SQL

LAG(x) looks at the previous row and LEAD(x) at the next row, in the order given inside OVER. Without ORDER BY inside OVER the order is not defined, so always write it. The default for a missing row is NULL; the optional third argument LAG(x, 1, 0) changes that default, and it is easy to use it by mistake here.

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