Deolite Data Engineer Interview Questions (2026)

These are two different window operations, and the question is really checking that you know the difference. `LAG` looks back a fixed number of rows; a cumulative total sums everything from the start of the partition to the current row. ```sql SELECT id, surface_area, LAG(surface_area, 1, 0) OVER (ORDER BY id) AS previous_area, SUM(surface_area) OVER ( ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS cumulative_area FROM surfaces ORDER BY id; ``` ### How LAG works `LAG(column, offset, default)` returns the value from `offset` rows earlier in the ordered partition. The third argument is what makes this robust: without it, the first row's `LAG` is NULL, and any arithmetic on NULL yields NULL. Supplying `0` means `surface_area + LAG(surface_area, 1, 0)` gives a real number on row one instead of a hole. `LEAD` is the same function looking forward. Strictly, `LAG` alone cannot produce a cumulative total — it only reaches back one row. A true running total needs `SUM` with a frame, which is why both appear above. ### The frame clause `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` states explicitly that the frame is row-based. The default when you write only `ORDER BY id` is `RANGE`, which is value-based: rows sharing the same `id` collapse into one frame and all receive the same total. With a unique `id` the results are identical, but if the ordering column ever contains duplicates the two diverge. Being explicit removes the ambiguity. ### Per-group totals Add `PARTITION BY` to restart the accumulation for each group, for example `PARTITION BY material_type ORDER BY id`. ### Ordering matters A cumulative total is only meaningful over a deterministic order. If `id` is not unique, add a tiebreaker, otherwise the same query can return different numbers on different runs. In the interview, also mention that `LAG` is the standard way to compute period-over-period change: `surface_area - LAG(surface_area) OVER (ORDER BY id)`.