Forward-fill the last known reading

SQL data engineering interview problem. Difficulty: advanced. Pattern: Window Functions. About 16 minutes. Part of the Pro drill bank.

Sensors often miss a reading and the value is stored as NULL. For many reports you want to fill each gap with the last value you did know. This is called a forward fill. sensor_readings has device_id, reading_ts and reading. For every row, return the original reading and a new column filled_reading: the most recent reading that is not NULL, for the same device, at or before the row's reading_ts. If a device has no earlier non-NULL reading, filled_reading stays NULL. The rows are stored in no special time order, so order by time yourself. Return device_id, reading_ts, reading and filled_reading, ordered by device_id and then reading_ts.

Requirements

• Keep every row. Do not remove the rows with a NULL reading.
• Fill a NULL with the latest non-NULL reading of the same device that came before it in time.
• Never take a value from another device.
• Return all 4 columns, ordered by device_id, then reading_ts.

Constraints

• Timestamps are unique within a device.
• The rows are not stored in time order, so the order must come from ORDER BY reading_ts inside the window.
• filled_reading can only look backwards, never forward.

Examples

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