BigQuery Query Examples: 13 SQL Patterns Every Data Team Should Know

By Ari Heber
July 29, 2026 | 5 min read
Laptop showing a BigQuery SQL query beside dashboards illustrating joins, group by, ranking and window functions

Looking for a BigQuery query example you can actually copy, adopt, and run? You’re in the right place.

This is a working BigQuery query example reference guide. There are thirteen common SQL patterns below, each with a runnable example against BigQuery’s public datasets, plus a note on how that pattern affects the bytes BigQuery scans, because that’s what determines what the query actually costs you.

BigQuery bills $6.25 per TiB scanned under on-demand pricing, with the first 1 TiB each month free. The examples either use BigQuery public datasets or simplified sample tables that illustrate the SQL syntax you’ll use in production.

Read on to learn the most common BigQuery SQL query examples.

13 Common BigQuery Query Examples

The examples below progress from basic SQL queries to more advanced analytical queries. If you’re new to BigQuery, read them in order. If you’re looking for a specific technique, jump directly to that section.

1. A Basic SELECT Query for Specific Columns

Start with the pattern you’ll write the most often: pulling a handful of columns from one table.

SELECT
  name,
  gender,
  number
FROM
  `bigquery-public-data.usa_names.usa_1910_2013`
WHERE
  state = 'CA'
ORDER BY
  number DESC
LIMIT 10;

BigQuery is columnar, so it only reads the columns you name in SELECT, WHERE, and other clauses, not the whole row. SELECT * forces a scan of every column in the table, which is usually the single biggest lever you have over cost before you touch anything else.

For example, on a table with 40 columns, selecting the three you need instead of * can cut bytes scanned by an order of magnitude.

2. Filtering Rows with WHERE

WHERE clauses are also one of the primary ways BigQuery prunes partitions, making them one of the most important performance optimization tools available. Filtering looks simple, but where you put the filter changes what BigQuery actually reads.

SELECT
  order_id,
  customer_id,
  order_total
FROM
  `project.dataset.orders`
WHERE
  order_status = 'completed'
  AND order_total > 100;

PRO TIP: LIMIT doesn’t reduce cost. BigQuery scans the full set of columns and rows implied by the query, then applies LIMIT to what it returns to you. SELECT * FROM orders LIMIT 10 on a 2 TB table scans 2 TB, same as if there were no LIMIT at all. If you want to preview data, use the table preview in the BigQuery console instead. It doesn’t trigger a scan.

3. Aggregating Data with GROUP BY and HAVING

Aggregation is where BigQuery earns its keep on large datasets.

SELECT
  corpus,
  COUNT(*) AS word_count,
  SUM(word_count) AS total_occurrences
FROM
  `bigquery-public-data.samples.shakespeare`
GROUP BY
  corpus
HAVING
  total_occurrences > 5000
ORDER BY
  total_occurrences DESC;

GROUP BY determines what gets aggregated, and HAVING filters the aggregated results after grouping. That’s different from WHERE, which filters rows before grouping happens.

Put your row-level filters in WHERE wherever you can. Filtering before aggregation means BigQuery does less work per group, and on wide tables, less work per group adds up fast.

4. Inner Join Across Two Tables

Joins are usually where a query’s cost profile changes the most, since you’re now scanning two tables instead of one.

SELECT
  o.order_id,
  o.order_total,
  c.customer_name,
  c.customer_region
FROM
  `project.dataset.orders` AS o
INNER JOIN
  `project.dataset.customers` AS c
ON
  o.customer_id = c.customer_id
WHERE
  o.order_date >= '2026-01-01';

Although BigQuery’s optimizer often pushes filters down automatically, writing queries that filter large tables as early as possible makes your intent clearer and can reduce the amount of data processed in complex jobs.

If you write the date filters as an outer WHERE clause applied after the join, BigQuery has already joined the full, unfiltered orders table against customers before the filter does anything. Pushing filters into a subquery ahead of the join, or relying on BigQuery’s query optimizer to push them down automatically, keeps the join itself working against the smaller set of rows.

5. LEFT JOIN to Find Unmatched Records

LEFT JOIN is the pattern for “give me everything on the left side, matched or not,” which comes up constantly in data quality checks.

SELECT
  o.order_id,
  o.customer_id
FROM
  `project.dataset.orders` AS o
LEFT JOIN
  `project.dataset.customers` AS c
ON
  o.customer_id = c.customer_id
WHERE
  c.customer_id IS NULL;

This finds every order with a customer_id that has no matching row in customers, a broken foreign key you’d want to catch before it breaks a downstream dashboard. Same join-order logic applies here as with INNER JOIN: narrow both tables with WHERE clauses before the join runs whenever you can, rather than filtering the joined result afterward.

