Prepare and test your skills
Prepare and test your skills
Free sample question, answered and explained below. The Associate Data Practitioner practice bank has 1,156 more like it. Try 10 free: instant score, no account.
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?
GROUP BY clause isIn standard SQL and GoogleSQL for BigQuery, the GROUP BY clause groups rows that share identical values in specified summary columns into summary rows. When aggregate functions such as AVG(), COUNT(), or SUM() are used alongside non-aggregated columns or expressions in a SELECT list, all non-aggregated columns must be included in the GROUP BY clause.
subscriber_type and EXTRACT(HOUR FROM start_time) AS hour_of_day without aggregating them. Adding GROUP BY subscriber_type, hour_of_day informs BigQuery to group records by these unique attribute combinations.AVG() calculation (✓): Once grouped, the AVG(duration_minutes) function calculates the mean trip duration for each distinct combination of subscriber type and hour.SELECT list (such as hour_of_day) as well as the original column references.EXTRACT() and FORMAT_TIMESTAMP().The GROUP BY clause is the standard, syntactically required mechanism in BigQuery SQL to perform grouping transformations before evaluating aggregate functions.
WHERE clause doesThe WHERE clause filters raw rows from the source table before any grouping or aggregation takes place based on boolean filter conditions.
WHERE filters individual rows but does not consolidate rows into summary groups.WHERE is evaluated before SELECT, meaning column aliases created in SELECT (such as hour_of_day) cannot typically be referenced in WHERE without a subquery or repeating the expression.subscriber_type is neither grouped nor aggregated.GROUP BY and HAVING clauses.The query already included a GROUP BY clause and the goal was to filter out null subscriber records and specific trip start times prior to computing averages.
ORDER BY clause doesThe ORDER BY clause sorts the resulting output rows of a query in ascending (ASC) or descending (DESC) order based on specified column values or expressions.
ORDER BY controls only the display sequence of the final result set; it does not group individual records into aggregated buckets.ORDER BY clause without a GROUP BY clause will not satisfy the standard SQL requirement that non-aggregated columns in a SELECT list containing aggregate functions must be grouped.WHERE), grouping (GROUP BY), and aggregation have completed.The query already had a valid GROUP BY clause and the requirement was simply: "Sort the final aggregated output by subscriber type and descending hour of day."
HAVING clause doesThe HAVING clause applies filter criteria to aggregated groups or summary results after a GROUP BY clause has been evaluated.
HAVING can filter aggregate expressions like AVG(duration_minutes) > 0, it does not perform the grouping of non-aggregated columns (subscriber_type and hour_of_day).HAVING without a GROUP BY clause will still result in an aggregation error because subscriber_type remains unaggregated and ungrouped.GROUP BY clause and before ORDER BY.The query already contained GROUP BY subscriber_type, hour_of_day and the objective was to restrict results only to groups where the average trip length exceeded a specific duration threshold.
Answer them yourself and get your score the moment you finish. No account needed.
Full access includes
Keep the momentum going with these hand-picked practice scenarios
Last updated