Reviewed by Aditya Kumar · Last reviewed 2026-03-24
SELECT id, value, LAG(value) OVER (ORDER BY timestamp) AS prev_value, value - LAG(value) OVER (ORDER BY timestamp) AS delta FROM table ORDER BY timestamp. For change detection: SELECT *, LAG(column) OVER (PARTITION BY id ORDER BY updated_at) AS prev, CASE WHEN column !=...
SELECT id, value, LAG(value) OVER (ORDER BY timestamp) AS prev_value, value - LAG(value) OVER (ORDER BY timestamp) AS delta FROM table ORDER BY timestamp. For change detection: SELECT *, LAG(column) OVER (PARTITION BY id ORDER BY updated_at) AS prev, CASE WHEN column != LAG(column) OVER (...) THEN 'changed' END AS change_flag. Why: LAG gives previous row; compare to detect changes. Scalability: Partition by id for per-entity; ensure timestamp indexed.
Red Flag: Full scan when incremental CDC is available. Pro-Move: 'We use LAG for audit; real CDC uses Debezium for source capture.'
According to DataEngPrep.tech, this is one of the most frequently asked SQL interview questions, reported at 1 company. DataEngPrep.tech maintains an editor-reviewed database of 1,863 data engineering interview questions across 7 categories.