On-time scorecard by airline and quarter
The group CEO reviews punctuality every quarter for the three airlines in the alliance: SkyMeridian, Orient Wings and Falcon Crest Air. A flight is on time when it departed no more than 15 minutes late (dep_delay_min <= 15, early departures count as on time). The airline of a flight is the airline that owns its aircraft in aircraft.airline. Cancelled flights are not part of punctuality.
Task: for completed flights, return airline, quarter (text such as 2025-Q3, built from the scheduled departure date), flights (count) and on_time_pct (share of flights that were on time, ROUND(x, 2)). Only keep airline and quarter groups with at least 5 flights. Sort by airline A to Z, then quarter 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.
Need a hint?
Months 1 to 3 are quarter 1. Integer division can turn a month number into a quarter number.
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.