Handle Missing Values and Datetimes | Dataford Interview Questions

How do you handle missing values and inconsistent datetime formats in a dataset?

Write a PostgreSQL query that standardizes supported event timestamp formats, identifies invalid or missing timestamps, and flags other missing values without removing source records.

Output

One row per event, ordered by event_id.
Return event_id, normalized vehicle_id, source_name, normalized_event_at, datetime_status, mileage_km, and missing_value_status.
Preserve all events, including records with missing or invalid values.

Schema

raw_vehicle_events

Column	Type	Description
event_idPK	INT	Unique event identifier
vehicle_id	VARCHAR(20)	Vehicle identifier from the source feed
source_id	INT	Source system identifier
raw_event_time	VARCHAR(40)	Event timestamp received as text
mileage_km	INT	Vehicle mileage in kilometers

event_sources

Column	Type	Description
source_idPK	INT	Unique source system identifier
source_name	VARCHAR(50)	Source system name

Your question is Handle Missing Values and Datetimes. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem