Data Analyst Interview
Menu
Browse in your language. All mock interviews, preparation sessions and feedback are in English only.

Does SQLite LAG Respect a ROWS Window Frame?

No. In SQLite, LAG uses the ordered partition and ignores the window frame specification. Restricting the frame to CURRENT ROW does not prevent LAG from reading the previous row. If the business rule requires a time limit, calculate the previous timestamp and test that condition explicitly.

Why would an analyst expect the frame to restrict LAG?

A report might use the same window definition for a total and a previous-value comparison. It is tempting to assume that every function reads only the rows inside that frame. The following synthetic sales example demonstrates why that assumption fails in SQLite. US and GB are market identifiers; every amount is already in USD.

CREATE TABLE daily_sales (
 market TEXT, day TEXT, revenue_usd INTEGER
);
INSERT INTO daily_sales VALUES
 ('US','2026-11-01',100),
 ('US','2026-11-02',150),
 ('US','2026-11-10',200),
 ('GB','2026-11-02',80);

Assume one row per market and day. There are deliberately no US records between November 2 and November 10. A missing row is not automatically a zero-sales day: the exercise does not establish whether those daily extracts were received.

Compare LAG with SUM on a current-row frame

SELECT market, day, revenue_usd,
 LAG(revenue_usd) OVER (
  PARTITION BY market ORDER BY day
  ROWS BETWEEN CURRENT ROW AND CURRENT ROW
 ) AS previous_revenue,
 SUM(revenue_usd) OVER (
  PARTITION BY market ORDER BY day
  ROWS BETWEEN CURRENT ROW AND CURRENT ROW
 ) AS frame_revenue
FROM daily_sales
ORDER BY market, day;
market  day         revenue  previous_revenue  frame_revenue
GB      2026-11-02  80       NULL              80
US      2026-11-01  100      NULL              100
US      2026-11-02  150      100               150
US      2026-11-10  200      150               200

SUM respects this frame and returns the current row’s amount. LAG still returns the preceding US record, including $150 on November 10. The GB partition has no earlier row, so its previous value is NULL. Changing the frame bounds does not turn a row-offset comparison into a calendar-day comparison.

Enforce the actual date rule

Suppose the requested metric is revenue on the immediately preceding calendar day. First obtain the previous observed date and revenue. Then expose the amount only when the date difference is exactly one day. This explicit eligibility rule preserves the current row even when no valid comparison exists.

WITH previous AS (
 SELECT market, day, revenue_usd,
  LAG(day) OVER (
   PARTITION BY market ORDER BY day
  ) AS previous_day,
  LAG(revenue_usd) OVER (
   PARTITION BY market ORDER BY day
  ) AS previous_revenue
 FROM daily_sales
)
SELECT market, day, revenue_usd,
 CASE WHEN julianday(day) - julianday(previous_day) = 1
      THEN previous_revenue END AS prior_calendar_day_revenue
FROM previous
ORDER BY market, day;

The November 2 US row has prior-calendar-day revenue of $100. The November 10 US row has NULL because its preceding observed date is eight days earlier. Do not substitute zero unless a separate, documented completeness rule establishes a real zero. This query uses SQLite date functions and ISO calendar dates; it is not intended for arbitrary local timestamp strings.

What needs to be true about the input?

The example assumes market and date form a unique key. If there are multiple order rows per day, aggregate them to that grain first; otherwise the previous row may be another order from the same day. If the business needs a previous transaction instead, define a deterministic timestamp and transaction-ID ordering. Those are different analytical questions.

The order inside OVER determines the previous row. The final ORDER BY controls display order and cannot repair an ambiguous window order. Missing market identifiers also need an explicit handling policy before partitioning. Preserve the original data and report excluded or unresolved records separately.

How should you test the report?

Include a first row in each market, adjacent dates, a multi-day gap and multiple markets. The executable example above was run in SQLite: all expected LAG and SUM values were checked, along with the adjacent-day result and the missing comparison across the gap. In a production dataset, add a uniqueness assertion for the daily key before computing the window.

For calendar completion rather than a simple eligibility test, see LAG with missing reporting periods. For a function that does depend on the frame, compare LAST_VALUE and the current-row frame.

Source and dialect limits

SQLite documents the distinction in its official window-function reference. This article describes SQLite behavior. Check your database’s documentation before copying frame syntax to another engine; some systems restrict which clauses a navigation function accepts. In an interview, name the engine, define the comparison grain and explain the time eligibility rule before presenting a previous-value metric.

Leave a Comment

✉ WhatsApp