How much of the fleet really flies each month?
The fleet manager is asked why the group owns 14 aircraft when some seem to sit idle. Every aircraft in the aircraft table belongs to the fleet, including ones that never fly. For each month, utilised aircraft are those that operated at least one completed flight. The board also wants a running total of completed flights and the month-on-month swing in aircraft used.
Task: for completed flights only, return one row per month that has at least one completed flight: month (YYYY-MM from sched_dep), flights, aircraft_used (distinct aircraft), fleet_utilised_pct (aircraft_used as a percent of all aircraft in the fleet table, ROUND(x, 2)), running_flights (cumulative flights from the first month up to and including this one) and change_vs_prev (aircraft_used minus the previous listed month's aircraft_used, NULL for the first month). Sort by month ascending.
The data you can query
Each table is shown with its column types and the first rows, so you can see exactly what the data looks like.
| id INTEGER | airline TEXT | model TEXT | seats INTEGER | delivered_year INTEGER |
|---|---|---|---|---|
| 1 | SkyMeridian | A320 | 180 | 2017 |
| 2 | SkyMeridian | A320 | 180 | 2019 |
| 3 | SkyMeridian | A321 | 200 | 2021 |
| 4 | SkyMeridian | B787-9 | 290 | 2018 |
| 5 | SkyMeridian | A350-900 | 320 | 2022 |
| 6 | SkyMeridian | B787-9 | 290 | 2020 |
Showing the first 6 of 14 rows.
| code TEXT | name TEXT | city TEXT | country TEXT |
|---|---|---|---|
| JFK | John F. Kennedy International | New York | USA |
| LHR | Heathrow | London | United Kingdom |
| DXB | Dubai International | Dubai | UAE |
| SIN | Changi | Singapore | Singapore |
| LHE | Allama Iqbal International | Lahore | Pakistan |
| ISB | Islamabad International | Islamabad | Pakistan |
Showing the first 6 of 12 rows.
| id INTEGER | flight_id INTEGER | passenger_id INTEGER | cabin TEXT | fare_usd INTEGER |
|---|---|---|---|---|
| 1 | 1 | 14 | Economy | 230 |
| 2 | 1 | 38 | Economy | 180 |
| 3 | 2 | 1 | Economy | 380 |
| 4 | 2 | 22 | Economy | 400 |
| 5 | 2 | 45 | Economy | 445 |
| 6 | 3 | 1 | Economy | 450 |
Showing the first 6 of 557 rows.
| id INTEGER | flight_no TEXT | route_id INTEGER | aircraft_id INTEGER | sched_dep TEXT | dep_delay_min INTEGER | status TEXT |
|---|---|---|---|---|---|---|
| 1 | FC185 | 3 | 12 | 2025-01-02 07:15:00 | 0 | Completed |
| 2 | OW370 | 8 | 10 | 2025-01-03 05:30:00 | 40 | Completed |
| 3 | SM489 | 35 | 5 | 2025-01-03 08:00:00 | 25 | Completed |
| 4 | SM415 | 33 | 6 | 2025-01-03 14:45:00 | 10 | Completed |
| 5 | OW592 | 14 | 9 | 2025-01-08 16:00:00 | 50 | Completed |
| 6 | SM222 | 4 | 2 | 2025-01-09 08:45:00 | 35 | Completed |
Showing the first 6 of 260 rows.
| id INTEGER | name TEXT | home_country TEXT | loyalty_tier TEXT |
|---|---|---|---|
| 1 | Ayesha Khan | Pakistan | Silver |
| 2 | Bilal Okafor | Australia | Silver |
| 3 | Chen Silva | Germany | NULL |
| 4 | Diego Tanaka | Australia | Silver |
| 5 | Elena Weber | Germany | NULL |
| 6 | Farid Nguyen | Australia | Gold |
Showing the first 6 of 45 rows.
| id INTEGER | origin TEXT | dest TEXT | distance_km INTEGER |
|---|---|---|---|
| 1 | LHE | DXB | 2100 |
| 2 | DXB | LHE | 2100 |
| 3 | ISB | DXB | 2300 |
| 4 | DXB | ISB | 2300 |
| 5 | DXB | LHR | 5500 |
| 6 | LHR | DXB | 5500 |
Showing the first 6 of 40 rows.
Your query runs on SQLite. Write a single SELECT; use LIMIT instead of TOP.
Log in or create a free account to run your code, submit and earn points.
Run your code to see the output here. Ctrl + Enter also runs it.