SQL Linear Recursion to Find Cheapest Airline Connections

Author(s): Ashutosh Malgaonkar

Originally published on Towards AI.

COMPANY X employees are trying to find the cheapest flights to upcoming conferences.

When people fly long distances, a direct city-to-city flight is often more expensive than taking two flights with a stop in a hub city. Travelers might save even more money by breaking the trip into three flights with two stops. But for the purposes of this challenge, let’s assume that no one is willing to stop three times. The table contains the following columns: • id – the unique ID of the flight; • origin – the origin city of the current flight; • destination – the destination city of the current flight; • cost – the cost of current flight.

Your task is to produce a trips table listing the cheapest possible trips between all origin-destination pairs present in the dataset, considering routes with up to two stops.

This table should have the columns origin, destination, and min_price (cheapest one). Sort the output table by origin, then by destination.

The cities are all represented by three-letter uppercase abbreviations.

Note: A flight from SFO to JFK is considered different from a flight from JFK to SFO.

Here is the data table:

Now let us look to understand what the problem is expecting us to… Read the full blog for free on Medium.

Join thousands of data leaders on the AI newsletter. Join over 80,000 subscribers and keep up to date with the latest developments in AI. From research to projects and ideas. If you are building an AI startup, an AI-related product, or a service, we invite you to consider becoming a sponsor.

Published via Towards AI