How would you design a relational data model to track the lifecycle of an autonomous drone at Anduril from manufacturing, through testing, to active deployment?
Write a PostgreSQL query against the model below that identifies drones manufactured, fully tested with passing results, and currently active at their latest deployment. Ignore deployments with a NULL active_since value.
Output
- One row per qualifying drone with
drone_id, serial_number, model, manufactured_at, latest_test_at, passed_tests, deployment_site, active_since, and days_to_active_deployment.
- Include only drones with manufacturing records, at least one test, all tests passing, and a valid active deployment.
- Order by
active_since ascending, then drone_id ascending.
Output details
- Use the earliest non-NULL
event_timestamp from MANUFACTURED events as manufactured_at.
- Set
latest_test_at to the maximum non-NULL completed_at across all test runs.
- Count every test row in
passed_tests, requiring result exactly equal to PASS; NULL or other results disqualify the drone.
- Select the active deployment with greatest
active_since, breaking ties by greatest deployment_id.
- Calculate
days_to_active_deployment as the date difference between active_since and the date portion of manufactured_at.
