SQL Assessment: Find flight occupancy by fare

SQL code copied to buffer

Task 31:

For each departure airport, calculate the average occupancy rate of business and economy class flights in August 2017 (according to the actual departure date). Average occupancy is calculated as the percentage of occupied seats out of the total number of seats in a given class.
Display the result in three columns:
departure_airport — departure airport code
average_business_occupancy — average occupancy rate of business class seats (as a percentage, rounded to two digits)
average_economy_occupancy — average occupancy rate of economy class seats (as a percentage, rounded to two digits)

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

Explore over 360 diverse tasks on our platform.

Bookings Database (PostgreSQL)

The subject area of this database is airline flights through various airports.

ER diagram of the Bookings database

The Bookings Database contains 8 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)