BigQuery for analytics in GCP - Mini Project: Build & Apply

BigQuery for analytics

📖 Scenario: You work as a data analyst for a retail company. The company collects sales data in Google BigQuery. You want to analyze the sales data to find total sales per product category.

🎯 Goal: Create a BigQuery SQL query that calculates total sales amount for each product category from the sales dataset.

📋 What You'll Learn

Create a table variable with sales data including product category and sales amount

Add a filter condition to select sales only from the year 2023

Write a SQL query to sum sales amount grouped by product category

Complete the query with ORDER BY clause to sort results by total sales descending

💡 Why This Matters

🌍 Real World

Retail companies analyze sales data to understand product performance and make business decisions.

💼 Career

Data analysts and cloud engineers use BigQuery to process large datasets efficiently for reporting and insights.

1

Create sales data table variable

Create a BigQuery table variable called sales_data with columns product_category (string), sales_amount (integer), and sale_year (integer). Insert these exact rows: ('Electronics', 1000, 2023), ('Clothing', 500, 2023), ('Electronics', 1500, 2022), ('Clothing', 700, 2023), ('Books', 300, 2023).

GCP

# Create the sales_data table with specified columns and rows
# Your code here

Hint

Use CREATE TEMP TABLE to create the table and INSERT INTO to add rows.

2

Add filter for sales year 2023

Create a variable called filter_year and set it to 2023. This will be used to filter sales data for the year 2023.

GCP

CREATE TEMP TABLE sales_data (
  product_category STRING,
  sales_amount INT64,
  sale_year INT64
);

INSERT INTO sales_data (product_category, sales_amount, sale_year) VALUES
('Electronics', 1000, 2023),
('Clothing', 500, 2023),
('Electronics', 1500, 2022),
('Clothing', 700, 2023),
('Books', 300, 2023);

-- Define the filter year variable
-- Your code here

Hint

Use DECLARE to create a variable and set it to 2023.

3

Write SQL query to sum sales by category

Write a SQL query that selects product_category and the sum of sales_amount as total_sales from sales_data. Use WHERE sale_year = filter_year to filter data. Group the results by product_category.

GCP

CREATE TEMP TABLE sales_data (
  product_category STRING,
  sales_amount INT64,
  sale_year INT64
);

INSERT INTO sales_data (product_category, sales_amount, sale_year) VALUES
('Electronics', 1000, 2023),
('Clothing', 500, 2023),
('Electronics', 1500, 2022),
('Clothing', 700, 2023),
('Books', 300, 2023);

DECLARE filter_year INT64 DEFAULT 2023;

-- Write the SQL query to sum sales by product_category with filter
-- Your code here

Hint

Use SUM() to add sales_amount and GROUP BY product_category.

4

Add ORDER BY clause to sort results

Add an ORDER BY total_sales DESC clause to the existing SQL query to sort the results by total sales in descending order.

GCP

CREATE TEMP TABLE sales_data (
  product_category STRING,
  sales_amount INT64,
  sale_year INT64
);

INSERT INTO sales_data (product_category, sales_amount, sale_year) VALUES
('Electronics', 1000, 2023),
('Clothing', 500, 2023),
('Electronics', 1500, 2022),
('Clothing', 700, 2023),
('Books', 300, 2023);

DECLARE filter_year INT64 DEFAULT 2023;

SELECT product_category, SUM(sales_amount) AS total_sales
FROM sales_data
WHERE sale_year = filter_year
GROUP BY product_category
-- Add ORDER BY clause below
-- Your code here

Hint

Use ORDER BY total_sales DESC to sort from highest to lowest sales.

Practice

1. What is the main purpose of Google BigQuery in data analytics?

easy

A. To run fast SQL queries on large datasets without managing servers

B. To store files like images and videos

C. To create virtual machines for computing

D. To send emails automatically

Solution

1. Step 1: Understand BigQuery's role

   BigQuery is designed to analyze large amounts of data quickly using SQL without needing to manage servers.

2. Step 2: Compare options

   Options A, C, and D describe other services unrelated to BigQuery's main function.

3. Final Answer:

   To run fast SQL queries on large datasets without managing servers -> Option A

4. Quick Check:

   BigQuery = fast SQL analytics [OK]

Hint: BigQuery = fast SQL on big data, no servers needed [OK]

Common Mistakes:

• Confusing BigQuery with storage or compute services
• Thinking BigQuery manages virtual machines
• Assuming BigQuery sends emails

2. Which of the following is the correct syntax to select all columns from a table named sales_data in BigQuery?

