Splitfiles

FROM splitgraph/geonames:latest IMPORT {
    SELECT
        -- Note we need to escape the closing curly brace here for the
        -- Splitfile executor.
        substring(alternatenames FROM ',([A-Z]{3\\}),') AS iata_code,
        name,
        latitude,
        longitude,
        geonameid
    FROM all_countries
    WHERE feature_code = 'AIRP'
    AND country_code = 'US'
    AND substring(alternatenames FROM ',([A-Z]{3\\}),') IS NOT NULL
} AS airports

SQL {
    CREATE TABLE us_flights_2009 AS
    SELECT
        origin_airport AS origin_iata,
        ao.name AS origin_name,
        ao.latitude AS origin_lat,
        ao.longitude AS origin_lon,

        destination_airport AS destination_iata,
        ad.name AS destination_name,
        ad.latitude AS destination_lat,
        ad.longitude AS destination_lon,

        SUM(passengers) AS total_passengers,
        SUM(seats) AS total_seats,
        SUM(flights) AS flights
    FROM "splitgraph/domestic_us_flights:latest".flights f
        JOIN airports ao ON f.origin_airport = ao.iata_code
        JOIN airports ad ON f.destination_airport = ad.iata_code
    WHERE
        EXTRACT('year' FROM fly_month) = 2009
        GROUP BY origin_airport, destination_airport,
            ao.name, ao.latitude, ao.longitude,
            ad.name, ad.latitude, ad.longitude
}