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.
