Task 31:

Find flight occupancy by fare

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

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)

Learning SQL: Generate, Manipulate, and Retrieve Data by Alan Beaulieu

As data floods into your company, you need to put it to work right away―and SQL is the best tool for the job. With the latest edition of this introductory guide, author Alan Beaulieu helps developers get up to speed with SQL fundamentals for writing database applications, performing administrative tasks, and generating reports. You’ll find new chapters on SQL and big data, analytic functions, and working with very large databases.

Practice

Lessons

Test

Find flight occupancy by fare

Login

Register

Find flight occupancy by fare