test_namespace/complex_dataset

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
0 Sources
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)
 
SQL
1 Source
CREATE TABLE total_flights_by_origin
  AS SELECT fly_month
          , origin_airport
          , sum(passengers) AS total_passengers
          , sum(seats) AS total_seats
          , sum(flights) AS total_flights
     FROM "splitgraph/domestic_us_flights:fc17993fcd2090481ad8cafb651f9e2f61f12f6a533b8523d88600847a48e9dc".flights
     GROUP BY fly_month, origin_airport