Interview Query

Second Longest Flight

4
Have you seen this question before?

Given a table of flights, extract the 2nd longest flight between each pair of cities. Order the flights by the flight id ascending.

Note: For any cities X and Y, (source_location=X, destination_location=Y) and (source_location=Y, destination_location=X) are counted as the same pair of cities.

Note: If there are fewer than two flights between two cities, there is no 2nd longest flight.

Example:

Input:

flights table

Column Type
id INTEGER
destination_location VARCHAR
source_location VARCHAR
plane_id INTEGER
flight_start DATETIME
flight_end DATETIME

Output:

Column Type
id INTEGER
destination_location VARCHAR
source_location VARCHAR
flight_start DATETIME
flight_end DATETIME
Next question: Sum to N
.....
MySQL 8.0.17
Loading editor
Use Shift + Enter to run query