Bigquery Sql Group By Aggregation Clause | Unlock the power of your data in the cloud! Get hands-on with Google Cloud's core data services like BigQuery and Looker to validate your practical skills in data ingestion, analysis, and management, and earn your Associate Data Practitioner certification! | StudyCerts

Associate Data Practitioner

Unlock the power of your data in the cloud! Get hands-on with Google Cloud's core data services like BigQuery and Looker to validate your practical skills in data ingestion, analysis, and management, and earn your Associate Data Practitioner certification!

Fundamental

Prepare and test your skills

Track Readiness

Set Exam Goal

Fundamental

Prepare and test your skills

Track Readiness

Set Exam Goal

Bigquery Sql Group By Aggregation Clause

Section: Define and execute SQL queries in BigQuery to generate reports and extract key insights

Worked example. The correct answer is already marked and every option is explained below, so there is nothing to select here. To answer questions yourself, start the free trial.

A data analyst is writing a SQL query in BigQuery to analyze bike rental trends. The goal is to generate a report showing the average trip duration broken down by subscriber type and the starting hour of the day.

The analyst writes the following query in BigQuery Studio:

SELECT
  subscriber_type,
  EXTRACT(HOUR FROM start_time) AS hour_of_day,
  AVG(duration_minutes) AS avg_trip_length
FROM
  `bigquery-public-data.austin_bikeshare.bikeshare_trips`

When running the query, BigQuery returns an error indicating that subscriber_type is neither grouped nor aggregated.

Which clause should the analyst append to the query to resolve the error and produce the desired summary report?

WHERE subscriber_type IS NOT NULL AND hour_of_day > 0

HAVING AVG(duration_minutes) > 0

ORDER BY subscriber_type, hour_of_day DESC

GROUP BY subscriber_type, hour_of_day

Associate Data Practitioner

Bigquery Sql Group By Aggregation Clause

Related questions