Automotive SQL Examples

Concept

Automotive and embedded engineering generate large volumes of structured data: vehicle builds, ECU inventories, software versions, diagnostic trouble codes (DTCs), CAN bus databases (message and signal definitions similar to DBC files), captured bus traffic, and HIL/vehicle test results. SQL is used by test engineers, diagnostics engineers, validation teams, and data engineers to trace issues across all of these.

Domain primer:

17-character Vehicle Identification Number Electronic Control Unit (VCU, BMS, BCM, ECM, ADAS...)

Diagnostic Trouble Code, e.g., P0301. First letter: P = powertrain, C = chassis, B = body, U = network/communication

Controller Area Network. A message (frame) has a CAN ID, a DLC (data length), and a cycle time; it carries signals defined by start bit, length, factor, offset, unit Hardware-in-the-Loop test bench Installing a software version on an ECU

Why It Matters

Typical engineering questions — "Which vehicles still run the old BMS software?", "Is the DTC rate higher on version 2.3.1?", "Which CAN messages are dropping frames?", "Which test cases fail on the latest release?" — are answered with exactly the joins, aggregations, and window functions from earlier modules.

Syntax

Standard SQL with PostgreSQL date/time functions; dialect notes are included where they matter.

Example

36.2 Sample data 36.3 Vehicle lookup ADAS, BCM, ECM Partial VIN search (last 6 digits, as service staff often use):

36.4 ECU relationships and current software (software version tracking)

Current software on each ECU = the latest installation per ECU:

Outdated ECUs — current version is not the latest RELEASED version for that ECU type:

(ADAS 0.9.5 is BETA, so it has no RELEASED version and does not appear; decide explicitly how your fleet report should treat beta software.) Software update history of one ECU:

Version-label caution: never sort version strings alphabetically ('1.10.0' < '1.9.0' as text). Store major/minor/patch as integers, or sort by release date.

36.5 DTC reporting DTC frequency with catalog details:

Door ajar switch circuit Cylinder 1 misfire detected Replace hybrid/EV battery pack Radar sensor misalignment Lost communication with ECM/PCM

Active DTCs by severity (dashboard tile):

DTCs by system (first letter of the code):

Time to clear (repair turnaround) for cleared DTCs:

DTCs joined with the software version running on the ECU at the time of detection (point-in-time join):

36.6 CAN message statistics Signal definitions of a message (DBC-style view), with the CAN ID shown in hexadecimal:

(Physical value = raw × factor + offset. Display precision of DECIMAL columns varies by DBMS.) Frame-loss statistics per capture:

Aggregated per message, flagging loss above 1%:

Expected frames check: a 10 ms cycle over 60 s gives 60 / 0.010 = 6000 frames, which matches frames_expected. Nominal bus load contribution of each message on its bus (frames per second):

36.7 Test result analysis Overall results:

Pass rate per software version (excluding BLOCKED from the denominator):

Latest result per test case per software version (a re-test supersedes the earlier failure):

36.8 Failed test analysis Failed tests with vehicle and software context:

Regen braking torque limit Cell overvoltage protection AEB pedestrian detection

Test cases that failed on more than one software version or vehicle (systemic issues):

Correlate failed tests with active DTCs on the same vehicle and ECU type:

Fleet health summary per vehicle (combining several sources — aggregate each source first to avoid fan-out):

