How would you use a SQL join and conditional aggregation to identify connected vehicles that have experienced more than three distinct error codes in a single shift at Bosch?
Use the vehicles, vehicle_shifts, and vehicle_error_events tables. Return only qualifying connected vehicle shifts.
vehicle_id, shift_id, shift_date, and distinct_error_codesvehicle_id, then shift_id ascending| Column | Type | Description |
|---|---|---|
| vehicle_idPK | INT | Unique vehicle identifier |
| vehicle_model | VARCHAR(80) | Vehicle model name |
| is_connected | BOOLEAN | Whether the vehicle is connected to Bosch vehicle services |
| Column | Type | Description |
|---|---|---|
| shift_idPK | INT | Unique shift identifier |
| vehicle_id | INT | Vehicle assigned to the shift |
| shift_date | DATE | Calendar date of the shift |
| shift_name | VARCHAR(20) | Shift label |
| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Unique error event identifier |
| shift_id | INT | Shift during which the event occurred |
| error_code | VARCHAR(20) | Reported diagnostic error code |
| event_timestamp | TIMESTAMP | Time when the error was recorded |