test_namespace/complex_dataset
Loading...

Provenance

3 Dependencies
 
2 Tables
  • IMPORT INTO jfk_flights
    SELECT fly_month
         , passengers
         , seats
         , flights
    FROM flights
    WHERE origin_airport = 'JFK'
  • IMPORT INTO lax_flights
    SELECT fly_month
         , passengers
         , seats
         , flights
    FROM flights
    WHERE origin_airport = 'LAX'
  • IMPORT INTO votes_by_state
    SELECT candidate_normalized
         , state_postal
         , sum(votes)
    FROM precinct_results
    GROUP BY candidate_normalized, state_postal
 
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)
Loading...