6. Window Function for Running Totals and Rankings

Window functions calculate a value across a set of rows related to the current row, without collapsing those rows into a single output row the way GROUP BY does.

SELECT
  order_date,
  region,
  daily_revenue,
  SUM(daily_revenue) OVER (
    PARTITION BY region
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total,
  RANK() OVER (
    PARTITION BY region
    ORDER BY daily_revenue DESC
  ) AS revenue_rank
FROM
  `project.dataset.daily_regional_revenue`;

PARTITION BY splits the data into groups (here, by region), and the window function runs independently within each group. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW defines a running window from the start of the partition to the current row, which is what gives you the running total.

RANK() is one of the several numbering functions BigQuery supports alongside ROW_NUMBER() and DENSE_RANK(), useful for “top N per group” queries.

7. CTEs for Multi-Step Query Logic

Common table expressions let you break a complex query into named, sequential steps instead of nesting subqueries inside each other.

WITH regional_totals AS (
  SELECT
    region,
    SUM(order_total) AS total_revenue
  FROM
    `project.dataset.orders`
  WHERE
    order_date >= '2026-01-01'
  GROUP BY
    region
),
ranked_regions AS (
  SELECT
    region,
    total_revenue,
    RANK() OVER (ORDER BY total_revenue DESC) AS revenue_rank
  FROM
    regional_totals
)
SELECT *
FROM ranked_regions
WHERE revenue_rank <= 5;

CTEs don’t change what BigQuery scans compared to an equivalent subquery; they’re a readability tool, not a performance one. But readable multi-step logic is easier to review for the filter-placement issues in the two join examples above, which is where the real cost differences come from. If your team can’t tell you where your filters sit relative to your joins, they can’t catch an expensive query in review.

8. Filtering a Partitioned Table by Date

This is the single highest-leverage pattern on this list for controlling cost, and it depends on how the table was built, not just how you write the query.

SELECT
  event_type,
  COUNT(*) AS event_count
FROM
  `project.dataset.events`
WHERE
  DATE(event_timestamp) BETWEEN '2026-06-01' AND '2026-06-07'
GROUP BY
  event_type;

If events are partitioned on event_timestamp, this query only reads the partitions covering that seven-day window, not the full table. On a table holding two years of event data, filtering by a date range after partitioning routinely cuts bytes scanned by 90% or more. If the table isn’t partitioned, the query scans everything regardless of how tightly you filter.

Our BigQuery optimization technique guide covers how to set up partitioning and clustering on existing tables, alongside the require_partition_filter option that blocks accidental full scans outright.

9. Querying Nested and Repeated Fields with UNNEST

BigQuery natively supports nested (STRUCT) and repeated (ARRAY) fields, which means related data can live inside a single row instead of a separate table you’d otherwise have to join.

SELECT
  order_id,
  item.product_name,
  item.quantity
FROM
  `project.dataset.orders`,
  UNNEST(line_items) AS item
WHERE
  item.quantity > 1;

If line_items is an array of structs on the order table, UNNEST flattens it into one row per item, so you can filter and select fields inside the array as if it were a joined table.

The advantage over a real join? No separate line items table to scan and match, since the data already lives with the parent row. This is a genuinely different way to model one-to-many relationships in BigQuery compared to a normalized schema, and it’s worth considering at design time if you’re migrating from a row-oriented database.

10. Querying Sharded Tables with Wildcard Syntax

Some datasets, including several BigQuery public datasets, are split across multiple tables sharded by date or category instead of stored as one partitioned table. Wildcard tables let you query across the shards at once.

SELECT
  COUNT(*) AS total_rows
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20170701' AND '20170731';

_TABLE_SUFFIX is a pseudocolumn that lets you filter which shards get scanned, similar in spirit to filtering a partitioned table by date.

That said, if you’re designing a new table from scratch, a native partitioned table is almost always the better choice over date-sharded tables. Sharded tables carry separate metadata and schema per shard, which adds overhead and makes the dataset harder to manage as it grows. Wildcard syntax is most useful for querying datasets you don’t control that are already sharded, not for new table design.

11. Parameterized Queries for dbt Models and Pipelines

Hardcoding values into query strings is fine for ad hoc analysis and a bad idea in anything that runs on a schedule or takes user input. Parameterized queries separate the SQL from the values.

SELECT
  customer_id,
  order_total
FROM
  `project.dataset.orders`
WHERE
  order_date = @target_date
  AND region = @target_region;

Called from the bq CLI, a client library, or a dbt model, @target_date and @target_region get bound to actual values at execution time rather than string-interpolated into the query text. Beyond the obvious injection of risk of building queries with string concatenation, parameterization means BigQuery can reuse the query plan across calls instead of treating each interpolated string as a distinct query, which matters if the same pattern runs hundreds of times a day across a dbt project or an orchestrated pipeline.

12. Checking query Cost Before You Run It

Every pattern above affects bytes scanned differently, and you don’t have to guess which one you landed on. BigQuery can estimate the cost of a query before it runs.

bq query \
  --use_legacy_sql=false \
  --dry_run \
  'SELECT * FROM `project.dataset.large_table` WHERE event_date = "2026-06-01"'

A dry run returns the number of bytes the query would process without actually running it or incurring any charge. In the console, the same estimate appears automatically above the query editor as you type, updating live as you add filters or columns.

Dry runs are especially useful during development because they estimate bytes processed without consuming query quotas or incurring charges.

Get in the habit of checking that number before running anything against a table you know is large. It catches the missing partition filter of the leftover SELECT * before it shows up on next month’s bill instead of after.

13. Using QUALIFY

QUALIFY filters the results of window functions without requiring an extra CTE or subquery, making many ranking queries shorter and easier to read.

SELECT
  customer_id,
  order_total,
  ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY order_total DESC
  ) AS rn
