SQL Join and Category Aggregation | Dataford Interview Questions

Skydio uses flight-event data to monitor activity across its drone portfolio. Write a PostgreSQL query that joins drone models with their flight events and summarizes completed flight activity by category.

Requirements

Include every drone category, including categories with no qualifying flight events.
Consider only events from 2025-01-01 through 2025-03-31, inclusive.
Return the number of completed flights and total completed flight minutes for each category. Treat missing totals as zero.
Order the results alphabetically by category.

Schema

drone_models

Column	Type	Description
model_idPK	INT	Unique drone model identifier
model_name	VARCHAR(100)	Skydio drone model name
category	VARCHAR(50)	Operational category for the model

flight_events

Column	Type	Description
event_idPK	INT	Unique flight event identifier
model_id	INT	Referenced drone model identifier
event_date	DATE	Date of the flight event
status	VARCHAR(20)	Flight event status
flight_minutes	INT	Recorded flight duration in minutes

Whenever you're ready, walk me through your approach or go straight to the code. The requirements and the two tables are in the Question tab.

Problem

Requirements

Include every drone category, including categories with no qualifying flight events.
Consider only events from 2025-01-01 through 2025-03-31, inclusive.
Return the number of completed flights and total completed flight minutes for each category. Treat missing totals as zero.
Order the results alphabetically by category.

Schema

drone_models

Column	Type	Description
model_idPK	INT	Unique drone model identifier
model_name	VARCHAR(100)	Skydio drone model name
category	VARCHAR(50)	Operational category for the model

flight_events

Column	Type	Description
event_idPK	INT	Unique flight event identifier
model_id	INT	Referenced drone model identifier
event_date	DATE	Date of the flight event
status	VARCHAR(20)	Flight event status
flight_minutes	INT	Recorded flight duration in minutes

Whenever you're ready, walk me through your approach or go straight to the code. The requirements and the two tables are in the Question tab.