Task 48:

Airports with Delays

Calculate the average departure delays for each airport from the flights table in August 1, 2017. Your output should include the following columns:
- departure_airport
- overall_average_departure_delay:  the overall average delay across all airports
- airport_average_departure_delay: the average delay for each specific airport

Only include airports where the average delay exceeds the overall average, and sort the results by airport_average_departure_delay in descending order.

Use PostgreSQL syntax to write your answer. Descriptions of tables are provided in the right pane.

Write your request in the field below and click the "Check it!" button.

Get hint

Copy code Clear editor

Bookings Database: table structure and schema overview

The Bookings database (PostgreSQL) models airline flights across multiple airports and is widely used for SQL practice.

This page shows the table structure, key columns, and constraints used in typical analytical and transactional SQL queries.

The Bookings database contains 8 main tables.

ER diagram of the Bookings database

The list of tables

aircrafts_data - table of aircrafts.

aircraft_codeUnique code for each aircraft
modelAircraft model name in English and Russian in JSON format
rangeAircraft fly range in kilometers

PRIMARY KEY, btree (aircraft_code)

	aircraft_code	model	range
1	773	{"en": "Boeing 777-300", "ru": "Боинг 777-300"}	11100

airports_data - table of airports.

airport_codeUnique code for each airport
airport_nameAirport name in English and Russian in JSON format
cityAirport city in English and Russian in JSON format
coordinatesAirport coordinates as POINT(longitude, latitude)
timezoneAirport timezone name

PRIMARY KEY, btree (airport_code)

	airport_code	airport_name	city	coordinates	timezone
1	YKS	{"en": "Yakutsk Airport", "ru": "Якутск"}	{"en": "Yakutsk", "ru": "Якутск"}	(129.77099609375,62.0932998657227)	Asia/Yakutsk

boarding_passes - table of boarding passes.

ticket_noTicket number
flight_idFlight identificator
boarding_noBoarding pass number
seat_noSeat number

PRIMARY KEY, btree (ticket_no, flight_id)
UNIQUE CONSTRAINT, btree (flight_id, boarding_no)
UNIQUE CONSTRAINT, btree (flight_id, seat_no)

FOREIGN KEY (ticket_no, flight_id) REFERENCES ticket_flights(ticket_no, flight_id)

	ticket_no	flight_id	boarding_no	seat_no
1	0005435212351	30625	1	2D

bookings - table of bookings.

book_refBooking number
book_dateBooking date
total_amountTotal booking cost

PRIMARY KEY, btree (book_ref)

	book_ref	book_date	total_amount
1	00000F	2017-07-05 00:12:00+00	265700.00

flights - table of flights.

flight_idFlight ID
flight_noFlight number
scheduled_departureScheduled departure time
scheduled_arrivalScheduled arrival time
departure_airportAirport of departure
arrival_airportAirport of arrival
statusFlight status
aircraft_codeAircraft code, IATA
actual_departureActual departure time
actual_arrivalActual arrival time

PRIMARY KEY, btree (flight_id)
UNIQUE CONSTRAINT, btree (flight_no, scheduled_departure)

	flight_id	flight_no	scheduled_departure	scheduled_arrival	departure_airport	arrival_airport	status	aircraft_code	actual_departure	actual_arrival
1	1185	PG0134	2017-09-10 06:50:00+00	2017-09-10 11:55:00+00	DME	BTK	Scheduled	319

seats - table of aircraft seats.

aircraft_codeAircraft code, IATA
seat_noSeat number
fare_conditionsTravel class

PRIMARY KEY, btree (aircraft_code, seat_no)

FOREIGN KEY (aircraft_code) REFERENCES aircrafts(aircraft_code) ON DELETE CASCADE

	aircraft_code	seat_no	fare_conditions
1	319	2A	Business

ticket_flights - ticket to flights relations.

ticket_noTicket number
flight_idFlight ID
fare_conditionsTravel class
amountTravel cost

PRIMARY KEY, btree (ticket_no, flight_id)

FOREIGN KEY (flight_id) REFERENCES flights(flight_id)
FOREIGN KEY (ticket_no) REFERENCES tickets(ticket_no)

	ticket_no	flight_id	fare_conditions	amount
1	0005432159776	30625	Business	42100.00

tickets - table of tickets.

ticket_noTicket number
book_refBooking number
passenger_idPassenger ID
passenger_namePassenger name
contact_dataPassenger contact information

	ticket_no	book_ref	passenger_id	passenger_name	contact_data
1	0005432000987	06B046	8149 604011	VALERIY TIKHONOV	{"phone": "+70127117011"}

PRIMARY KEY, btree (ticket_no)

FOREIGN KEY (book_ref) REFERENCES bookings(book_ref)

Head First SQL: A Learner's Guide to Querying and Managing Data by Kimberly Fessel

Do you have an abundance of data but don't know how to make sense of it? Do you want to gain useful insights from your data, but you're not sure where to begin? Mining data is a vital, well-paying skill, and SQL provides the most fundamental way to query and manage data. But learning SQL can be intimidating. This thoroughly revised book teaches you SQL fundamentals in a painless and enjoyable manner. With the Head First series’ hands-on, conversational style, you'll quickly grasp SQL concepts, then move to intermediate topics, including subqueries and joins. You'll gain the knowledge, skills, and confidence necessary to get the most out of your data with SQL.

Practice

Lessons

Playground

Test

Airports with Delays

Login

Register

Airports with Delays