BigQuery SQL and pricing model in GCP - Mini Project: Build & Apply

BigQuery SQL and Pricing Model

📖 Scenario: You work as a data analyst for a retail company. Your manager wants you to analyze sales data stored in Google BigQuery. To keep costs low, you need to understand how BigQuery pricing works and write SQL queries that minimize data scanned.

🎯 Goal: Build a simple BigQuery SQL query to calculate total sales per product category, while considering the pricing model by filtering data efficiently.

📋 What You'll Learn

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

Add a filter configuration variable to select a specific product category

Write a SQL query that sums sales amount grouped by product category using the filter

Add a LIMIT clause to restrict the number of rows returned

💡 Why This Matters

🌍 Real World

BigQuery is a cloud data warehouse where you run SQL queries on large datasets. Understanding how to write efficient queries and use filters helps reduce the amount of data scanned, lowering costs.

💼 Career

Data analysts and cloud engineers use BigQuery daily to analyze data. Knowing how to write filtered queries and apply limits is essential to optimize performance and control expenses.

1

Create sales data table variable

Create a BigQuery table variable called sales_data with these exact columns and values: product_category with values 'Electronics', 'Clothing', 'Books', and sales_amount with values 1000, 500, 300 respectively.

GCP

# Create the sales_data table variable with product_category and sales_amount columns
# Your code here

Need a hint?

Use a list of dictionaries to represent the table rows with exact keys and values.

2

Add filter configuration variable

Add a variable called selected_category and set it to the string 'Electronics' to filter sales data by this category.

GCP

sales_data = [
    {'product_category': 'Electronics', 'sales_amount': 1000},
    {'product_category': 'Clothing', 'sales_amount': 500},
    {'product_category': 'Books', 'sales_amount': 300}
]
# Add the selected_category variable below
# Your code here

Need a hint?

Assign the exact string 'Electronics' to the variable selected_category.

3

Write SQL query to sum sales by category with filter

Write a SQL query string variable called query that selects product_category and the sum of sales_amount as total_sales from sales_data, filtering where product_category equals selected_category, and grouping by product_category.

GCP

sales_data = [
    {'product_category': 'Electronics', 'sales_amount': 1000},
    {'product_category': 'Clothing', 'sales_amount': 500},
    {'product_category': 'Books', 'sales_amount': 300}
]
selected_category = 'Electronics'
# Write the SQL query string variable called query below
# Your code here

Need a hint?

Use an f-string to insert the selected_category variable inside the WHERE clause.

4

Add LIMIT clause to SQL query

Add a LIMIT 1 clause at the end of the query string to restrict the number of rows returned to 1.

GCP

sales_data = [
    {'product_category': 'Electronics', 'sales_amount': 1000},
    {'product_category': 'Clothing', 'sales_amount': 500},
    {'product_category': 'Books', 'sales_amount': 300}
]
selected_category = 'Electronics'
query = f"""
SELECT product_category, SUM(sales_amount) AS total_sales
FROM sales_data
WHERE product_category = '{selected_category}'
GROUP BY product_category
"""
# Add LIMIT 1 clause to the query string below
# Your code here

Need a hint?

Append the line LIMIT 1 at the end of the SQL query string.