A sensor reports every hour, except when it does not. Downstream wants a reading on every row, so a gap takes the last value the sensor actually sent.
Rows do not arrive in timestamp order.
One row for every input row, same sensor_id and reading_ts, with:
reading_c — the reading if there is one, otherwise the most recent non-null reading from the same sensor at an earlier timestamp, and null if that sensor has not reported yet| sensor_id | reading_ts | reading_c |
|---|---|---|
| 1 | 2024-01-01T00:00:00Z | 10.0 |
| 1 | 2024-01-01T01:00:00Z | 10.0 |
| 1 | 2024-01-01T02:00:00Z | 10.0 |
| 1 | 2024-01-01T03:00:00Z | 12.5 |
readings| reading_c | sensor_id | reading_ts |
|---|---|---|
| 10 | 1 | 2024-01-01T00:00:00Z |
| null | 1 | 2024-01-01T01:00:00Z |
| null | 1 | 2024-01-01T02:00:00Z |
| 12.5 | 1 | 2024-01-01T03:00:00Z |
| sensor_id | reading_ts | reading_c |
|---|---|---|
| 1 | 2024-01-01T00:00:00Z | 10 |
| 1 | 2024-01-01T01:00:00Z | 10 |
| 1 | 2024-01-01T02:00:00Z | 10 |
| 1 | 2024-01-01T03:00:00Z | 12.5 |
readings| reading_c | sensor_id | reading_ts |
|---|---|---|
| null | 1 | 2024-01-01T00:00:00Z |
| null | 1 | 2024-01-01T01:00:00Z |
| 5 | 1 | 2024-01-01T02:00:00Z |
| sensor_id | reading_ts | reading_c |
|---|---|---|
| 1 | 2024-01-01T00:00:00Z | null |
| 1 | 2024-01-01T01:00:00Z | null |
| 1 | 2024-01-01T02:00:00Z | 5 |
Community-reported interview topic. Not an official company question and no affiliation is implied.
Run against the public tests, or submit to score against all of them.