4.1 BigQuery SQL Syntax & Analytical Functions

Google Cloud Associate Data Practitioner Exam

Track your progress as you read

Finished sections get a check mark.

1 Introduction & Exam Overview

About the Associate Data Practitioner Exam

2 Chapter 1: Data Processing Paradigms & Ingestion Strategies

1.1 ETL, ELT, and ETLT Transformation Paradigms 1.2 Data Transfer and Migration Tools 1.3 Data Quality, Cleaning, and Validation

3 Chapter 2: Data Formats & Storage Solution Selection

2.1 File Formats & Serialization Characteristics 2.2 Cloud Storage Buckets, Location Types & Storage Classes 2.3 Selecting Google Cloud Databases and Storage Services

4 Chapter 3: Loading Data & Modern Ingestion Architectures

3.1 Batch Loading Data into BigQuery 3.2 Streaming Ingestion & Deduplication Patterns 3.3 BigQuery External Tables, BigLake & Federated Queries 3.4 Schema Definition & Evolution for Loaded Data

5 Chapter 4: BigQuery SQL Analytics & Query Foundations

4.1 BigQuery SQL Syntax & Analytical Functions 4.2 Complex Data Types: Arrays, Structs, and JSON 4.3 Answering Business Questions with BigQuery: Worked Analyses

6 Chapter 5: BigQuery Performance Optimization & Cost Governance

5.1 Table Partitioning Strategies 5.2 Table Clustering & Data Skipping 5.3 Query Optimization, Execution Plans, and Cost Controls

7 Chapter 6: Interactive Analysis, Notebooks & Visual Presentation

6.1 Exploratory Analysis with Jupyter & Colab Enterprise 6.2 Looker Studio (Data Studio) Reporting & Dashboard Design 6.3 Enterprise BI with Looker & LookML Foundations 6.4 Building, Modifying & Sharing Looker Dashboards

8 Chapter 7: Machine Learning with BigQuery ML & Vertex AI

7.1 Planning an ML Project: BigQuery ML, AutoML, or Pretrained Models? 7.2 Creating, Training & Evaluating Models with BigQuery ML 7.3 Running Inference and Organizing Models in Model Registry 7.4 Using Pretrained Gemini Models from BigQuery with Remote Connections

9 Chapter 8: Pipeline Engines & Transformation Services

8.1 Transformation Engine Selection Matrix 8.2 Apache Beam & Cloud Dataflow Core Concepts 8.3 Stream Processing: Windowing, Watermarks, and Triggers

10 Chapter 9: Workflow Orchestration, Automation & Monitoring

9.1 SQL Pipelines with Dataform & Scheduled Queries 9.2 Workflow Orchestration with Cloud Composer & Workflows 9.3 Event-Driven Ingestion: Pub/Sub to BigQuery & Eventarc Triggers 9.4 Monitoring Pipelines: Dataflow Job UI, Cloud Logging & Cloud Monitoring

11 Chapter 10: Access Control, Governance & Data Sharing

10.1 IAM Principles & Role Hierarchies for Data Services 10.2 Cloud Storage Security & Access Controls 10.3 Fine-Grained Security: Column-Level & Row-Level Controls 10.4 Secure Data Sharing with Analytics Hub (BigQuery Sharing)

12 Chapter 11: Lifecycle Management, Reliability & Compliance

11.1 Object Lifecycle Management & Storage Tiering 11.2 High Availability & Disaster Recovery Architectures 11.3 Data Retention, Time Travel, and Point-in-Time Recovery 11.4 Encryption Key Management & Privacy Compliance

4.1 BigQuery SQL Syntax & Analytical Functions

Key Takeaways Explain This Topic AI

GoogleSQL (formerly called Google Standard SQL) is BigQuery's ANSI-compliant dialect; backticks quote identifiers that contain hyphens, such as project IDs.

Analytical window functions execute aggregations, rankings, and offsets over a defined frame without collapsing rows, preserving individual record granularity.

Ranking functions exhibit distinct tie-breaking semantics: ROW_NUMBER assigns continuous unique integers, RANK skips subsequent positions after duplicate ties, and DENSE_RANK assigns contiguous ranks without gaps.

The QUALIFY clause filters window function calculations directly within the main query block, eliminating the requirement for verbose derived subqueries or Common Table Expressions.