FROM
  `project.dataset.orders`
QUALIFY rn = 1;

Writing Good Queries Solves Half the Problem

Every pattern above is something you control at the SQL level:

  • Which columns you select
  • Where you place your filters relative to your joins
  • Whether your tables are partitioned
  • Whether you’re scanning shards you don’t need

Get these right and you’ll cut byte scanning significantly on most workloads. That’s real, and it’s worth doing regardless of anything else in this article.

But bytes scanned is only one side of what determines your BigQuery bill. The other side is which pricing model the scan runs against.

The exact same well-written query costs a predictable, capped amount on committed slot capacity and a variable, usage-based amount on on-demand pricing, and the right answer depends on workload patterns that shift by the hour: a dashboard refresh at 9am, a dbt run at 2am, and an analyst’s exploratory query at 4pm. Query-level optimization doesn’t touch that decision. It has to be made, and remade, at the job level.

Where Query-Level Optimization Runs Out

Most teams solve the pricing-model question once, manually, and then leave it alone. They pick on-demand because it’s simple, or they buy a fixed reservation because their workload feels big enough to justify it, and that choice sits static for months while actual usage drifts beneath it. Idle reservation capacity gets paid no matter if it’s used or not. Spiky on-demand workloads get billed at full rate even during the hours they could have run more cheaply on reserved slots.

Once you’ve optimized the SQL itself, the remaining opportunity is deciding which BigQuery pricing model should execute the job.

That’s the layer Yuki operates on.

Every well-optimized query you write still has to run somewhere, and Yuki routes each BigQuery job to whichever compute option, on-demand or reservation, is more efficient for that specific job in real time, based on bytes scanned and current system load. No query rewrites. No changing how your dbt models or Airflow DAGs are written. It sits underneath the queries in this article, and it’s part of the cost equation that SQL alone can’t reach no matter how carefully you write it.

If you want to see what that split actually looks like on your workloads, Yuki’s free BigQuery cost analysis shows you where your queries are landing today and what routing them dynamically would save.

BigQuery Query Examples FAQ

What language does BigQuery use?

BigQuery uses Google SQL (formerly Standard SQL), which closely follows ANSI SQL while adding extensions like STRUCT, ARRAY, UNNEST, and QUALIFY.

Can I use SQL in BigQuery?

Yes, you can use SQL in BigQuery. BigQuery supports GoogleSQL for analytical workloads and also offers a legacy SQL dialect for backwards compatibility, although Google recommends GoogleSQL for new development.

How do I test a BigQuery query without paying?

You can test a BigQuery query without paying by using a dry run or running the query in BigQuery sandbox.

Does LIMIT reduce BigQuery cost?

No, LIMIT does not reduce BigQuery cost. LIMIT reduces rows returned, not bytes scanned.

By Ari Heber
Founding engineer at Yuki. I build large-scale data platforms and AI infrastructure. Cost optimization across cloud and query engines has followed me throughout my career, and today I focus on making AI systems work reliably on real data and real workloads.

Table of Contents

Free cost analysis

Take 5 minutes to learn how much money you can save on your Snowflake account.

By clicking Submit you’re confirming that you agree with our Terms and Conditions.

Follow us on LinkedIn

Related posts

Free cost analysis

Take 5 minutes to learn how much money you can save on your Snowflake account.

By clicking Submit you’re confirming that you agree with our Terms and Conditions.

Skip to content