Find Overlapping Anomalous Coordinates

MediumSQL · PostgreSQL

Problem

Zoox engineers want to identify locations where anomalous path-planning adjustments affected multiple autonomous vehicles. Because GPS readings can differ slightly, treat coordinates rounded to four decimal places as the same location.

Write a PostgreSQL query to identify overlapping anomalous coordinates from the provided tables.

Requirements

Include only path-planning events marked as anomalous with non-null latitude and longitude.
Join events to known Zoox autonomous vehicles and group coordinates after rounding latitude and longitude to four decimal places.
Return locations associated with at least two distinct vehicles, along with the number of distinct vehicles, anomalous adjustments, and a comma-separated list of vehicle IDs.
Order results by rounded latitude and longitude.

Schema

autonomous_vehicles

Column	Type	Description
vehicle_idPK	INT	Unique identifier for an autonomous vehicle
vehicle_name	VARCHAR(80)	Internal Zoox vehicle name
fleet_status	VARCHAR(30)	Current operating status of the vehicle

path_planning_events

Column	Type	Description
event_idPK	INT	Unique path-planning event identifier
vehicle_id	INT	Vehicle associated with the event
event_time	TIMESTAMP	Time when the planning adjustment occurred
latitude	NUMERIC(9,6)	Recorded latitude
longitude	NUMERIC(9,6)	Recorded longitude
anomaly_flag	BOOLEAN	Whether the event was classified as anomalous
adjustment_type	VARCHAR(40)	Type of path-planning adjustment
adjustment_meters	NUMERIC(6,2)	Magnitude of the path adjustment in meters

Tablesautonomous_vehiclespath_planning_events