Detect Duplicate Device Logs | Dataford Interview Questions

Write a SQL query to find duplicate records in a database table containing Axon device logs.

Treat records with the same device_id, event_type, logged_at, and status as duplicates. Exclude the unique log_id from duplicate matching.

Output

One row per duplicated record signature
Include device_id, event_type, logged_at, status, and duplicate_count
Include only signatures occurring more than once
Order by device_id, event_type, logged_at, and status ascending, with NULL statuses first

axon_device_logs

Column	Type	Description
log_idPK	INTEGER	Unique identifier for the log row
device_id	VARCHAR(20)	Axon device identifier
event_type	VARCHAR(40)	Type of device event
logged_at	TIMESTAMPTZ	Timestamp when the event was logged
status	VARCHAR(20)	Processing status of the log event

Your question is Detect Duplicate Device Logs. Start with the requirements and the one table 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.

Write a SQL query to find duplicate records in a database table containing Axon device logs.

Treat records with the same device_id, event_type, logged_at, and status as duplicates. Exclude the unique log_id from duplicate matching.

One row per duplicated record signature
Include device_id, event_type, logged_at, status, and duplicate_count
Include only signatures occurring more than once
Order by device_id, event_type, logged_at, and status ascending, with NULL statuses first

axon_device_logs

Column	Type	Description
log_idPK	INTEGER	Unique identifier for the log row
device_id	VARCHAR(20)	Axon device identifier
event_type	VARCHAR(40)	Type of device event
logged_at	TIMESTAMPTZ	Timestamp when the event was logged
status	VARCHAR(20)	Processing status of the log event

Your question is Detect Duplicate Device Logs. Start with the requirements and the one table 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.