SQL code copied to buffer

SQLtest - up your SQL skills

Harness the power of practice to master SQL. Our interactive exercises are designed to solidify your understanding, from foundational concepts to advanced techniques. Whether you're a SQL novice or a seasoned pro, you’ll find challenges tailored to your skill level. Boost your confidence for job interviews and real-world applications through consistent practice.

Donate

Task 31:

For each airport, find the average occupancy of flights in August 2017 (according to the actual departure date) for business and economy class.
Display the result in two columns departure_airport - departure airport code, average_business_occupancy - average occupancy of business class seats in percent, rounded to two decimal places , average_economy_occupancy - average occupancy of economy class seats. 
Sort the results by departure airport code.

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

To write the answer, use PostgreSQL syntax. Descriptions of the tables are given in the right panel.

Explore over 250 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

Table aircrafts_data

Table columns:

aircraft_code - Unique code for each aircraft.
model - Aircraft model name in English and Russian in JSON format.
range - Aircraft fly range in kilometers.

Indexes:

PRIMARY KEY, btree (aircraft_code)

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

Table airports_data

Table columns:

airport_code - Unique code for each airport.
airport_name - Airport name in English and Russian in JSON format.
city - Airport city in English and Russian in JSON format.
coordinates - Airport coordinates as POINT(longitude, latitude).
timezone - Airport timezone name.

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

Indexes:

PRIMARY KEY, btree (airport_code)

Table boarding_passes

Table columns:

ticket_no - Ticket number.
flight_id - Flight identificator.
boarding_no - Boarding pass number.
seat_no - Seat number.

	ticket_no	flight_id	boarding_no	seat_no
1	0005435212351	30625	1	2D

Indexes:

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

Foreign-key constraints:

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

Table bookings

Table columns:

book_ref - Booking number.
book_date - Booking date.
total_amount - Total booking cost.

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

Indexes:

PRIMARY KEY, btree (book_ref)

Table flights

Table columns:

flight_id - Flight ID.
flight_no - Flight number.
scheduled_departure - Scheduled departure time.
scheduled_arrival - Scheduled arrival time.
departure_airport - Airport of departure.
arrival_airport - Airport of arrival.
status - Flight status.
aircraft_code - Aircraft code, IATA.
actual_departure - Actual departure time.
actual_arrival - Actual arrival time.

	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

Indexes:

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

Table seats

Table columns:

aircraft_code - Aircraft code, IATA.
seat_no - Seat number.
fare_conditions - Travel class.

	aircraft_code	seat_no	fare_conditions
1	319	2A	Business

Indexes:

PRIMARY KEY, btree (aircraft_code, seat_no)

Foreign-key constraints:

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

Table ticket_flights

Table columns:

ticket_no - Ticket number.
flight_id - Flight ID.
fare_conditions - Travel class.
amount - Travel cost.

	ticket_no	flight_id	fare_conditions	amount
1	0005432159776	30625	Business	42100.00

Indexes:

PRIMARY KEY, btree (ticket_no, flight_id)

Foreign-key constraints:

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

Table tickets

Table columns:

ticket_no - Ticket number.
book_ref - Booking number.
passenger_id - Passenger ID.
passenger_name - Passenger name.
contact_data - Passenger contact information.

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

Indexes:

PRIMARY KEY, btree (ticket_no)

Foreign-key constraints:

FOREIGN KEY (book_ref) REFERENCES bookings(book_ref)