A business analyst is analyzing an e-commerce orders dataset in BigQuery. The retail operations manager asks for a regional performance breakdown to answer two specific business questions: What is the total monetary revenue generated by each sales region? What is the average order value across transactions in each sales region? The dataset orders contains columns order_id, region, and order_amount. Which GoogleSQL query should the analyst write to segment the dataset and answer both business inquiries?

SELECT region, SUM(order_amount) AS total_revenue, AVG(order_amount) AS average_order_value FROM `project.dataset.orders` GROUP BY region: ### **What the solution is:** This SQL query combines standard **aggregate functions** (`SUM` and `AVG`) with a **`GROUP BY` clause** in GoogleSQL to compute descriptive statistics categorized across distinct attribute values. ### **How it meets the requirements:** - **Total revenue calculation (✓):** The `SUM(order_amount)` function aggregates the numerical values within `order_amount` by adding them together for each group, producing the total monetary revenue. - **Central tendency calculation (✓):** The `AVG(order_amount)` function calculates the arithmetic mean of `order_amount` for each group, determining the average transaction size. - **Regional segmentation (✓):** The `GROUP BY region` clause partitions the rows into subsets based on unique values in the `region` column, ensuring both summary metrics are computed independently per region. ### **Key advantages and capabilities:** - **Native BigQuery optimization:** Standard aggregate functions like `SUM` and `AVG` run efficiently across distributed data partitions in BigQuery. - **Deterministic segmentation:** Grouping by categorical dimensions allows multidimensional slicing of large transactional tables into compact summary records. - **Direct business answers:** Producing both aggregate sum totals and central tendencies provides immediate insight into regional volume versus individual purchasing behavior. ### **Why this is optimal:** In standard SQL syntax used by BigQuery, whenever non-aggregated columns such as `region` appear in the `SELECT` list alongside aggregation functions like `SUM()` and `AVG()`, they must be specified in the `GROUP BY` clause. This query adheres strictly to standard SQL semantics while utilizing the correct, built-in aggregation functions.

StudyCerts

Bigquery Sql Aggregation Regional Revenue Average | 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 Aggregation Regional Revenue Average

Section: Analyze data to answer business questions

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 business analyst is analyzing an e-commerce orders dataset in BigQuery. The retail operations manager asks for a regional performance breakdown to answer two specific business questions:

What is the total monetary revenue generated by each sales region?
What is the average order value across transactions in each sales region?

The dataset orders contains columns order_id, region, and order_amount.

Which GoogleSQL query should the analyst write to segment the dataset and answer both business inquiries?

SELECT region, TOTAL(order_amount) AS total_revenue, MEAN(order_amount) AS average_order_value FROM project.dataset.orders GROUP BY region

SELECT region, SUM(order_amount) AS total_revenue, AVG(order_amount) AS average_order_value FROM project.dataset.orders GROUP BY region

SELECT region, SUM(order_amount) AS total_revenue, AVG(order_amount) AS average_order_value FROM project.dataset.orders ORDER BY region

SELECT region, COUNT(order_amount) AS total_revenue, AVG(order_amount) AS average_order_value FROM project.dataset.orders GROUP BY region

Associate Data Practitioner

Bigquery Sql Aggregation Regional Revenue Average

Related questions