google-bigquerygoogle-cloudsql

Austin Bikeshare Stats

Ben Gorman

Ben Gorman

Life's a garden. Dig it.

Challenge

Using the austin_bikeshare public dataset, calculate summary statistics on all the trips. Namely,

  • total trips
  • min_start_time
  • max_start_time
  • avg_duration_minutes

How do I access the austin_bikeshare dataset?

  1. Search for "public" in the explorer pane
  2. Click "Broaden search to all"
  3. Pin the bigquery-public-data project
  4. Clear the search bar
  5. Expand the breadcrumb next to bigquery-public-data to find the austin_bikeshare dataset

Solution

SELECT
  count(*) as trips,
  min(start_time) as min_start_time,
  max(start_time) as max_start_time,
  avg(duration_minutes) as avg_duration_minutes
FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`

Explanation

  1. count(*) counts the number of rows in the table.
  2. min(start_time) as min_start_time aggregates the start_time field, measuring the min start_time and naming the result min_start_time.
  3. max(start_time) as max_start_time aggregates the start_time field, measuring the max start_time and naming the result max_start_time.
  4. avg(duration_minutes) as avg_duration_minutes aggregates the duration_minutes field, measuring the mean duration_minutes and naming the result avg_duration_minutes.
  5. FROM bigquery-public-data.austin_bikeshare.bikeshare_trips references the bikeshare_trips table in the austin_bikeshare dataset in the bigquery-public-data project.