SQL data engineering interview problem. Difficulty: advanced. Pattern: Window Functions. About 16 minutes. Part of the Pro drill bank.
sensor_readings has gaps: some reading values are NULL. For each row return the original reading and filled_reading, which is the most recent non-NULL reading of the same device at or before that row's reading_ts. If a device has no earlier non-NULL reading, filled_reading stays NULL. The rows are stored out of time order. Columns: device_id, reading_ts, reading, filled_reading. Order by device_id, reading_ts.
Input: sensor_readings device_id | reading_ts | reading d1 | 2024-08-01 09:30:00 | 11 d1 | 2024-08-01 09:00:00 | 10.5 d1 | 2024-08-01 09:20:00 | NULL d1 | 2024-08-01 09:10:00 | NULL d1 | 2024-08-01 09:40:00 | NULL d2 | 2024-08-01 09:00:00 | NULL d2 | 2024-08-01 09:10:00 | 7.5 d2 | 2024-08-01 09:20:00 | NULL d3 | 2024-08-01 09:00:00 | 3.25 Output: device_id | reading_ts | reading | filled_reading d1 | 2024-08-01 09:00:00 | 10.5 | 10.5 d1 | 2024-08-01 09:10:00 | NULL | 10.5 d1 | 2024-08-01 09:20:00 | NULL | 10.5 d1 | 2024-08-01 09:30:00 | 11 | 11 d1 | 2024-08-01 09:40:00 | NULL | 11 d2 | 2024-08-01 09:00:00 | NULL | NULL d2 | 2024-08-01 09:10:00 | 7.5 | 7.5 d2 | 2024-08-01 09:20:00 | NULL | 7.5 d3 | 2024-08-01 09:00:00 | 3.25 | 3.25 Why this passes: d1: 10.5, then two NULLs filled with 10.5, then 11.0 and a final NULL filled with 11.0. d2: the first NULL has nothing before it and stays NULL, 7.5 fills the next gap. d3 has one value.
Input: sensor_readings device_id | reading_ts | reading d1 | 2024-08-01 09:00:00 | 1.5 d1 | 2024-08-01 09:10:00 | NULL d1 | 2024-08-01 09:20:00 | 2.5 Output: device_id | reading_ts | reading | filled_reading d1 | 2024-08-01 09:00:00 | 1.5 | 1.5 d1 | 2024-08-01 09:10:00 | NULL | 1.5 d1 | 2024-08-01 09:20:00 | 2.5 | 2.5 Why this passes: A single NULL between two readings.
Topics: lakebench, sql, forward fill, null, time series.
More SQL interview questions · All interview problems · Learn data engineering
Interview-style drill: Replace NULL readings with the last non-NULL value of the same device by time.
`sensor_readings` has gaps: some `reading` values are NULL. For each row return the original `reading` and `filled_reading`, which is the most recent non-NULL reading of the **same device** at or before that row's `reading_ts`. If a device has no earlier non-NULL reading, `filled_reading` stays NULL. The rows are stored out of time order. Columns: `device_id`, `reading_ts`, `reading`, `filled_reading`. Order by `device_id`, `reading_ts`.