How can_loss_pct is derived per vehicle (frames lost ÷ frames expected across all of the vehicle's captures — summing frames, not averaging per-capture percentages):

Verifying report numbers by hand like this is exactly the check a tester should perform on dashboard queries.

vehicle 1 ──── N ecu 1 ──── N ecu_software N ──── 1 software_version
   |                |
   | 1              | 1
   | N              | N
diagnostic_dtc N ──── 1 dtc_catalog
   |
vehicle 1 ──── N can_bus_capture N ──── 1 can_message 1 ──── N can_signal
vehicle 1 ──── N test_result N ──── 1 software_version

CREATE TABLE vehicle (
    vehicle_id  INT          PRIMARY KEY,
    vin         CHAR(17)     NOT NULL UNIQUE,
    model       VARCHAR(40)  NOT NULL,
    model_year  SMALLINT     NOT NULL,
    market      CHAR(2)      NOT NULL,
    build_date  DATE         NOT NULL
);

CREATE TABLE ecu (
    ecu_id      INT          PRIMARY KEY,
    vehicle_id  INT          NOT NULL REFERENCES vehicle(vehicle_id),
    ecu_code    VARCHAR(10)  NOT NULL,          -- VCU, BMS, BCM, ECM, ADAS
    supplier    VARCHAR(40)  NOT NULL,
    serial_no   VARCHAR(30)  NOT NULL UNIQUE,
    CONSTRAINT uq_ecu_per_vehicle UNIQUE (vehicle_id, ecu_code)
);

CREATE TABLE software_version (
    sw_version_id  INT          PRIMARY KEY,
    ecu_code       VARCHAR(10)  NOT NULL,
    version_label  VARCHAR(20)  NOT NULL,
    release_date   DATE         NOT NULL,
    release_status VARCHAR(10)  NOT NULL CHECK (release_status IN ('RELEASED', 'BETA', 'WITHDRAWN')),
    CONSTRAINT uq_sw_version UNIQUE (ecu_code, version_label)
);

CREATE TABLE ecu_software (                   -- installation (flashing) history
    install_id     INT        PRIMARY KEY,
    ecu_id         INT        NOT NULL REFERENCES ecu(ecu_id),
    sw_versi
…

INSERT INTO vehicle VALUES
(1, 'EHBAUREV250000001', 'Aurora EV',  2025, 'IN', DATE '2025-02-10'),
(2, 'EHBAUREV250000002', 'Aurora EV',  2025, 'IN', DATE '2025-03-05'),
(3, 'EHBTITAN260000003', 'Titan SUV',  2026, 'EU', DATE '2025-11-20'),
(4, 'EHBAURIC250000004', 'Aurora ICE', 2025, 'IN', DATE '2025-03-28');

INSERT INTO ecu VALUES
(1,  1, 'VCU',  'Supplier-A', 'VCU-A-0001'),
(2,  1, 'BMS',  'Supplier-B', 'BMS-B-0001'),
(3,  1, 'BCM',  'Supplier-C', 'BCM-C-0001'),
(4,  2, 'VCU',  'Supplier-A', 'VCU-A-0002'),
(5,  2, 'BMS',  'Supplier-B', 'BMS-B-0002'),
(6,  2, 'BCM',  'Supplier-C', 'BCM-C-0002'),
(7,  3, 'ECM',  'Supplier-D', 'ECM-D-0003'),
(8,  3, 'BCM',  'Supplier-C', 'BCM-C-0003'),
(9,  3, 'ADAS', 'Supplier-E', 'ADS-E-0003'),
(10, 4, 'ECM',  'Supplier-D', 'ECM-D-0004'),
(11, 4, 'BCM',  'Supplier-C', 'BCM-C-0004');

INSERT INTO software_version VALUES
(1, 'VCU',  '1.0.0', DATE '2025-01-15', 'RELEASED'),
(2, 'VCU',  '1.1.0', DATE '2025-06-01', 'RELEASED'),
(3, 'BMS',  '2.3.1', DATE '2025-02-01', 'RELEASED'),
(4, 'BMS',  '2.4.0', DATE '2025-07-10', 'RELEASED'),
(5, 'BCM',  '5.0.2', DATE '2024-12-01', 'RELEASED'),
(6, 'ECM',  '3.2.0', DATE '2025-05-20', 'RELEASED'),
(7, 'ADAS', '0.
…

SELECT v.vin, v.model, v.model_year, v.market,
       COUNT(e.ecu_id) AS ecu_count,
       STRING_AGG(e.ecu_code, ', ' ORDER BY e.ecu_code) AS ecus
FROM vehicle v
LEFT JOIN ecu e ON e.vehicle_id = v.vehicle_id
WHERE v.vin = 'EHBTITAN260000003'
GROUP BY v.vehicle_id, v.vin, v.model, v.model_year, v.market;

SELECT vin, model FROM vehicle WHERE vin LIKE '%000002';     -- EHBAUREV250000002, Aurora EV

WITH current_sw AS (
    SELECT es.ecu_id, es.sw_version_id, es.installed_at,
           ROW_NUMBER() OVER (PARTITION BY es.ecu_id ORDER BY es.installed_at DESC) AS rn
    FROM ecu_software es
)
SELECT v.vin, e.ecu_code, sv.version_label, cs.installed_at
FROM current_sw cs
JOIN ecu e               ON e.ecu_id = cs.ecu_id
JOIN vehicle v           ON v.vehicle_id = e.vehicle_id
JOIN software_version sv ON sv.sw_version_id = cs.sw_version_id
WHERE cs.rn = 1
ORDER BY v.vin, e.ecu_code;

WITH current_sw AS (
    SELECT ecu_id, sw_version_id,
           ROW_NUMBER() OVER (PARTITION BY ecu_id ORDER BY installed_at DESC) AS rn
    FROM ecu_software
),
latest_release AS (
    SELECT ecu_code, sw_version_id, version_label,
           ROW_NUMBER() OVER (PARTITION BY ecu_code ORDER BY release_date DESC) AS rn
    FROM software_version
    WHERE release_status = 'RELEASED'
)
SELECT v.vin, e.ecu_code, cur.version_label AS installed, lr.version_label AS latest
FROM current_sw cs
JOIN ecu e                  ON e.ecu_id = cs.ecu_id
JOIN vehicle v              ON v.vehicle_id = e.vehicle_id
JOIN software_version cur   ON cur.sw_version_id = cs.sw_version_id
JOIN latest_release lr      ON lr.ecu_code = e.ecu_code AND lr.rn = 1
WHERE cs.rn = 1
  AND cs.sw_version_id <> lr.sw_version_id
ORDER BY v.vin, e.ecu_code;

SELECT sv.version_label, es.installed_at,
       LAG(sv.version_label) OVER (ORDER BY es.installed_at) AS previous_version
FROM ecu_software es
JOIN software_version sv ON sv.sw_version_id = es.sw_version_id
WHERE es.ecu_id = 1
ORDER BY es.installed_at;

SELECT d.dtc_code, c.description, c.severity,
       COUNT(*)                                        AS occurrences,
       COUNT(DISTINCT d.vehicle_id)                    AS vehicles_affected,
       SUM(CASE WHEN d.status = 'ACTIVE' THEN 1 ELSE 0 END) AS active_now
FROM diagnostic_dtc d
JOIN dtc_catalog c ON c.dtc_code = d.dtc_code
GROUP BY d.dtc_code, c.description, c.severity
ORDER BY occurrences DESC, d.dtc_code;

SELECT c.severity, COUNT(*) AS active_dtcs
FROM diagnostic_dtc d
JOIN dtc_catalog c ON c.dtc_code = d.dtc_code
WHERE d.status = 'ACTIVE'
GROUP BY c.severity
ORDER BY CASE c.severity WHEN 'HIGH' THEN 1 WHEN 'MEDIUM' THEN 2 ELSE 3 END;

Common Mistakes

Sorting software versions as strings.

Joining DTCs to the current software instead of the software installed at detection time.

Joining DTCs, tests, and CAN captures in one step and multiplying counts (fan-out); aggregate each first.

Averaging per-capture loss percentages instead of summing frames (captures with different expected counts would be weighted incorrectly).

Storing CAN IDs as text in mixed formats ('0x1A0', '1A0', '416'); store integers and format on output.

Using offset as a column name (reserved word in several DBMSs).

Best Practices

Keep installation history (ecu_software) rather than overwriting a "current version" column; derive current state with window functions or a view.

Maintain a DTC catalog table and reference it with a foreign key.

Index by (vehicle, time) for diagnostic events and by (version, test case, time) for test results.

Build views such as v_ecu_current_software and v_fleet_health for dashboards.

For raw CAN logs (millions of frames), use time-partitioned tables or a time-series/columnar store and keep summaries in the relational database.

Interview Relevance

High for automotive, embedded, and validation roles: "How would you find all vehicles with outdated ECU software?", "How would you correlate test failures with DTCs?", "Design a schema for software version tracking", "How do you compute frame loss?".

Practice Questions

List vehicles that have at least one HIGH-severity active DTC.

For each ECU code, count how many ECUs run each software version.

Find test cases that have never passed on any version.

Find the average duration of PASS tests per test level (HIL vs VEHICLE).

Find DTCs detected within 7 days after a software installation on the same ECU.

Write a view v_ecu_current_software.