31 Days of BigQuery: Daily Challenges to Boost Your Skills

Ready to Flex Your BigQuery Muscles?

Welcome to Adswerve's 31 Days of BigQuery, where each day in May brings a new mini challenge to sharpen your skills, stretch your brain, and maybe even spark a little friendly competition. Whether you’re a BigQuery beginner or a seasoned SQL wizard, there’s something here for you.

These challenges were thoughtfully crafted (and solved!) by Adswerve’s Technical Services team—the behind-the-scenes problem solvers who build, architect and scale data solutions for our clients every day. Now, we're sharing that expertise with you.

Each day in May, we will post a new 10-minute BigQuery challenge for you to solve, with the solution provided the following day. The challenges will cover a wide variety of BigQuery features and will become more difficult as the month progresses. Feel free to reach out via our social channels with any questions.

As with many data problems, most of the challenges may have more than one solution; we would be excited to hear if your solution differs from ours.

Day 1 - Create your own BigQuery Instance

Challenge: 

For this first challenge, set up your own Google Cloud project, enable BigQuery, and query the public dataset. BigQuery is available in a free "sandbox" environment, limiting you to 10GB of storage and 1TB of monthly queries.

When ready, use standard SQL in the BigQuery studio to retrieve the top 5 most popular names in the US during the years 2010 and 2019 using the publicly available table  `bigquery-public-data.usa_names.usa_1910_current`. 

Tip
BigQuery comes with a large amount of publicly available data. I encourage you to explore these datasets; many have come in handy in the past.

Resources
https://cloud.google.com/bigquery/public-data
https://cloud.google.com/bigquery/docs/sandbox
https://console.cloud.google.com/marketplace/product/social-security-administration/us-names
https://www.w3schools.com/sql/

Solution

The 5 most popular names in the US during the period of 2010 to 2019 are Emma, Olivia, Noah, Sophia and Liam.


A possible SQL approach:

#1 Challenge
SELECT name, sum(number) as number
FROM `bigquery-public-data.usa_names.usa_1910_current`
WHERE year BETWEEN 2010 and 2019
GROUP BY 1
ORDER BY 2 DESC
LIMIT 5

Day 2 - Quickly Chart Query Outputs

On day two, we will explore an underutilized (personal opinion) feature hidden in the query output. While BigQuery allows you to open your query results in Looker Studio, Python notebooks, and data canvas, sometimes the quickest way to visualize is only a couple of clicks away in the "Chart" tab.

Challenge 
In this challenge, build a chart using the public dataset (`bigquery-public-data.usa_names.usa_1910_current` ) from the first challenge that visualizes the popularity of your name and a few other names throughout the years. See the screenshot below as an example of the expected output.

Tip
You will notice that while the Chart function is easy to use, it comes with limitations, such as only allowing a single dimension, but allowing up to 5 measures.

Solution

