medium · ~12 min
Telemetry from legacy weather sensors encodes missing data with sentinel values rather than NULL: failed measurements arrive with value = -999, and unidentified stations arrive as 'N/A', an empty string '', or NULL.
NULL
value = -999
'N/A'
''
Write a query summarizing readings per cleaned station:
station
'unknown'
readings
avg_value
Input contract: reading_id is unique and non-null in readings; station is a nullable string that may also contain 'N/A' or ''; value is a non-null float where -999 signals a failed reading.
reading_id
value
-999