LAG, changes and the previous observed period

LAG retrieves a value from a preceding ordered row in a partition. The worked query first aggregates observations to one company-day row, then attaches the previous observed day's total for that company. Company A has totals five on the first and one on the third.

The predecessor for A on the third is its first-day total five, not company B's second-day value. It is also not automatically yesterday: the selected observation sequence has a missing calendar day. Sequence adjacency and calendar adjacency are different contracts.

The daily intermediate result establishes one row per company and date. LAG partitions by company_id and orders those unique dates. The first observed day has no predecessor, so its previous value is null rather than an invented zero.

Aggregating before the window prevents two raw observations on A's first day from being treated as two separate daily periods. A downstream difference can then compare daily totals instead of accidentally comparing a partial observation with a completed day.

The counterexample omits the company partition and calculates a global predecessor across companies. Its deterministic day-company ordering still does not represent a per-company history. A wrong partition can produce plausible values that belong to another entity entirely.

Another common mistake filters to the final date before computing LAG, leaving the selected row with no predecessor. If the question needs a comparison with earlier history, compute the window over that history first and filter the displayed period afterward.

Return each company-day with the change from its previous observed day. Keep the daily aggregation and company partition, then subtract LAG(added) from added. Predict null for the first days, minus four for A on the third and minus two for B on the second.

Do not coalesce the first predecessor to zero: that would label the first observation as a measured increase from zero without evidence. The task asks for absolute differences, not percentages, so it introduces no division or zero-denominator policy.

Step 1: Keep one daily row per company. Step 2: Subtract the company-specific predecessor without replacing its absence.

The solution preserves four company-day rows and reports exactly those changes. A's minus four spans the gap between its first and third day; the label must therefore say previous observed day. It does not establish a one-day decline without a complete calendar policy.

Test the first-row null, the partition boundary and the missing-day case. A fixture containing only one company and consecutive dates would conceal both a missing partition and an incorrect claim about calendar adjacency.

Explain the source grain, partition, ordering and predecessor meaning in an interview. Then identify whether the displayed time filter belongs before or after the window. These choices define the evidence behind a change, independently of which window-function syntax you remember.

The next lesson introduces a complete calendar scaffold. Once the reporting contract explicitly authorizes zero for absent complete-log days, the sequence can represent every calendar day rather than only the days that happened to have observations.

Optional stretch — after the core task works: Compute a display-date filter after the window and compare it with filtering history too early.

Computer practice: https://applydjinn.com/typorithm/sql-lab-20261105.zip. Load the shared setup.sql fixture before running a snippet. The local verifier compares exact ordered row arrays in SQLite and PostgreSQL, keeps SQL values separate from driver bindings and never connects to the production website database.