Modeling Mixed-Frequency Data | Dataford Interview Questions

How would you model a dataset that requires both high-frequency sensor updates and long-term historical analysis at Waymo?

For this exercise, write a SQL query that summarizes readings during the specified two-day period while retaining vehicles without qualifying readings. The result must also identify the most recent qualifying reading for each vehicle.

Output

One row per vehicle, ordered by vehicle_id ascending.
Columns: vehicle_id, vehicle_name, reading_count, average_speed_mps, maximum_temperature_c, and latest_reading_at.
Include all vehicles, including those with zero qualifying readings. Exclude readings outside the period and readings that do not belong to a listed vehicle.

Schema

vehicles

Column	Type	Description
vehicle_idPK	INT	Unique Waymo vehicle identifier
vehicle_name	VARCHAR(80)	Human-readable vehicle label
fleet_status	VARCHAR(30)	Current fleet status

sensor_readings

Column	Type	Description
reading_idPK	BIGINT	Unique sensor reading identifier
vehicle_id	INT	Vehicle associated with the reading
recorded_at	TIMESTAMPTZ	UTC timestamp when the reading was recorded
sensor_type	VARCHAR(40)	Sensor stream that produced the reading
speed_mps	NUMERIC(8,2)	Vehicle speed in meters per second
temperature_c	NUMERIC(6,2)	Sensor temperature in degrees Celsius

Your question is Modeling Mixed-Frequency Data. 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