Carry the last good reading forward

For devices 1–10, return reading_id, device_id, recorded_at, value and filled_value: value when known, otherwise the device’s most recent earlier known value (by recorded_at, reading_id). Leading NULLs stay NULL.

what to return

Columns: reading_iddevice_idrecorded_atvaluefilled_value in this order
Rows: 13101 rows · any order
Skill: Fill gaps in a series with the last known value. Refresher: FIRST_VALUE, LAST_VALUE & frames →

expected result

Your query should return exactly these columns and values. Run it and compare.

expected result13101 rows · any order

reading_id	device_id	recorded_at	value	filled_value
59	1	2025-03-01 00:38:00	4.15	4.15
164	1	2025-03-01 01:38:00	3.19	3.19
271	1	2025-03-01 02:38:00	3.4	3.4

first 3 of 13101 rows

data you can query

IoT / Sensors dataset: the first 6 rows of each table. Under each column name: the kind of value it holds, and which table it links to. Your queries run on all rows.

sensor_readings10 columns · 150,425 rows

reading_idnumber · unique id	device_idnumber · links to devices	sequence_numbernumber	recorded_atdate & time	received_atdate & time	valuedecimal	battery_pctnumber	gateway_idnumber	signal_rssi_dbmnumber	quality_flagtext
1	17	16458	2025-03-01 00:00:00	2025-03-01 00:00:12	18.38	NULL	NULL	NULL	ok
2	7	25230	2025-03-01 00:00:00	2025-03-01 00:00:42	5.22	67	22	-81	ok
3	169	56856	2025-03-01 00:02:00	2025-03-01 00:02:53	40.03	NULL	NULL	NULL	ok
4	12	50947	2025-03-01 00:03:00	2025-03-01 00:03:17	36.84	NULL	NULL	NULL	ok
5	34	50357	2025-03-01 00:02:00	2025-03-01 00:03:17	40.22	NULL	NULL	NULL	ok
6	123	32417	2025-03-01 00:03:00	2025-03-01 00:03:32	38.37	NULL	NULL	NULL	ok

reading_idnumber · unique id: 1
device_idnumber · links to devices: 17
sequence_numbernumber: 16458
recorded_atdate & time: 2025-03-01 00:00:00
received_atdate & time: 2025-03-01 00:00:12
valuedecimal: 18.38
battery_pctnumber: NULL
gateway_idnumber: NULL
signal_rssi_dbmnumber: NULL
quality_flagtext: ok

reading_idnumber · unique id: 2
device_idnumber · links to devices: 7
sequence_numbernumber: 25230
recorded_atdate & time: 2025-03-01 00:00:00
received_atdate & time: 2025-03-01 00:00:42
valuedecimal: 5.22
battery_pctnumber: 67
gateway_idnumber: 22
signal_rssi_dbmnumber: -81
quality_flagtext: ok

reading_idnumber · unique id: 3
device_idnumber · links to devices: 169
sequence_numbernumber: 56856
recorded_atdate & time: 2025-03-01 00:02:00
received_atdate & time: 2025-03-01 00:02:53
valuedecimal: 40.03
battery_pctnumber: NULL
gateway_idnumber: NULL
signal_rssi_dbmnumber: NULL
quality_flagtext: ok

reading_idnumber · unique id: 4
device_idnumber · links to devices: 12
sequence_numbernumber: 50947
recorded_atdate & time: 2025-03-01 00:03:00
received_atdate & time: 2025-03-01 00:03:17
valuedecimal: 36.84
battery_pctnumber: NULL
gateway_idnumber: NULL
signal_rssi_dbmnumber: NULL
quality_flagtext: ok

reading_idnumber · unique id: 5
device_idnumber · links to devices: 34
sequence_numbernumber: 50357
recorded_atdate & time: 2025-03-01 00:02:00
received_atdate & time: 2025-03-01 00:03:17
valuedecimal: 40.22
battery_pctnumber: NULL
gateway_idnumber: NULL
signal_rssi_dbmnumber: NULL
quality_flagtext: ok

reading_idnumber · unique id: 6
device_idnumber · links to devices: 123
sequence_numbernumber: 32417
recorded_atdate & time: 2025-03-01 00:03:00
received_atdate & time: 2025-03-01 00:03:32
valuedecimal: 38.37
battery_pctnumber: NULL
gateway_idnumber: NULL
signal_rssi_dbmnumber: NULL
quality_flagtext: ok

first 6 of 150,425 rows · your queries use all of them

solution.sql

Hints and the answer are always available. Using them counts the question as solved with help.