Top Contributors to Diagnostic Trouble Codes | Dataford Interview Questions

Your question is Top Contributors to Diagnostic Trouble Codes. Start with the requirements and the two tables on the right.

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

Problem

General Motors needs to identify vehicle models contributing the most diagnostic trouble codes in a large sensor database. Write a PostgreSQL query that aggregates diagnostic events for January 2024 and returns the top three models.

Requirements

Join vehicles to diagnostic events while preserving vehicles with no matching events during the date range.
Use conditional aggregation to count Critical and Warning events separately, along with total events, affected vehicles, and distinct non-null DTC codes.
Exclude models with no in-range events, rank the remaining models by total events descending, and return the top three with deterministic ordering.

Schema

vehicles

Column	Type	Description
vehicle_idPK	INT	Unique vehicle identifier
model	VARCHAR(80)	General Motors vehicle model

diagnostic_events

Column	Type	Description
event_idPK	INT	Unique diagnostic event identifier
vehicle_id	INT	Vehicle associated with the event
dtc_code	VARCHAR(20)	Diagnostic trouble code
severity	VARCHAR(20)	Diagnostic event severity
event_timestamp	TIMESTAMP	Timestamp when the event was recorded