캐글 intro_to_sql/05-as-with

rides_per_month_query =  """WITH cte AS
                            (
                              SELECT EXTRACT(MONTH FROM trip_start_timestamp) AS month
                                FROM `bigquery-public-data.chicago_taxi_trips.taxi_trips`
                                WHERE EXTRACT(YEAR FROM trip_start_timestamp) = 2017
                            )
                            SELECT month, COUNT(1) AS num_trips
                              FROM cte
                              GROUP BY month
                              ORDER BY month
                         """ ​​

rides_per_year_query = """WITH cte AS
                          (
                            SELECT EXTRACT(YEAR FROM trip_start_timestamp) AS year
                              FROM `bigquery-public-data.chicago_taxi_trips.taxi_trips`
                          )
                          SELECT year, COUNT(1) AS num_trips
                            FROM cte
                            GROUP BY year
                            ORDER BY year
                       """

speeds_query = """
               WITH RelevantRides AS
               (
                   SELECT EXTRACT(HOUR FROM trip_start_timestamp) AS hour_of_day,
                       trip_miles,
                       trip_seconds
                   FROM `bigquery-public-data.chicago_taxi_trips.taxi_trips`
                   WHERE trip_start_timestamp > '2017-01-01'
                       AND trip_start_timestamp < '2017-07-01'
                       AND trip_seconds > 0
                       AND trip_miles > 0
               )
               SELECT hour_of_day,
                   COUNT(1) AS num_trips,
                   3600 * SUM(trip_miles) / SUM(trip_seconds) AS avg_mph
               FROM RelevantRides
               GROUP BY hour_of_day
               ORDER BY hour_of_day
               """​