Period Delta - SQL Programming

LAG reads a value from the previous report row so a query can calculate changes between periods.

Program

Play the script to choose a product and compare each month's users with the prior month.

period_delta_lag.sql

CREATE TABLE report_choice AS WITH params(wanted_product) AS (VALUES ()) SELECT wanted_product FROM params;
CREATE TABLE monthly_usage (product TEXT, month TEXT, users INTEGER);
INSERT INTO monthly_usage VALUES ('app', '2026-01', 100), ('app', '2026-02', 130), ('app', '2026-03', 120), ('web', '2026-01', 80), ('web', '2026-02', 90), ('web', '2026-03', 110);
SELECT month, users, LAG(users) OVER (PARTITION BY product ORDER BY month) AS prior_users, users - LAG(users) OVER (PARTITION BY product ORDER BY month) AS delta FROM monthly_usage WHERE product = (SELECT wanted_product FROM report_choice) ORDER BY month;

CREATE TABLE report_choice AS WITH params(wanted_product) AS (VALUES ()) SELECT wanted_product FROM params;
CREATE TABLE monthly_usage (product TEXT, month TEXT, users INTEGER);
INSERT INTO monthly_usage VALUES ('app', '2026-01', 100), ('app', '2026-02', 130), ('app', '2026-03', 120), ('web', '2026-01', 80), ('web', '2026-02', 90), ('web', '2026-03', 110);
SELECT month, users, LAG(users) OVER (PARTITION BY product ORDER BY month) AS prior_users, users - LAG(users) OVER (PARTITION BY product ORDER BY month) AS delta FROM monthly_usage WHERE product = (SELECT wanted_product FROM report_choice) ORDER BY month;

LAG `LAG(users)` reads the previous row in the same product partition.

delta Subtracting the lagged value gives a period-over-period change.

first row The first row has no previous period, so the prior value and delta are `NULL`.