runtimetask Conditional aggregation · medium
Reliability bands
For each airline, return airline_code, on_time_pct (operated flights arriving no more than 15 minutes late among operated, non-diverted flights, 0–100, 1 decimal) and band: 'high' at 85 or above, 'medium' at 75 or above, otherwise 'low'.
what to return
- Columns
airline_codeon_time_pctbandin this order- Rows
- 14 rows · any order
- Skill
- Combine a rate with CASE bands. Refresher: Make decisions with CASE →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result14 rows · any order
| airline_code | on_time_pct | band |
|---|---|---|
| AA | 79.6 | medium |
| AS | 81.6 | medium |
| B6 | 76.1 | medium |
first 3 of 14 rows
data you can query
U.S. Flights dataset: the first 6 rows of each table. Under each column name: the kind of value it holds, and which table it links to. Your queries run on all rows.
flights25 columns · 124,460 rows
| flight_idnumber · unique id | flight_datedate · links to calendar | airline_codetext · links to airlines | flight_numbernumber | tail_numbertext | origintext · links to airports | destinationtext · links to airports | route_idnumber · links to routes | scheduled_departuredate & time | actual_departuredate & time | departure_delaynumber | taxi_out_minutesnumber | scheduled_arrivaldate & time | actual_arrivaldate & time | arrival_delaynumber | air_time_minutesnumber | distance_milesnumber | cancellednumber | cancellation_codetext · links to cancellation_reasons | divertednumber | carrier_delaynumber | weather_delaynumber | nas_delaynumber | security_delaynumber | late_aircraft_delaynumber |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 2025-01-01 | AA | 2158 | N960NN | SMF | ORD | 5042 | 2025-01-01 00:25 | 2025-01-01 00:37 | 12 | 10 | 2025-01-01 06:34 | 2025-01-01 06:18 | -16 | 202 | 1781 | 0 | NULL | 0 | NULL | NULL | NULL | NULL | NULL |
| 2 | 2025-01-01 | AS | 150 | N524AS | ANC | LAX | 90 | 2025-01-01 00:44 | 2025-01-01 00:42 | -2 | 10 | 2025-01-01 07:08 | 2025-01-01 06:44 | -24 | 281 | 2345 | 0 | NULL | 0 | NULL | NULL | NULL | NULL | NULL |
| 3 | 2025-01-01 | AA | 2319 | N861NN | PHX | ORD | 4078 | 2025-01-01 00:45 | 2025-01-01 00:39 | -6 | 17 | 2025-01-01 05:16 | 2025-01-01 04:44 | -32 | 160 | 1440 | 0 | NULL | 0 | NULL | NULL | NULL | NULL | NULL |
| 4 | 2025-01-01 | UA | 2232 | N24516 | SFO | ORD | 4820 | 2025-01-01 00:59 | 2025-01-01 00:56 | -3 | 31 | 2025-01-01 07:26 | 2025-01-01 07:23 | -3 | 214 | 1846 | 0 | NULL | 0 | NULL | NULL | NULL | NULL | NULL |
| 5 | 2025-01-01 | AA | 575 | N163AA | DFW | CLT | 1457 | 2025-01-01 05:00 | 2025-01-01 04:55 | -5 | 23 | 2025-01-01 08:32 | 2025-01-01 08:14 | -18 | 105 | 936 | 0 | NULL | 0 | NULL | NULL | NULL | NULL | NULL |
| 6 | 2025-01-01 | AA | 1196 | N338ST | EWR | MIA | 1808 | 2025-01-01 05:00 | 2025-01-01 04:53 | -7 | 22 | 2025-01-01 08:20 | 2025-01-01 08:10 | -10 | 151 | 1085 | 0 | NULL | 0 | NULL | NULL | NULL | NULL | NULL |
- flight_idnumber · unique id
- 1
- flight_datedate · links to calendar
- 2025-01-01
- airline_codetext · links to airlines
- AA
- flight_numbernumber
- 2158
- tail_numbertext
- N960NN
- origintext · links to airports
- SMF
- destinationtext · links to airports
- ORD
- route_idnumber · links to routes
- 5042
- scheduled_departuredate & time
- 2025-01-01 00:25
- actual_departuredate & time
- 2025-01-01 00:37
- departure_delaynumber
- 12
- taxi_out_minutesnumber
- 10
- scheduled_arrivaldate & time
- 2025-01-01 06:34
- actual_arrivaldate & time
- 2025-01-01 06:18
- arrival_delaynumber
- -16
- air_time_minutesnumber
- 202
- distance_milesnumber
- 1781
- cancellednumber
- 0
- cancellation_codetext · links to cancellation_reasons
- NULL
- divertednumber
- 0
- carrier_delaynumber
- NULL
- weather_delaynumber
- NULL
- nas_delaynumber
- NULL
- security_delaynumber
- NULL
- late_aircraft_delaynumber
- NULL
- flight_idnumber · unique id
- 2
- flight_datedate · links to calendar
- 2025-01-01
- airline_codetext · links to airlines
- AS
- flight_numbernumber
- 150
- tail_numbertext
- N524AS
- origintext · links to airports
- ANC
- destinationtext · links to airports
- LAX
- route_idnumber · links to routes
- 90
- scheduled_departuredate & time
- 2025-01-01 00:44
- actual_departuredate & time
- 2025-01-01 00:42
- departure_delaynumber
- -2
- taxi_out_minutesnumber
- 10
- scheduled_arrivaldate & time
- 2025-01-01 07:08
- actual_arrivaldate & time
- 2025-01-01 06:44
- arrival_delaynumber
- -24
- air_time_minutesnumber
- 281
- distance_milesnumber
- 2345
- cancellednumber
- 0
- cancellation_codetext · links to cancellation_reasons
- NULL
- divertednumber
- 0
- carrier_delaynumber
- NULL
- weather_delaynumber
- NULL
- nas_delaynumber
- NULL
- security_delaynumber
- NULL
- late_aircraft_delaynumber
- NULL
- flight_idnumber · unique id
- 3
- flight_datedate · links to calendar
- 2025-01-01
- airline_codetext · links to airlines
- AA
- flight_numbernumber
- 2319
- tail_numbertext
- N861NN
- origintext · links to airports
- PHX
- destinationtext · links to airports
- ORD
- route_idnumber · links to routes
- 4078
- scheduled_departuredate & time
- 2025-01-01 00:45
- actual_departuredate & time
- 2025-01-01 00:39
- departure_delaynumber
- -6
- taxi_out_minutesnumber
- 17
- scheduled_arrivaldate & time
- 2025-01-01 05:16
- actual_arrivaldate & time
- 2025-01-01 04:44
- arrival_delaynumber
- -32
- air_time_minutesnumber
- 160
- distance_milesnumber
- 1440
- cancellednumber
- 0
- cancellation_codetext · links to cancellation_reasons
- NULL
- divertednumber
- 0
- carrier_delaynumber
- NULL
- weather_delaynumber
- NULL
- nas_delaynumber
- NULL
- security_delaynumber
- NULL
- late_aircraft_delaynumber
- NULL
- flight_idnumber · unique id
- 4
- flight_datedate · links to calendar
- 2025-01-01
- airline_codetext · links to airlines
- UA
- flight_numbernumber
- 2232
- tail_numbertext
- N24516
- origintext · links to airports
- SFO
- destinationtext · links to airports
- ORD
- route_idnumber · links to routes
- 4820
- scheduled_departuredate & time
- 2025-01-01 00:59
- actual_departuredate & time
- 2025-01-01 00:56
- departure_delaynumber
- -3
- taxi_out_minutesnumber
- 31
- scheduled_arrivaldate & time
- 2025-01-01 07:26
- actual_arrivaldate & time
- 2025-01-01 07:23
- arrival_delaynumber
- -3
- air_time_minutesnumber
- 214
- distance_milesnumber
- 1846
- cancellednumber
- 0
- cancellation_codetext · links to cancellation_reasons
- NULL
- divertednumber
- 0
- carrier_delaynumber
- NULL
- weather_delaynumber
- NULL
- nas_delaynumber
- NULL
- security_delaynumber
- NULL
- late_aircraft_delaynumber
- NULL
- flight_idnumber · unique id
- 5
- flight_datedate · links to calendar
- 2025-01-01
- airline_codetext · links to airlines
- AA
- flight_numbernumber
- 575
- tail_numbertext
- N163AA
- origintext · links to airports
- DFW
- destinationtext · links to airports
- CLT
- route_idnumber · links to routes
- 1457
- scheduled_departuredate & time
- 2025-01-01 05:00
- actual_departuredate & time
- 2025-01-01 04:55
- departure_delaynumber
- -5
- taxi_out_minutesnumber
- 23
- scheduled_arrivaldate & time
- 2025-01-01 08:32
- actual_arrivaldate & time
- 2025-01-01 08:14
- arrival_delaynumber
- -18
- air_time_minutesnumber
- 105
- distance_milesnumber
- 936
- cancellednumber
- 0
- cancellation_codetext · links to cancellation_reasons
- NULL
- divertednumber
- 0
- carrier_delaynumber
- NULL
- weather_delaynumber
- NULL
- nas_delaynumber
- NULL
- security_delaynumber
- NULL
- late_aircraft_delaynumber
- NULL
- flight_idnumber · unique id
- 6
- flight_datedate · links to calendar
- 2025-01-01
- airline_codetext · links to airlines
- AA
- flight_numbernumber
- 1196
- tail_numbertext
- N338ST
- origintext · links to airports
- EWR
- destinationtext · links to airports
- MIA
- route_idnumber · links to routes
- 1808
- scheduled_departuredate & time
- 2025-01-01 05:00
- actual_departuredate & time
- 2025-01-01 04:53
- departure_delaynumber
- -7
- taxi_out_minutesnumber
- 22
- scheduled_arrivaldate & time
- 2025-01-01 08:20
- actual_arrivaldate & time
- 2025-01-01 08:10
- arrival_delaynumber
- -10
- air_time_minutesnumber
- 151
- distance_milesnumber
- 1085
- cancellednumber
- 0
- cancellation_codetext · links to cancellation_reasons
- NULL
- divertednumber
- 0
- carrier_delaynumber
- NULL
- weather_delaynumber
- NULL
- nas_delaynumber
- NULL
- security_delaynumber
- NULL
- late_aircraft_delaynumber
- NULL
first 6 of 124,460 rows · your queries use all of them
solution.sql
Hints and the answer are always available. Using them counts the question as solved with help.