FROM splitgraph/domestic_us_flights:fc17993fcd2090481ad8cafb651f9e2f61f12f6a533b8523d88600847a48e9dc IMPORT {SELECT fly_month
     , passengers
     , seats
     , flights
FROM flights
WHERE origin_airport = 'JFK'} AS jfk_flights, {SELECT fly_month
     , passengers
     , seats
     , flights
FROM flights
WHERE origin_airport = 'LAX'} AS lax_flights
FROM mildbyte/qoz_vote_fraction:63a21276dc4d3a237bfc67afc1ad85a3dadc9391a53a7290d610249c9957ce04 IMPORT vote_fraction AS votes_by_county, qoz_vote_fraction AS qoz_vote_fraction
FROM splitgraph/2016_election:3835145ada3f07cad99087d1b1071122d58c48783cbfe4694c101d35651fba90 IMPORT {SELECT candidate_normalized
     , state_postal
     , sum(votes)
FROM precinct_results
GROUP BY candidate_normalized, state_postal} AS votes_by_state
SQL {CREATE TABLE total_flights
  AS WITH jfk_flights_monthly AS (SELECT fly_month
                                       , sum(passengers) AS p
                                       , sum(seats) AS s
                                       , sum(flights) AS f
                                  FROM jfk_flights
                                  GROUP BY fly_month)

       , lax_flights_monthly AS (SELECT fly_month
                                      , sum(passengers) AS p
                                      , sum(seats) AS s
                                      , sum(flights) AS f
                                 FROM lax_flights
                                 GROUP BY fly_month)

       SELECT j.fly_month
            , j.p AS jfk_passengers
            , j.s AS jfk_seats
            , j.f AS jfk_flights
            , l.p AS lax_passengers
            , l.s AS lax_seats
            , l.f AS lax_flights
       FROM jfk_flights_monthly AS j
            INNER JOIN lax_flights_monthly AS l ON j.fly_month = l.fly_month;

ALTER TABLE total_flights ADD PRIMARY KEY (fly_month)}