Reporting Windows

Period Delta

Compare with Previous Row

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.

wanted_product
period_delta_lag.sql
Replay: real traced execution (multi-file project)
CREATE TABLE report_choice AS WITH params(wanted_product) AS (VALUES ('app')) 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 ('web')) 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;

  1. choice ← 1 row

    1CREATE TABLE report_choice AS WITH params(wanted_product) AS (VALUES ('app')) SELECT wanted_product FROM params;2CREATE TABLE monthly_usage (product TEXT, month TEXT, users INTEGER);
    values this step1 rowchoice

  2. monthly_usage ← 6 rows

    2CREATE TABLE monthly_usage (product TEXT, month TEXT, users INTEGER);3INSERT 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);4SELECT 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;
    values this step6 rowsmonthly_usage

  3. result ← 3 rows

    3INSERT 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);4SELECT 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;
    values this step3 rowsresult

  1. choice ← 1 row

    1CREATE TABLE report_choice AS WITH params(wanted_product) AS (VALUES ('web')) SELECT wanted_product FROM params;2CREATE TABLE monthly_usage (product TEXT, month TEXT, users INTEGER);
    values this step1 rowchoice

  2. monthly_usage ← 6 rows

    2CREATE TABLE monthly_usage (product TEXT, month TEXT, users INTEGER);3INSERT 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);4SELECT 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;
    values this step6 rowsmonthly_usage

  3. result ← 3 rows

    3INSERT 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);4SELECT 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;
    values this step3 rowsresult
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`.