Common Table Expressions (WITH clauses) provide modular query organization that improves readability and maintainability, though BigQuery inlines them during query optimization.

Last updated: October 2026

BigQuery SQL Syntax & Analytical Functions

Core Focus: Mastering BigQuery's Google Standard SQL dialect requires understanding how analytical window functions evaluate calculations across partitions without collapsing records, how navigation functions compute period-over-period variances, and how the QUALIFY clause simplifies complex data deduplication pipelines.

BigQuery executes queries using GoogleSQL (earlier documentation called it Google Standard SQL; it follows the ANSI SQL:2011 standard). In enterprise data analytics, simple group-by aggregations often fall short when business logic demands running totals, moving averages, top-N deduplication, or period-over-period delta calculations. Analytical window functions and modular query constructs form the technical foundation for scalable in-warehouse data analysis.

Google Standard SQL Dialect Foundations

BigQuery natively supports Google Standard SQL, which is the default query syntax. While legacy SQL was supported in older environments, Google Standard SQL is required for advanced features such as complex data types, Common Table Expressions (CTEs), and windowing operations.

Identifier Quoting and Case Sensitivity

In Google Standard SQL, table identifiers and column names adhere to specific syntax and case rules:

Backtick Escaping: Identifiers containing hyphens, dots, or reserved keywords must be enclosed in backticks ( ). Fully qualified table names take the form project-id.dataset_name.table_name . Because Google Cloud project IDs often include hyphens (e.g., prod-analytics-2026`), failing to escape the qualified table reference causes a syntax error.

Case Sensitivity: Keywords (such as SELECT , FROM , WHERE ), function names, and column names are case-insensitive. Dataset and table names are case-sensitive by default (a dataset can be created as case-insensitive). String literals ('Active' vs. 'active') and JSON keys are case-sensitive.

Query Structure: The declarative evaluation follows a structured hierarchy from input sources to final output projection: SELECT [DISTINCT] select_list

FROM from_clause
WHERE condition
GROUP BY group_by_list
HAVING having_condition
QUALIFY qualify_condition
ORDER BY order_list
LIMIT count [OFFSET offset_value];
Aggregate Functions vs. Analytical Window Functions

To construct effective analytical queries, data practitioners must distinguish between aggregate functions and analytical window functions .

Feature Aggregate Functions ( GROUP BY ) Analytical Window Functions ( OVER )

Row Granularity Collapses multiple input rows into a single summary row per group. Preserves each original input row, appending calculated window metrics alongside row attributes.

Clause Requirement Requires a GROUP BY clause listing all non-aggregated projected columns. Requires an OVER clause defining partitions, ordering, and window frames.

Cardinality Output row count equals the number of distinct grouping keys. Output row count matches the input row count (unless filtered downstream).

Filtering Evaluated before the HAVING clause filter. Evaluated after WHERE , GROUP BY , and HAVING ; filtered via QUALIFY .

Anatomy of the OVER Clause

An analytical window function is defined by invoking an aggregate or analytical function followed immediately by an OVER clause: FUNCTION(argument) OVER (

[PARTITION BY partition_column, ...]
[ORDER BY sort_column [ASC|DESC], ...]
[ROWS|RANGE window_frame]
)

PARTITION BY : Divides the dataset into independent subsets (partitions) over which the function evaluates. If omitted, the entire dataset is treated as a single partition.

ORDER BY : Establishes the physical and logical sequence of records within each partition. This is mandatory for ranking and navigation functions.

Window Frame ( ROWS vs. RANGE ): Specifies the boundary of rows relative to the current row to include in the calculation (e.g., for rolling averages). -- Aggregate function: Collapses 10,000 transactions into 10 regional summaries

SELECT region, SUM(amount) AS total_sales
FROM `retail.transactions`
GROUP BY region;

- Analytical window function: Retains all 10,000 transactions, adding regional share
SELECT
transaction_id,
region,
amount,
SUM(amount) OVER (PARTITION BY region) AS regional_total_sales,
ROUND(amount / SUM(amount) OVER (PARTITION BY region) * 100, 2) AS pct_of_regional_sales
FROM `retail.transactions`;
Ranking Functions: ROW_NUMBER , RANK , and DENSE_RANK

Ranking functions assign an ordinal integer to rows within a partition based on an explicit ORDER BY specification. However, their tie-breaking behavior differs significantly when encountering identical ordering values: Value: 100 90 90 80 70

ROW_NUMBER: 1 2 3 4 5 (Strict sequential counter, no ties allowed)
RANK: 1 2 2 4 5 (Ties share rank; skips subsequent positions)
DENSE_RANK: 1 2 2 3 4 (Ties share rank; no gaps in sequence)
Detailed Function Comparison

ROW_NUMBER() : Generates a continuous sequence of unique integers starting from 1 for each partition. If two rows share identical values in the ORDER BY column, BigQuery assigns sequential row numbers non-deterministically unless secondary tie-breaking sort keys are specified.

RANK() : Assigns the same rank number to tied rows. The subsequent row receives a rank equal to its physical position, leaving a numerical gap equal to the number of tied rows minus one.

DENSE_RANK() : Assigns identical rank numbers to tied rows, but increments the next rank by 1 without gaps. This is ideal for competitive leaderboards (e.g., gold, silver, bronze tiers) where gaps would distort position tiers. SELECT

employee_id,
department_id,
salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk,
DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rnk
FROM `hr.salaries`;
Navigation Functions: LEAD and LAG

Navigation functions allow queries to access attributes from adjacent or distant rows within a window partition without performing self-joins.

LAG(expression [, offset [, default_value]]) : Accesses a value from a row that precedes the current row by offset positions within the ordered partition. If offset is omitted, it defaults to 1. If no preceding row exists, it returns default_value (or NULL if omitted).

LEAD(expression [, offset [, default_value]]) : Accesses a value from a row that follows the current row by offset positions.

Practical Application: Period-over-Period Variance

Calculating day-over-day revenue differences requires comparing the current day's metric against the previous calendar record within the same dimension: SELECT

store_id,
calendar_date,
daily_revenue,
LAG(daily_revenue, 1, 0.0) OVER (
PARTITION BY store_id
ORDER BY calendar_date ASC
) AS prior_day_revenue,
daily_revenue - LAG(daily_revenue, 1, daily_revenue) OVER (
PARTITION BY store_id
ORDER BY calendar_date ASC
) AS dod_revenue_variance
FROM `retail.daily_store_metrics`;
Window Frames: Cumulative Totals and Moving Averages

A window frame explicitly defines the subset of rows within a partition that contributes to an aggregation relative to the current row. Frames are declared using ROWS (physical row count) or RANGE (logical value ranges):

Common Frame Definitions

Unbounded Running Total: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW Calculates a cumulative sum from the very first row of the partition up to and including the current row.

7-Day Rolling Moving Average: ROWS BETWEEN 6 PRECEDING AND CURRENT ROW Averages the current row and the preceding 6 rows (a 7-row moving window).

Centered Moving Average: ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING Smooths localized volatility by averaging the current row with its immediate predecessor and successor.

Exam Trap: When an ORDER BY clause is present in an OVER specification without an explicit frame, Google Standard SQL sets a default frame of RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW . For aggregate functions like SUM() or AVG() , this can unintentionally compute a running total instead of a partition-wide aggregate! SELECT

customer_id,
order_date,
order_amount,
- Explicit cumulative running total
SUM(order_amount) OVER (
PARTITION BY customer_id
ORDER BY order_date ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cumulative_customer_spend,
- Moving average of this order and the previous 29 orders
AVG(order_amount) OVER (
PARTITION BY customer_id
ORDER BY order_date ASC
ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
) AS moving_avg_30_orders
FROM `sales.orders`;
Filtering Window Calculations: WHERE vs. QUALIFY

A frequent source of syntax errors for practitioners is attempting to filter the output of a window function inside a WHERE or HAVING clause.

The Logical Processing Order

To understand why this fails, consider the logical execution sequence of BigQuery SQL: 1. FROM / JOIN (Identify and join data sources)

2. WHERE (Filter individual input rows)
3. GROUP BY (Group rows into summary buckets)
4. HAVING (Filter grouped summary rows)
5. WINDOW FUNCTIONS (Compute analytical window metrics: OVER)
6. QUALIFY (Filter rows based on window function results)
7. DISTINCT (Eliminate duplicate output rows)
8. ORDER BY (Sort final projected results)
9. LIMIT / OFFSET (Truncate projected output)

Because the WHERE clause executes in Step 2, window functions (which evaluate in Step 5) do not yet exist when WHERE evaluates. Attempting WHERE ROW_NUMBER() OVER (...) = 1 results in a direct compilation error.

The Legacy Solution: Derived Subqueries or CTEs

Historically, analysts had to compute the window function inside a Common Table Expression (CTE) or subquery, and then apply a WHERE filter in the outer query: -- Verbose Legacy CTE Pattern

WITH RankedOrders AS (
SELECT
order_id,
customer_id,
order_date,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
FROM `sales.orders`
)
SELECT order_id, customer_id, order_date
FROM RankedOrders
WHERE rn = 1;
The Modern Solution: The QUALIFY Clause

Google Standard SQL introduces the QUALIFY clause, which evaluates immediately after analytical window functions (Step 6). It filters rows based on window results directly within the primary query block without requiring intermediate CTEs or wrapper subqueries: -- Streamlined QUALIFY Pattern for Deduplication

SELECT
order_id,
customer_id,
order_date,
order_amount
FROM `sales.orders`
WHERE order_status = 'COMPLETED'
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) = 1;
Modular SQL Design: Common Table Expressions (CTEs) vs. Subqueries

Enterprise analytical pipelines often involve multi-stage transformations. BigQuery provides two primary tools for structuring modular code: Common Table Expressions ( WITH clauses) and nested subqueries .

Common Table Expressions (CTEs)

CTEs define temporary, named result sets that exist only for the scope of the containing query: WITH DailyTotals AS (

SELECT
DATE(transaction_timestamp) AS tx_date,
store_id,
SUM(amount) AS daily_sales
FROM `retail.pos_events`
GROUP BY tx_date, store_id
),
StoreAverages AS (
SELECT
tx_date,
store_id,
daily_sales,
AVG(daily_sales) OVER (PARTITION BY store_id) AS avg_historical_sales
FROM DailyTotals
)
SELECT *
FROM StoreAverages
WHERE daily_sales > avg_historical_sales * 1.5;
CTE Execution and Inlining in BigQuery

Data practitioners must understand how BigQuery's optimizer processes CTEs:

Inlining Behavior: In BigQuery, a CTE is generally not materialized into a physical temporary table by default. Instead, the query optimizer inlines the CTE definition directly into the execution graph. If a CTE is referenced three times in subsequent queries, BigQuery's query planner may execute that CTE's underlying scan and transformation three distinct times unless the optimizer determines it can share intermediate states.

Readability vs. Materialization: While CTEs greatly enhance readability, maintainability, and testing over deeply nested subqueries, they do not serve as an automatic caching mechanism. If a costly CTE must be queried multiple times across separate queries or complex joins, persisting the intermediate result into a temporary table ( CREATE TEMP TABLE ) or physical staging table is more cost-effective.

Common Exam Traps & Practitioner Scenarios

Exam Tip: Keep the logical processing sequence in mind on the exam. Questions frequently test your ability to recognize invalid SQL statements that attempt to filter window functions using WHERE instead of QUALIFY .

Trap 1: Using Window Functions in WHERE or HAVING

The Trap: An exam scenario asks how to retrieve only the top 3 transactions per region, providing a query with WHERE RANK() OVER (...) <= 3 .

The Reality: Window functions cannot appear in WHERE or HAVING clauses. The query will fail during SQL parsing. The correct syntax is either a QUALIFY clause in the same query block or a CTE with an outer WHERE filter.

Trap 2: Omitting ORDER BY in Navigation Functions

The Trap: Attempting to call LEAD(column) or LAG(column) with only a PARTITION BY clause.

The Reality: Navigation functions inherently require a sequential order to define what "preceding" or "following" means. Omitting the ORDER BY clause inside the OVER specification causes a compilation error.

Trap 3: Deduplication with Ties ( RANK vs. ROW_NUMBER )

The Trap: Using RANK() OVER (PARTITION BY id ORDER BY updated_at DESC) in a QUALIFY filter set to = 1 to deduplicate records.

The Reality: If two duplicate records contain identical timestamps, RANK() assigns 1 to both records. Filtering on = 1 fails to eliminate duplicates. ROW_NUMBER() guarantees that only one record receives the rank of 1, ensuring true deduplication.