Because the built-in charting solution only allows a single dimension and multiple measures, I added each of the observed names as a sum(if metric. Make sure to order by year for the visualization to be rendered as expected.

#2 Challenge
SELECT 
  year, 
  sum(if(name = "Luka", number, 0)) as Luka  , 
  sum(if(name = "Peter", number, 0)) as Peter, 
  sum(if(name = "Luca", number, 0)) as Luca  ,
FROM `bigquery-public-data.usa_names.usa_1910_current` 
GROUP BY ALL
ORDER BY year ASC

Day 3 - Query Costs

Understanding query costs is crucial for managing your overall #BigQuery expenses. While the primary BQ charges are for storage and analysis (querying), advanced features like streaming inserts, BI Engine, Storage API, Notebook Runtimes, and BigQuery reservations can also contribute to costs as your usage evolves.

Challenge
Estimate the cost of the queries you wrote on days 1 and 2. In the on-demand pricing mode (most common), the cost is determined by the amount of data scanned for a query. Queries written in the UI (if valid) provide an estimate of how much data will be scanned in the query validator below the input window (see the screenshot).

Queries are charged at $6.25 per TiB or $0.00625 per GiB scanned.

Explore more
See how the price of queries changes with more or fewer columns selected, see what happens when using filters and limits. Think about how BigQuery’s columnar storage impacts the price of these queries.

Resources:
https://cloud.google.com/bigquery/docs/storage_overview
https://cloud.google.com/bigquery/pricing

Solution
The queries used in the previous challenges are both 143.71MB in size, which translates to 143.71MB => 0.00014371TB

0.00014371TB x $6.25/TB = $0.0008981875

Adding more columns increases the size of the queries, while using limit and filters does not impact the cost of the query due to BigQuery scanning each column fully.

Day 4 - Table Date Sharding

BigQuery supports table sharding as a way to divide your data, often seen with daily exports like Google Analytics 4 (events_YYYYMMDD). While you can query these daily tables individually, BigQuery lets you query them collectively using a wildcard (*) combined with the _TABLE_SUFFIX pseudo-column to filter specific date ranges.

Challenge
Using the public Google Analytics 4 dataset sample available at:
bigquery-public-data.ga4_obfuscated_sample_ecommerce run a query that provides the number of sessions (session_start events) between December 1 and December 31 2020.

Explore more
Keep in mind that while sharding is common in some exports, partitioning is generally the recommended approach in BigQuery for benefits like performance and easier management. We'll explore partitioning soon!

Resources
https://lnkd.in/d6tc5p8G
https://lnkd.in/daftABea

Solution

#4 Challenge
SELECT
 COUNTIF(event_name = "session_start") as sessions
FROM
 `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
 _table_suffix BETWEEN "20201201" AND "20211231"

Day 5 - Repeated Fields

The use of repeated fields in BigQuery is one of the biggest differences when comparing BQ to traditional (relational) SQL databases such as MySQL, PostgreSQL, etc. The array field in BigQuery not only introduces a new way to store data, but also challenges the need to normalize tables.

For example, a table of customers with their phone numbers, where each customer can have multiple phone numbers, would require two separate tables in a traditional database (per first normal form), whereas in BigQuery, customers' phone numbers can be stored in an Array (repeated) field for each customer.

In the previous challenge, we explored the Google Analytics 4 sharded tables. The events table stores event parameters as an Array (repeated field) of structs (container of multiple fields). In practice, each Google Analytics event has multiple event parameters. An array of structs is almost like a table within each row (event) and can be queried as a parameter.

Challenge
Complete the following query to produce an output of pageviews per page location for January 31, 2021 of the Google Analytics 4 sample table.

#4 Challenge
SELECT 
 (SELECT _________ FROM unnest(__________) WHERE key = "page_location") as page_location,
 countif(event_name = “page_view”) as page_views
FROM
 bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131
GROUP BY page_location

Solution
We were looking for value.string_value and event_params to fill the blanks. In the Google Analytics export's event parameters, values can come as different data types, that's why inside the value struct we specifically reference string_value.

SELECT
	(SELECT value.string_value FROM unnest(event_params) WHERE key = "page_location") as page_location,
	countif(event_name = “page_view”) as page_views
FROM
	bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131
GROUP BY page_location

Day 6 - Window Functions & CTEs

On day 5 we looked at how repeated fields (Arrays) work in BigQuery. Today, let's tackle Window Functions and Common Table Expressions (CTEs). 

- Window Functions: perform calculations across a set of rows that are somehow related. Unlike aggregate functions such as SUM, COUNT, AVG, etc. they do not collapse the rows. Instead, they return a value for each row based on a “window” they are in. Today, we’ll use them to create a new dimension for the Google Analytics 4 sample that will number events in a session.

- CTEs or Common Table Expressions defined using the WITH clause allow you to create a temporary, named query that can be referenced within the same query as any other table. This is incredibly useful to break down complex queries into more manageable and readable parts.

Challenge
Use a CTE to define a base query. The base query should include event_timestamp, event_name, geo.region,  device.web_info.browser, user_pseudo_id (unique user identifier) and ga_session_number (an integer value from event_param repeated field with the key ga_session_number. The GA Session Number indicates the number of sessions a user has had on your website or app (a counter that increases with each visit).

For each session number, the events in each session (starting with 1 for the first event in the session) are ordered by the event_timestamp.

If you’re new to window functions, the resources below has great examples to get you started.

Tip

Resources
Window Functions in BigQuery - https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls

Solution

 WITH base AS (
  SELECT  
    event_timestamp,
    geo.region,
    device.web_info.browser,
    event_name,
    user_pseudo_id,
    (SELECT value.int_value FROM unnest(event_params) WHERE key = "ga_session_number") as ga_session_number
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131` 
)

SELECT 
  *,
  ROW_NUMBER() OVER(PARTITION BY user_pseudo_id, ga_session_number ORDER BY event_timestamp) as hit_number
FROM base

Day 7 - Data Definition Language

Data definition language (DDL) allows you to create, delete, and alter resources in BigQuery. Think of DDL as part of SQL that provides a way to define and manage the structural aspects of your data warehouse (BigQuery). In BigQuery, many resources can be controlled through the UI, however, knowing how to build and manage resources using DDL will help you get quicker and allow you to automate some of these processes.

Challenge
Create a dataset (schema) and a table using DDL. Create your table in the newly created dataset on top of the query from the previous challenge using the AS keyword in the create table SQL.

