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 needs to prepare a summary report in BigQuery that combines customer information with sales transactions. The database contains two tables:
customers (columns: customer_id, customer_name)orders (columns: order_id, customer_id, order_amount)The analyst must produce a structured dataset showing each customer's name, the total number of orders placed, and their total spending across all transactions.
Which SQL query should the analyst execute in BigQuery to generate this report?
project.dataset.customers AS c JOIN project.dataset.orders AS o ON c.customer_id = o.customer_id GROUP BY c.customer_name, o.order_id, o.order_amount;This query joins the two tables and includes every column in both the SELECT list and the GROUP BY clause without applying aggregate functions.
GROUP BY clause contains all available unique keys and attributes (including order_id), each output row represents an individual transaction rather than a summarized customer overview.total_orders and total_spent simply display raw order_id values and individual order amounts rather than aggregated counts and totals.COUNT() and SUM(), meaningful business metrics cannot be derived from granular transaction logs.The objective were simply to return a deduplicated list of every individual transaction along with the associated customer's name, without summarizing or calculating overall totals.
project.dataset.customers AS c JOIN project.dataset.orders AS o ON c.customer_id = o.customer_id;This query attempts to join the customers and orders tables and compute aggregate values (COUNT and SUM) alongside an unaggregated column (customer_name) without specifying a GROUP BY clause.
SELECT list must be included in a GROUP BY clause. Without GROUP BY c.customer_name, BigQuery cannot determine how to group the individual rows and will throw a syntax/semantic error.SELECT clause alongside aggregate functions unless an explicit GROUP BY is present.The requirement were to calculate the grand total spending and grand total order count across the entire company (empty dimension set), and the query omitted c.customer_name from the SELECT statement entirely.
project.dataset.customers AS c JOIN project.dataset.orders AS o ON c.customer_id = o.customer_id GROUP BY c.customer_name;This query combines tabular data from two distinct tables using a JOIN clause and applies aggregate functions (COUNT and SUM) grouped by a categorical attribute (customer_name) using a GROUP BY clause.
JOIN with the predicate ON c.customer_id = o.customer_id correctly merges the customers table with the orders table using their common key (customer_id).COUNT(o.order_id) aggregate function tallies the total number of order records associated with each individual customer.SUM(o.order_amount) aggregate function calculates the mathematical total of numeric order values for each customer.GROUP BY c.customer_name clause establishes the grouping boundary, ensuring that aggregation functions reduce multiple transaction rows down to a single summary row per customer.customer_name) as a dimension and numeric quantities (order_id counts, order_amount sums) as metrics.Executing transformations and aggregations directly in the underlying database ensures that downstream reports receive precise, pre-summarized data, minimizing processing overhead and avoiding query errors.
project.dataset.customers AS c JOIN project.dataset.orders AS o ON c.customer_id = o.customer_id GROUP BY o.order_amount;This query applies the SUM aggregate function to a text dimension (customer_name), groups by a metric field (order_amount), and computes an average instead of a sum for total spending.
customer_name is a non-numeric string dimension. Applying mathematical aggregation functions such as SUM() to non-numeric data causes an execution error.SUM), but this query uses AVG, which computes the average transaction size rather than the sum.order_amount organizes records by monetary value buckets rather than generating a breakdown by customer.COUNT or COUNT(DISTINCT).AVG instead of SUM distorts financial totals in reporting.The requirement was to group transactions by order amount buckets to analyze customer distribution, and if valid counting functions were applied to determine the number of transactions per bucket.
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