FastPrep
Problem · Database

Rank Flights by Airline and Destination Frequency

● EasyCapital One logoCapital OneINTERNOA

Problem statement

You are given a flights table. Return one output row for every flight.

Output exactly the columns id, destination, and departure_time.

Ordering

  1. Airlines with more total flights come first.
  2. If two airlines have the same total, order them by aviacompany in ascending order.
  3. Within an airline, destinations with more flights for that airline come first.
  4. If two destinations for the same airline have the same count, order them by destination in ascending order.
  5. Within an airline and destination, order rows by departure_time in ascending order.
  6. If all preceding sort keys tie, order rows by id in ascending order.

Compute both flight counts from all rows in the input table. The duration column is not returned and does not affect the ordering.

Table schema

MySQLPostgreSQLPandas

Use the same input data with any supported language. Open the Schema tab in the editor to see the generated SQL setup or Pandas DataFrames.

flights

Flight records used to compute airline and destination frequencies.

ColumnTypeNullableDescription
idPKIntegerNoUnique flight identifier.
aviacompanyTextNoAirline operating the flight.
destinationTextNoFlight destination code.
departure_timeTextNoZero-padded departure time in HH-MM format.
durationIntegerNoFlight duration in minutes.

Expected result

Your query or function must return these columns.

ColumnTypeNullableDescription
idIntegerNo—
destinationTextNo—
departure_timeTextNo—

Row order: must match exactly. Numeric tolerance: 0.

Constraints

  • The id column uniquely identifies a flight.
  • Every input cell is non-null.
  • aviacompany and destination contain non-empty uppercase ASCII letters and spaces.
  • departure_time is a zero-padded 24-hour time in HH-MM format.
  • duration is an integer number of minutes.
  • The flights table may be empty.

More Capital One problems