SQL for Travel Booking: Finding Cheapest Multi-City Itineraries with Recursive Queries

Flight Itinerary Optimization

Travel search engines must explore thousands of possible routes across a graph of cities and flights. Recursive CTEs let you traverse the graph in a single query.

1. Find All Reachable Cities from a Hub

Starting from a home city, find every city you can reach within 3 flight hops.

WITH RECURSIVE reachable AS (
    -- Anchor: Direct flights from home city
    SELECT
        receiver_city AS destination,
        1 AS hops,
        price AS total_price
    FROM flights
    WHERE departure_city = 'SFO'

    UNION ALL

    -- Recursive: Connect to next city
    SELECT
        f.receiver_city,
        r.hops + 1,
        r.total_price + f.price
    FROM flights f
    INNER JOIN reachable r ON f.departure_city = r.destination
    WHERE r.hops < 3
)
SELECT DISTINCT destination, hops, total_price
FROM reachable
ORDER BY hops, total_price;

2. Find Cheapest 3-City Itinerary

Search for the lowest-total-price route that visits exactly 3 distinct cities.

WITH RECURSIVE itinerary AS (
    SELECT
        departure_city AS start_city,
        receiver_city AS current_city,
        ARRAY[departure_city, receiver_city] AS path,
        price AS total_price,
        2 AS city_count
    FROM flights
    WHERE departure_city = 'SFO'

    UNION ALL

    SELECT
        i.start_city,
        f.receiver_city,
        path || f.receiver_city,
        total_price + f.price,
        city_count + 1
    FROM flights f
    JOIN itinerary i ON f.departure_city = i.current_city
    WHERE NOT f.receiver_city = ANY(path)  -- Don't revisit cities
      AND city_count < 3
)
SELECT start_city, current_city, total_price, city_count
FROM itinerary
WHERE city_count = 3
ORDER BY total_price
LIMIT 1;

3. Detecting Routing Loops

Ensure no city appears twice in a routing path — loops indicate broken graph data.

WITH RECURSIVE route AS (
    SELECT
        ARRAY[departure_city] AS path,
        receiver_city AS current,
        1 AS hops
    FROM flights
    WHERE departure_city = 'SFO'

    UNION ALL

    SELECT
        path || receiver_city,
        f.receiver_city,
        hops + 1
    FROM flights f
    JOIN route r ON f.departure_city = r.current
    WHERE NOT f.receiver_ptr = ANY(path)  -- Anti-loop check
)
SELECT current, hops, path
FROM route
WHERE hops > 5  -- Flags suspicious deep paths
LIMIT 10;

Key Takeaways for Production

• Recursive CTEs are your go-to for graph traversal — flight networks, org charts, recommendation engines.
• Use ANY(path) to efficiently check if a node has already been visited and avoid infinite loops.
• Total price computation must include all legs (taxes, fees, baggage) — the query above only sums base fares.
• Index departure_city and receiver_city for performance on large flight schedules.