easy

A. FETCH * sales_data;

B. GET ALL FROM sales_data;

C. SELECT * FROM sales_data;

D. SELECT ALL COLUMNS sales_data;

Solution

1. Step 1: Recall SQL SELECT syntax

   The correct SQL syntax to select all columns is SELECT * FROM table_name;.

2. Step 2: Check each option

   Only SELECT * FROM sales_data; matches the correct SQL syntax. Options A, B, and D use incorrect keywords or structure.

3. Final Answer:

   SELECT * FROM sales_data; -> Option C

4. Quick Check:

   SELECT * FROM table = correct syntax [OK]

Hint: Use SELECT * FROM table to get all columns [OK]

Common Mistakes:

• Using wrong keywords like GET or FETCH
• Omitting FROM keyword
• Writing SELECT ALL COLUMNS instead of SELECT *

3. Given the table orders with columns order_id, customer_id, and amount, what will this query return?

SELECT customer_id, SUM(amount) AS total_spent FROM orders GROUP BY customer_id ORDER BY total_spent DESC LIMIT 2;

medium

A. All orders sorted by amount

B. Top 2 customers who spent the most money

C. Total amount spent by all customers combined

D. First 2 orders by order_id

Solution

1. Step 1: Understand the query components

   The query groups orders by customer_id, sums the amount per customer, orders results by total spent descending, and limits output to 2 rows.

2. Step 2: Interpret the output

   This returns the top 2 customers who spent the most money.

3. Final Answer:

   Top 2 customers who spent the most money -> Option B

4. Quick Check:

   GROUP BY customer_id + SUM + ORDER BY DESC + LIMIT 2 = top 2 spenders [OK]

Hint: GROUP BY + SUM + ORDER BY DESC + LIMIT = top results [OK]

Common Mistakes:

• Thinking it returns all orders instead of grouped data
• Confusing LIMIT with total sum
• Ignoring ORDER BY direction

4. You wrote this query but get an error:

SELECT customer_id, amount FROM orders WHERE SUM(amount) > 100;

What is the problem?

medium

A. SELECT must include all columns in WHERE

B. Missing GROUP BY clause for customer_id

C. amount column does not exist

D. SUM() cannot be used in WHERE clause; use HAVING instead

Solution

1. Step 1: Identify misuse of SUM() in WHERE

   Aggregate functions like SUM() cannot be used in WHERE; they belong in HAVING clause.

2. Step 2: Correct usage

   Replace WHERE SUM(amount) > 100 with HAVING SUM(amount) > 100 and add GROUP BY customer_id.

3. Final Answer:

   SUM() cannot be used in WHERE clause; use HAVING instead -> Option D

4. Quick Check:

   Aggregates in HAVING, not WHERE [OK]

Hint: Use HAVING for conditions on aggregates, not WHERE [OK]

Common Mistakes:

• Using SUM() in WHERE clause
• Forgetting GROUP BY with aggregates
• Assuming WHERE filters aggregates

5. You want to find the average order amount per customer but only for customers who placed more than 5 orders. Which query correctly achieves this in BigQuery?

hard

A. SELECT customer_id, AVG(amount) AS avg_amount FROM orders GROUP BY customer_id HAVING COUNT(order_id) > 5;

B. SELECT customer_id, AVG(amount) AS avg_amount FROM orders WHERE COUNT(order_id) > 5 GROUP BY customer_id;

C. SELECT customer_id, AVG(amount) AS avg_amount FROM orders WHERE order_count > 5;

D. SELECT customer_id, AVG(amount) AS avg_amount FROM orders GROUP BY customer_id WHERE COUNT(order_id) > 5;

Solution

1. Step 1: Understand filtering on aggregate

   To filter groups by count, use HAVING clause after GROUP BY.

2. Step 2: Check each option's correctness

   SELECT customer_id, AVG(amount) AS avg_amount FROM orders GROUP BY customer_id HAVING COUNT(order_id) > 5; uses GROUP BY customer_id and HAVING COUNT(order_id) > 5 correctly. Options A, B, and C misuse WHERE or clause order.

3. Final Answer:

   SELECT customer_id, AVG(amount) AS avg_amount FROM orders GROUP BY customer_id HAVING COUNT(order_id) > 5; -> Option A

4. Quick Check:

   HAVING filters groups, WHERE filters rows [OK]

Hint: Use HAVING to filter groups after GROUP BY [OK]

Common Mistakes:

• Using WHERE to filter aggregates
• Placing WHERE after GROUP BY
• Referencing undefined columns like order_count