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?

SELECT c.customer_name, COUNT(o.order_id) AS total_orders, SUM(o.order_amount) AS total_spent FROM `project.dataset.customers` AS c JOIN `project.dataset.orders` AS o ON c.customer_id = o.customer_id GROUP BY c.customer_name;: ### **What this SQL query is** 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. ### **How it meets the requirements** - **Combines disparate tables (✓):** The `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`). - **Calculates order volume (✓):** The `COUNT(o.order_id)` aggregate function tallies the total number of order records associated with each individual customer. - **Calculates total spending (✓):** The `SUM(o.order_amount)` aggregate function calculates the mathematical total of numeric order values for each customer. - **Summarizes by customer dimension (✓):** The `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. ### **Key advantages and capabilities** - **Dataset-level transformation:** Aggregating and joining directly within BigQuery processes large volumes of raw transactional data efficiently before ingestion into downstream business intelligence tools. - **Dimension and metric separation:** Correctly treats categorical text (`customer_name`) as a dimension and numeric quantities (`order_id` counts, `order_amount` sums) as metrics. - **High performance:** Relies on BigQuery's distributed SQL execution engine to process millions of records quickly. ### **Why this is optimal** 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.

StudyCerts

Sql Bigquery Aggregation Join Customer Orders | 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

Sql Bigquery Aggregation Join Customer Orders

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 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?

SELECT c.customer_name, COUNT(o.order_id) AS total_orders, SUM(o.order_amount) AS total_spent FROM project.dataset.customers AS c JOIN project.dataset.orders AS o ON c.customer_id = o.customer_id GROUP BY c.customer_name;

SELECT c.customer_name, o.order_id AS total_orders, o.order_amount AS total_spent FROM 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;

SELECT c.customer_name, SUM(c.customer_name) AS total_orders, AVG(o.order_amount) AS total_spent FROM project.dataset.customers AS c JOIN project.dataset.orders AS o ON c.customer_id = o.customer_id GROUP BY o.order_amount;

Associate Data Practitioner

Sql Bigquery Aggregation Join Customer Orders

Related questions