Aggregate surface areas and calculate cumulative surface area using the LAG function.

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.

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).