A Practical Guide to Standard SQL in BigQuery

BigQuery is built to query enormous datasets quickly, and the language you talk to it in is Standard SQL. If you have written SQL before, most of this will feel familiar, because Standard SQL follows the ANSI SQL 2011 standard. What makes it worth a dedicated guide is the combination of two things: the modern features it layers on top, such as window functions, common table expressions, and first-class handling of arrays and nested data, and the fact that in BigQuery you pay for the bytes your query scans. So writing good SQL here is not only about correctness, it is also about cost. This guide walks through the building blocks and finishes with the habits that keep your queries fast and cheap.

A quick note on terminology. Standard SQL is the default in BigQuery, as opposed to the older Legacy SQL, and unless you have deliberately opted into Legacy, everything below is what you are already using.

The shape of a query

Every query starts from the same core: choose the columns, name the table, and optionally filter the rows. One BigQuery-specific habit is how tables are referenced, using backticks around the fully qualified project.dataset.table name.

SELECT first_name, last_name
FROM `myproject.crm.contacts`
WHERE country = 'Canada'

From there, three clauses do most of the everyday work. LIMIT caps how many rows come back, which is handy for a quick look at a table. WHERE filters rows by condition, and you can chain conditions with AND and OR. ORDER BY sorts the result, with DESC for descending order.

SELECT name, score
FROM `myproject.assessments.results`
ORDER BY score DESC
LIMIT 10

It is worth knowing that LIMIT does not reduce the data BigQuery scans, only the rows it returns, so it is great for previewing but not a cost-saving tool on its own.

Summarising with aggregations

Aggregations collapse many rows into summary numbers, using functions like COUNT, SUM, and AVG together with GROUP BY to say what you are grouping over.

SELECT country, COUNT(*) AS total_users
FROM `myproject.crm.contacts`
GROUP BY country
ORDER BY total_users DESC

That example also shows aliases, the AS keyword renaming a column in the output. Aliases are not just cosmetic, they let you name computed columns so you can reference and read them easily.

SELECT first_name AS name, salary * 1.10 AS adjusted_salary
FROM `myproject.hr.staff`

Working with dates

Date filtering is constant in analytics, and the BETWEEN operator paired with the DATE function is the clean way to bound a range. Wrapping the column in DATE ensures you are comparing dates to dates even when the underlying column is a timestamp.

SELECT *
FROM `myproject.sales.orders`
WHERE DATE(order_date) BETWEEN '2024-01-01' AND '2024-03-31'

Common table expressions

As queries grow, common table expressions, written with a WITH clause, keep them readable by naming an intermediate result and then querying it like a table. They are the antidote to deeply nested subqueries.

WITH top_customers AS (
  SELECT customer_id, SUM(amount) AS total_spent
  FROM `myproject.sales.transactions`
  GROUP BY customer_id
)
SELECT *
FROM top_customers
WHERE total_spent > 1000

Reading top to bottom, you first define what a top customer is by summing their spend, then select from that named result and keep only the high spenders. Breaking logic into named steps like this is one of the most effective ways to make complex SQL maintainable.

Combining tables with joins

Joins bring rows from two tables together on a matching key. An inner join keeps only rows that match in both, while a left join keeps every row from the first table and fills in nulls where the second has no match, which is exactly what you want when some users have no orders yet.

SELECT u.user_id, u.name, o.total_orders
FROM `myproject.crm.users` AS u
LEFT JOIN `myproject.sales.orders` AS o
ON u.user_id = o.user_id

The short table aliases, u and o, keep the column references tidy and make clear which table each column comes from.

Handling missing values

Real data has gaps, and IFNULL is the simplest way to substitute a fallback when a value is null, so your output is clean rather than full of blanks.

SELECT name, IFNULL(city, 'Unknown') AS city
FROM `myproject.crm.contacts`

Nested and repeated data

This is where BigQuery departs from a traditional database, and it is one of its real strengths. A single row can contain an array of values or a nested record, which lets you model things like a purchase with many items without a separate table. To work with an array you unnest it, which expands each element into its own row, joined back to the parent.

SELECT user_id, item
FROM `myproject.sales.purchases`,
UNNEST(items) AS item

Nested records, called structs, are accessed with dot notation, reaching into the record for the field you want.

SELECT user.name, user.age
FROM `myproject.crm.users`

Getting comfortable with UNNEST and dot notation is essential in BigQuery, because exports such as the GA4 data are stored exactly this way.

Window functions

Window functions are among the most powerful features in Standard SQL. They compute a value across a set of rows related to the current one, without collapsing the result the way a GROUP BY does. The OVER clause defines that window, with PARTITION BY splitting the data into groups and ORDER BY setting the order within each.

SELECT name, department,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM `myproject.hr.staff`

This ranks employees by salary within each department, so every row keeps its detail while also gaining its rank. Running totals, moving averages, and per-group rankings all come from this one construct.

Conditional logic

A CASE expression is SQL’s if-then-else, mapping values into categories inline.

SELECT name,
  CASE
    WHEN score >= 90 THEN 'A'
    WHEN score >= 80 THEN 'B'
    ELSE 'C'
  END AS grade
FROM `myproject.assessments.exams`

The conditions are checked in order, and the first one that matches wins, with ELSE catching everything left over.

Saving logic as a view

When a query becomes something you run often, you can save it as a view, which behaves like a virtual table defined by a query. Anyone querying the view runs the underlying SQL freshly each time, so it always reflects current data.

CREATE OR REPLACE VIEW `myproject.sales.view_top_customers` AS
SELECT customer_id, SUM(amount) AS total_spent
FROM `myproject.sales.transactions`
GROUP BY customer_id

BigQuery-specific features

A few things are particular to BigQuery and worth knowing. Large tables are often partitioned by date, and filtering on the partition column is the single biggest lever for both speed and cost, because BigQuery then reads only the relevant partitions instead of the whole table.

SELECT *
FROM `myproject.app.logs`
WHERE _PARTITIONDATE = '2025-04-04'

Parameters let you write a query once and supply values at run time, using an at-sign placeholder, which keeps queries reusable and safe in the interface or via the API.

SELECT *
FROM `myproject.sales.orders`
WHERE order_id = @order_id

And you can query metadata about your own datasets, for example to see table names and row counts, which is useful for auditing what you have.

SELECT table_name, row_count
FROM `myproject.sales.__TABLES__`

Writing queries that do not cost a fortune

Because BigQuery charges by the data scanned, a handful of habits pay off immediately. Select only the columns you actually need rather than reaching for a wildcard, since BigQuery is columnar and an unnecessary column is genuinely extra data read. Filter early and tightly with WHERE, especially on a partition column, so fewer rows and partitions are touched. Avoid SELECT * on wide tables, because it scans every column whether you use it or not. And before running anything expensive, glance at the cost estimate that the interface shows, which tells you how many bytes the query will scan before you commit to it.

These are not micro-optimisations. On a large table the difference between scanning two columns and scanning forty is the difference between pennies and real money, and it compounds every time the query runs.

Where to go next

Standard SQL in BigQuery rewards you twice over. The familiar clauses, select, where, group by, join, get you productive immediately, while the modern features, common table expressions, window functions, and native nested data, let you express analysis that would be painful elsewhere. Learn the building blocks here, build the cost-aware habits early, and you will be able to query datasets of almost any size with confidence.

See you soon.