SQL code copied to buffer

SQLtest

- SQL practice online

Take the SQL knowledge test, hone your query writing skills, and prepare for job interviews. Our practical SQL tasks are suitable for both beginners and experienced professionals.
Task 30:
For each airport, find the average occupancy of flights in August 2017 (according to the actual departure date).
Output the result in two columns departure_airport - departure airport code and average_flights_occupancy - average flight occupancy as a percentage, rounded to two decimal places. 
Sort the results in descending order of the second column. If occupancy values ​​are equal, sort by 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 230 diverse tasks on our platform.

Log in to save your progress.

Bookings database

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_codemodelrange
1773{"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_codeairport_namecitycoordinatestimezone
1YKS{"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_noflight_idboarding_noseat_no
100054352123513062512D
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_refbook_datetotal_amount
100000F2017-07-05 00:12:00+00265700.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
11185PG01342017-09-10 06:50:00+002017-09-10 11:55:00+00DMEBTKScheduled319
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_codeseat_nofare_conditions
13192ABusiness
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
1000543215977630625Business42100.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
1000543200098706B0468149 604011VALERIY TIKHONOV{"phone": "+70127117011"}
Indexes:
  • PRIMARY KEY, btree (ticket_no)
Foreign-key constraints:
  • FOREIGN KEY (book_ref) REFERENCES bookings(book_ref)