SQL for Average Flights per Crew | Dataford Interview Questions

Write a SQL query to find the average number of flights per crew member over the last year at Atlas Air.

Use the provided crew member and flight assignment data. Include every crew member in the calculation, including members with no qualifying flights during the period.

Output

Return one row with a column named average_flights_per_crew_member.
Calculate the average from each crew member's distinct flight count during the rolling one-year period ending today.
Round the result to two decimal places.

Schema

crew_members

Column	Type	Description
crew_member_idPK	INT	Unique identifier for a crew member
crew_member_name	VARCHAR(100)	Crew member's full name
crew_role	VARCHAR(30)	Operational role, such as pilot or flight engineer
base_code	VARCHAR(10)	Primary Atlas Air operating base code

crew_flight_assignments

Column	Type	Description
assignment_idPK	INT	Unique identifier for an assignment record
crew_member_id	INT	Assigned crew member identifier
flight_id	VARCHAR(20)	Atlas Air flight identifier
flight_date	DATE	Scheduled or operated flight date
assignment_status	VARCHAR(20)	Assignment state

Your question is SQL for Average Flights per Crew. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem