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

Need a 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

Need a 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

Need a 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

Need a hint?

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