Tips
You will have to use the following two DDL commands:
   - CREATE SCHEMA
   - CREATE TABLE AS SELECT 

Resources
https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language

Solution

#Create a dataset:
CREATE SCHEMA bqchallenges;

#Create a table based on a query
CREATE TABLE 
	bqchallenges.ga_data
AS
WITH base AS (
	SELECT
	event_timestamp,
	geo.region,
	device.web_info.browser,
	event_name,
	user_pseudo_id,
	(SELECT value.int_value FROM unnest(event_params) WHERE key = "ga_session_number") as ga_session_number
FROM 
	`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`
)

SELECT
	*,
	ROW_NUMBER() OVER(PARTITION BY user_pseudo_id, ga_session_number ORDER BY event_timestamp) as hit_number
FROM base;

Day 8 - Optimize with Partitioning & Clustering

On Day 3, we saw how query costs relate to the amount of data scanned in your query. Today, let's se optimize those costs and boost speed using partitioning and clustering!

Partitioning
Think of a partitioned table as a well-organized filing cabinet. Instead of one giant drawer, your table is divided into smaller, more manageable segments (partitions) based on the values in a specific partition column. When you query data and filter by the partition column (a date range), BigQuery can skip scanning irrelevant partitions entirely, leading to faster queries and lower costs.

In BigQuery, the partition column can be an integer, a time-unit, or the ingestion time. A table can have up to 10,000 partitions.

Clustering
If partitioning is like having separate drawers, clustering is like having alphabetically sorted files within those drawers. BigQuery sorts the data within storage blocks based on the values in one or more clustering columns. When you filter or aggregate on these columns, BigQuery can more efficiently locate the relevant blocks and rows. This is especially beneficial for columns with high cardinality that you frequently filter on.

In BigQuery you can specify up to four clustering columns. Unlike partitioning, there's no limit to the number of unique values in clustering columns. Take advantage of both for optimal performance!

Challenge
Create a partitioned and clustered table, by slightly rewriting yesterday’s solution below to include all the events_ tables using the wildcard notation. Then transform the table suffix to a date type using parse_date(“%Y%m%d”, _table_suffix) as table_date so it can be used as a partition. Use event name, browser and region as clustering columns

Explore More
See how using filtering by these columns when querying the newly created table changes the amount of data scanned. How does scanned data prediction compare between partitioning and clustering?

Resources
Partitioning - https://lnkd.in/gNRQwsFm
Clustering - https://lnkd.in/dhsjYDZp

Solution

CREATE TABLE bqchallenges.ga_data_par_clu
PARTITION BY table_date
CLUSTER BY event_name, browser, region
AS
WITH base AS (
 SELECT 
 parse_date("%Y%m%d", _table_suffix) as table_date,
 event_timestamp,
 geo.region,
 device.web_info.browser,
 event_name,
 user_pseudo_id,
 (SELECT value.int_value FROM unnest(event_params) WHERE key = "ga_session_number") as ga_session_number
 FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*` 
)

SELECT 
 *,
 ROW_NUMBER() OVER(PARTITION BY user_pseudo_id, ga_session_number ORDER BY event_timestamp) as hit_number
FROM base

Day 9 - Setting up hard limits

Despite careful query cost management and adherence to best practices, unexpected spikes can still occur. To prevent runaway costs,BigQuery provides hard limits on the daily data scanned at both project and project-user levels.

Challenge
Today's challenge is simple. In the Quotas & System Limits section of your GCP project, set a daily query usage limit.

Solution

1. Open your GCP project
2. Navigate to Quotas and System Limits
   
3. Filter for query usage
   
   
   
4. Check the quota you'd like to implement (per day or per user per day) and click edit
5. Update the value (uncheck unlimited) and click submit request
   
   

SELECT
  user_email,
  query,
  total_bytes_billed
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  creation_time BETWEEN "2025-05-01"
  AND "2025-05-31"
  AND job_type = "QUERY"
ORDER BY
  total_bytes_billed DESC
LIMIT
  10

 ALTER SCHEMA DATASET_NAME
 SET OPTIONS( storage_billing_model = 'BILLING_MODEL');
 

ST_GEOGPOINT(-73.985130, 40.758896)

SELECT 
  * 
FROM `bigquery-public-data.geo_us_boundaries.zip_codes` 
ORDER BY st_area(zip_code_geom) 
DESC LIMIT 5;

SELECT 
  zip_code, 
st_distance(ST_GEOGPOINT(-73.985130, 40.758896), zip_code_geom) as distance
FROM `bigquery-public-data.geo_us_boundaries.zip_codes` 
ORDER BY distance DESC;