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 tasked with generating a monthly revenue report by product category and geographic region from a multi-terabyte transactional dataset in BigQuery. The analyst needs to optimize the SQL query to minimize resource usage, reduce bytes scanned, and produce accurate summary aggregations.
Which SQL querying practice should the analyst apply to achieve efficient query execution and accurate reporting?
WHERE filtering clauses entirely and rely on a LIMIT clause to restrict the amount of data processed during aggregationLIMIT clause doesA LIMIT clause restricts the final number of output rows returned to the user or client interface after all sorting, joining, and aggregation operations have completed.
LIMIT clause does not stop BigQuery from scanning the full dataset required to calculate the requested aggregations; the entire dataset must still be read and aggregated before the top N rows are sliced.WHERE clause means the dataset is not constrained to the specific monthly reporting period or target criteria, corrupting the summary metrics.GROUP BY grouping across all partitions before applying the row limit.LIMIT for optimizationLIMIT executes as the final phase of a query pipeline.LIMIT.The analyst only needed to inspect a small sample of pre-aggregated rows (e.g., previewing the schema or verifying first-row output format in a notebook) rather than compiling a complete, accurate monthly report.
To optimize queries effectively, analysts should combine partition pruning with WHERE filtering rather than relying on LIMIT to control execution load.
SELECT * across all underlying tables and apply filtering conditions in a separate subquery wrapper after computing aggregationsUsing SELECT * retrieves all columns present in every referenced dataset, while filtering after aggregation in an outer wrapper forces the engine to aggregate the entire unpartitioned dataset before discarding irrelevant data points.
SELECT * in columnar systems forces the execution engine to scan and load every column in the table, maximizing I/O cost and network throughput regardless of whether those columns appear in the final report.This approach might be considered during exploratory interactive analysis on very small, non-columnar staging tables where schema structure is unknown and execution performance is negligible.
Filtering after aggregation should generally be reserved for summary-level metrics using the HAVING clause, whereas row-level conditions should always be evaluated in the WHERE clause prior to grouping.
CROSS JOIN across all dimensions and use the HAVING clause to filter out mismatched records and unwanted time periodsCROSS JOIN and post-join HAVING filtering doA CROSS JOIN produces the Cartesian product of two or more tables, pairing every row from the first table with every row from the second table. Using the HAVING clause applies filtering only after grouping and aggregation have taken place.
CROSS JOIN between multi-million-row transactional tables and dimensional tables creates an immense Cartesian intermediate dataset, often causing queries to exceed memory limits and fail.HAVING clause (✗): The HAVING clause is designed to filter on aggregated group properties (e.g., HAVING SUM(revenue) > 1000), not to filter individual row dimensions or date windows that should be evaluated in the WHERE clause.INNER JOIN ... ON) is appropriate leads to inaccurate calculations.A CROSS JOIN is intentionally required to compute matrix permutations—such as generating all combinations of product items against available store locations to identify zero-sale occurrences.
Effective join strategies in analytical SQL rely on explicit join predicates (JOIN ... ON key_a = key_b) combined with early WHERE filters to ensure that only relevant rows from each table are merged.
SELECT list and apply selective filtering in the WHERE clause before aggregating with GROUP BYColumn projection and predicate pushdown (early filtering) are foundational SQL optimization practices in columnar analytical databases like BigQuery. Column projection involves listing only specific needed attributes (such as region, category, and revenue) in the SELECT statement rather than querying all columns. Early filtering applies conditions within the WHERE clause to eliminate non-matching records prior to resource-intensive data processing operations such as joins and aggregations.
WHERE reduces the row count before the data enters the GROUP BY execution stage, minimizing shuffle overhead and memory consumption.SUM(revenue)) reflect only the target time window and categories.Analytical query engines are engineered around columnar architectures. Selecting only specific dimensions and pruning rows early via WHERE conditions aligns with how the query optimizer schedules execution stages, outperforming queries that pass unfiltered row sets into memory-intensive aggregation steps.
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