4.1 BigQuery SQL Syntax & Analytical Functions

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.

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]
)

1. 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.
2. ORDER BY: Establishes the physical and logical sequence of records within each partition. This is mandatory for ranking and navigation functions.
3. 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

1. 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.
2. 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).
3. 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.