runtimetask Conditional aggregation · medium
Flight outcomes by origin state
Return state, flights, cancelled, diverted and cancelled_pct (0–100, 2 decimals) per origin airport state.
what to return
- Columns
stateflightscancelleddivertedcancelled_pctin this order- Rows
- 52 rows · any order
- Skill
- Count several categories side by side. Refresher: Ratios, ROUND & NULLIF →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result52 rows · any order
| state | flights | cancelled | diverted | cancelled_pct |
|---|---|---|---|---|
| AK | 811 | 17 | 5 | 2.1 |
| AL | 622 | 26 | 1 | 4.18 |
| AR | 506 | 13 | 0 | 2.57 |
first 3 of 52 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
airports8 columns · 326 rows
| airport_codetext · unique id | airport_nametext | citytext | statetext | state_nametext | latitudedecimal | longitudedecimal | elevation_ftnumber |
|---|---|---|---|---|---|---|---|
| ABE | Lehigh Valley International Airport | Allentown/Bethlehem/Easton | PA | Pennsylvania | 40.6518 | -75.4428 | 393 |
| ABI | Abilene Regional Airport | Abilene | TX | Texas | 32.4113 | -99.6819 | 1791 |
| ABQ | Albuquerque International Sunport | Albuquerque | NM | New Mexico | 35.04 | -106.6089 | 5355 |
| ABR | Aberdeen Regional Airport | Aberdeen | SD | South Dakota | 45.4491 | -98.4218 | 1302 |
| ABY | Southwest Georgia Regional Airport | Albany | GA | Georgia | 31.5329 | -84.1962 | 197 |
| ACT | Waco Regional Airport | Waco | TX | Texas | 31.6113 | -97.2305 | 516 |
- airport_codetext · unique id
- ABE
- airport_nametext
- Lehigh Valley International Airport
- citytext
- Allentown/Bethlehem/Easton
- statetext
- PA
- state_nametext
- Pennsylvania
- latitudedecimal
- 40.6518
- longitudedecimal
- -75.4428
- elevation_ftnumber
- 393
- airport_codetext · unique id
- ABI
- airport_nametext
- Abilene Regional Airport
- citytext
- Abilene
- statetext
- TX
- state_nametext
- Texas
- latitudedecimal
- 32.4113
- longitudedecimal
- -99.6819
- elevation_ftnumber
- 1791
- airport_codetext · unique id
- ABQ
- airport_nametext
- Albuquerque International Sunport
- citytext
- Albuquerque
- statetext
- NM
- state_nametext
- New Mexico
- latitudedecimal
- 35.04
- longitudedecimal
- -106.6089
- elevation_ftnumber
- 5355
- airport_codetext · unique id
- ABR
- airport_nametext
- Aberdeen Regional Airport
- citytext
- Aberdeen
- statetext
- SD
- state_nametext
- South Dakota
- latitudedecimal
- 45.4491
- longitudedecimal
- -98.4218
- elevation_ftnumber
- 1302
- airport_codetext · unique id
- ABY
- airport_nametext
- Southwest Georgia Regional Airport
- citytext
- Albany
- statetext
- GA
- state_nametext
- Georgia
- latitudedecimal
- 31.5329
- longitudedecimal
- -84.1962
- elevation_ftnumber
- 197
- airport_codetext · unique id
- ACT
- airport_nametext
- Waco Regional Airport
- citytext
- Waco
- statetext
- TX
- state_nametext
- Texas
- latitudedecimal
- 31.6113
- longitudedecimal
- -97.2305
- elevation_ftnumber
- 516
first 6 of 326 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.