Standard SQL in BigQuery: 10 Code-Along Examples

The Standard SQL article covers the language BigQuery actually speaks, from the shape of a basic query to the window functions and cost habits that separate a working query from a good one. This workbook runs that ground in order: selecting and filtering, sorting and limiting, aggregating, grouping with HAVING, working with dates, joining tables and handling the nulls joins produce, CASE for conditional logic, CTEs for readable multi-step queries, window functions, and finally views plus the practices that keep a query cheap. Every example is a query you paste into the BigQuery console, with its result shown underneath so you can check your own.

Setup: run this once

CREATE SCHEMA IF NOT EXISTS scratch;
CREATE OR REPLACE TABLE scratch.customers AS
SELECT * FROM UNNEST([
  STRUCT(101 AS customer_id, 'Ana'  AS name, 'UK' AS country, DATE '2025-11-02' AS signup_date),
  (102, 'Ben',  'UK', DATE '2026-01-05'),
  (103, 'Cara', 'DE', DATE '2025-08-14'),
  (104, 'Dan',  'US', DATE '2026-01-20'),
  (105, 'Eve',  'US', DATE '2026-02-11')
]);
CREATE OR REPLACE TABLE scratch.orders AS
SELECT * FROM UNNEST([
  STRUCT(1 AS order_id, 101 AS customer_id, 'Books' AS category, 40 AS amount,
         DATE '2026-01-03' AS order_date, 'complete' AS status),
  (2,  101, 'Books',  55, DATE '2026-01-10', 'complete'),
  (3,  101, 'Games',  90, DATE '2026-02-04', 'complete'),
  (4,  102, 'Books',  30, DATE '2026-01-06', 'complete'),
  (5,  102, 'Games', 120, DATE '2026-01-18', 'complete'),
  (6,  102, 'Games',  75, DATE '2026-02-09', 'refunded'),
  (7,  103, 'Music',  20, DATE '2026-01-08', 'complete'),
  (8,  103, 'Music',  60, DATE '2026-02-02', 'complete'),
  (9,  104, 'Games', 200, DATE '2026-02-15', 'complete'),
  (10, 104, 'Books',  80, DATE '2026-03-01', 'complete')
]);

Five customers and ten orders across three months. Eve has never ordered and order 6 was refunded, and both of those facts do work later.

1. SELECT, FROM, WHERE

Every query names the columns you want, the table they live in, and the rows that qualify. In BigQuery a table reference is project.dataset.table, wrapped in backticks whenever it needs quoting.

SELECT
  customer_id,
  name,
  country
FROM `scratch.customers`
WHERE country = 'UK';

customer_id | name | country
------------+------+--------
101         | Ana  | UK
102         | Ben  | UK

Three clauses, three jobs: SELECT picks columns, FROM names the source, and WHERE keeps only the rows whose condition is true. The backticks matter in BigQuery because a fully qualified name contains dots that would otherwise be ambiguous, and they become mandatory when a project or dataset name contains a hyphen. Naming your columns rather than writing SELECT * is the first cost habit worth forming, since BigQuery charges by bytes scanned and every column you skip is data you do not pay for, a point Example 10 returns to.

2. ORDER BY and LIMIT

ORDER BY sorts the result and LIMIT caps how many rows come back. Together they answer “the top few by some measure”.

SELECT
  order_id,
  category,
  amount
FROM `scratch.orders`
WHERE status = 'complete'
ORDER BY amount DESC
LIMIT 3;

order_id | category | amount
---------+----------+-------
9        | Games    | 200
5        | Games    | 120
3        | Games    | 90

The three largest completed orders, all of them games. DESC reverses the default ascending sort, and LIMIT 3 stops after three rows. Two things are worth noticing. Without ORDER BY, LIMIT returns an arbitrary three rows rather than the top three, because SQL result order is not guaranteed unless you ask for it. And LIMIT does not reduce what BigQuery scans or bills, since the filtering and sorting happen across the whole table first, which is why WHERE is your cost lever and LIMIT is only a display convenience.

3. Aggregate functions and aliases

Aggregates collapse many rows into one number. AS renames the result so the output column has a readable name rather than a generated one.

SELECT
  COUNT(*)             AS order_count,
  SUM(amount)          AS total_revenue,
  ROUND(AVG(amount), 2) AS average_order,
  MIN(amount)          AS smallest,
  MAX(amount)          AS largest
FROM `scratch.orders`
WHERE status = 'complete';

order_count | total_revenue | average_order | smallest | largest
------------+---------------+---------------+----------+--------
9           | 695           | 77.22         | 20       | 200

Nine completed orders worth £695, averaging £77.22. One row out, because with no GROUP BY the aggregates run over the entire filtered set at once. ROUND(AVG(amount), 2) shows two functions nesting, the inner one computing and the outer one tidying, which is how most readable numeric output gets built. The AS aliases are not cosmetic either: without them BigQuery invents names like f0_, and any downstream query or dashboard referring to those is fragile.

4. GROUP BY and HAVING

GROUP BY splits the rows into groups and runs the aggregates within each. HAVING then filters those groups, which is a different job from WHERE.

SELECT
  category,
  COUNT(*)    AS order_count,
  SUM(amount) AS revenue
FROM `scratch.orders`
WHERE status = 'complete'      -- filters ROWS, before grouping
GROUP BY category
HAVING SUM(amount) > 100       -- filters GROUPS, after aggregating
ORDER BY revenue DESC;

category | order_count | revenue
---------+-------------+--------
Games    | 3           | 410
Books    | 4           | 205

Music is missing, and that absence is the lesson. Its two orders total £80, so the group exists but fails the HAVING test, whereas the refunded order was removed earlier by WHERE before any grouping happened. The distinction is about timing: WHERE runs on individual rows before aggregation and can therefore only reference columns, while HAVING runs on the grouped results and can reference aggregates. Trying to write WHERE SUM(amount) > 100 fails for exactly that reason, because at WHERE time no sum has been computed yet.

5. Working with dates

Dates support comparison, BETWEEN for ranges, and EXTRACT for pulling out parts. Grouping by an extracted part is how you build a monthly trend.

SELECT
  EXTRACT(MONTH FROM order_date) AS month,
  COUNT(*)    AS order_count,
  SUM(amount) AS revenue
FROM `scratch.orders`
WHERE status = 'complete'
  AND order_date BETWEEN DATE '2026-01-01' AND DATE '2026-03-31'
GROUP BY month
ORDER BY month;

month | order_count | revenue
------+-------------+--------
1     | 5           | 265
2     | 3           | 350
3     | 1           | 80

Three months of trend from one query. BETWEEN is inclusive at both ends, so an order dated exactly 1 January is kept, and DATE '2026-01-01' is the explicit literal form that avoids any ambiguity about how a string would be parsed. EXTRACT(MONTH FROM ...) returns just the month number, which is what makes it groupable, and the same function takes YEAR, DAYOFWEEK, QUARTER and more. Note also that GROUP BY month refers to the alias defined in the SELECT, which BigQuery permits and which keeps the query readable.

6. Joins, and the nulls they produce

An INNER JOIN keeps only matching rows; a LEFT JOIN keeps every row from the left table. IFNULL then substitutes a value where the join found nothing.

SELECT
  c.name,
  c.country,
  COUNT(o.order_id)        AS order_count,
  IFNULL(SUM(o.amount), 0) AS revenue
FROM `scratch.customers` AS c
LEFT JOIN `scratch.orders` AS o
  ON o.customer_id = c.customer_id
  AND o.status = 'complete'      -- the filter belongs HERE, not in WHERE
GROUP BY c.name, c.country
ORDER BY revenue DESC;

name | country | order_count | revenue
-----+---------+-------------+--------
Dan  | US      | 2           | 280
Ana  | UK      | 3           | 185
Ben  | UK      | 2           | 150
Cara | DE      | 2           | 80
Eve  | US      | 0           | 0

Eve appears with zeroes, which is the point of the left join, since an inner join would have dropped her and made the customer list silently incomplete. The detail worth memorising is where the status filter sits. Putting o.status = 'complete' in the ON clause means unmatched customers still survive with nulls, but moving it to a WHERE clause would test Eve’s null status, fail, and delete her row, quietly converting your left join back into an inner one. IFNULL(SUM(o.amount), 0) then turns her null revenue into a zero, and COUNT(o.order_id) is already 0 because COUNT of a column ignores nulls.

7. CASE for conditional logic

CASE returns different values depending on conditions, which turns a continuous column into labelled bands you can group by.

SELECT
  CASE
    WHEN amount >= 100 THEN 'large'
    WHEN amount >= 50  THEN 'medium'
    ELSE 'small'
  END          AS size_band,
  COUNT(*)     AS order_count,
  SUM(amount)  AS revenue
FROM `scratch.orders`
WHERE status = 'complete'
GROUP BY size_band
ORDER BY revenue DESC;

size_band | order_count | revenue
----------+-------------+--------
large     | 2           | 320
medium    | 4           | 285
small     | 3           | 90

Nine orders sorted into three bands, and the revenue splits almost evenly between the two large orders and the four medium ones. The WHEN conditions are tested top to bottom and the first true one wins, so ordering them from the highest threshold down is deliberate: written the other way round, every order over £100 would match the £50 test first and the large band would never fill. ELSE catches whatever falls through, and omitting it would produce nulls instead, which is rarely what you want in a grouping column.

8. CTEs: naming the steps

A WITH clause defines a named intermediate result, and several can be chained so each builds on the last. This turns a nested, unreadable query into a sequence you can follow.

WITH complete_orders AS (
  SELECT customer_id, amount
  FROM `scratch.orders`
  WHERE status = 'complete'
),
customer_totals AS (
  SELECT customer_id, SUM(amount) AS revenue
  FROM complete_orders           -- the previous CTE, used by name
  GROUP BY customer_id
)
SELECT
  c.name,
  t.revenue,
  ROUND(AVG(t.revenue) OVER (), 2) AS avg_customer_revenue,
  t.revenue > AVG(t.revenue) OVER () AS above_average
FROM customer_totals AS t
JOIN `scratch.customers` AS c USING (customer_id)
ORDER BY t.revenue DESC;

name | revenue | avg_customer_revenue | above_average
-----+---------+----------------------+--------------
Dan  | 280     | 173.75               | true
Ana  | 185     | 173.75               | true
Ben  | 150     | 173.75               | false
Cara | 80      | 173.75               | false

Two customers beat the £173.75 average. The query reads as three steps: filter to completed orders, total them per customer, then compare each customer against the average, and each step has a name that says what it does. CTEs cost nothing in performance terms compared with the equivalent nested subqueries, so the gain is entirely in readability and debuggability, since you can select from any CTE on its own to check it. Note USING (customer_id) as the shorthand for joining on an identically named column, and the OVER () window that computes the average across all rows without collapsing them, which Example 9 develops.

9. Window functions

A window function computes across a set of rows and returns a value for each row, without collapsing anything. PARTITION BY restarts the calculation per group.

SELECT
  category,
  order_id,
  amount,
  RANK() OVER (PARTITION BY category ORDER BY amount DESC) AS rank_in_category,
  SUM(amount) OVER (PARTITION BY category)                 AS category_total
FROM `scratch.orders`
WHERE status = 'complete'
ORDER BY category, rank_in_category;

category | order_id | amount | rank_in_category | category_total
---------+----------+--------+------------------+---------------
Books    | 10       | 80     | 1                | 205
Books    | 2        | 55     | 2                | 205
Books    | 1        | 40     | 3                | 205
Books    | 4        | 30     | 4                | 205
Games    | 9        | 200    | 1                | 410
Games    | 5        | 120    | 2                | 410
Games    | 3        | 90     | 3                | 410
Music    | 8        | 60     | 1                | 80
Music    | 7        | 20     | 2                | 80

Nine rows in, nine rows out, each carrying both its rank within its category and that category’s total. This is what separates window functions from GROUP BY: the grouping in Example 4 gave one row per category and discarded the orders, while PARTITION BY computes per category and keeps every order visible alongside the summary. RANK() needs an ORDER BYinside the window to know what it is ranking, whereas SUM() does not, since a total has no sequence. Filtering on a window result, such as keeping only rank 1, requires wrapping this query in an outer SELECT, because windows are computed after WHERE has already run.

10. Views, and keeping queries cheap

A view saves a query under a name so others can select from it without re-deriving the logic. Around it sit the habits that keep BigQuery bills low.

CREATE OR REPLACE VIEW `scratch.v_customer_revenue` AS
SELECT
  c.customer_id,
  c.name,
  c.country,
  COUNT(o.order_id)        AS order_count,
  IFNULL(SUM(o.amount), 0) AS revenue
FROM `scratch.customers` AS c
LEFT JOIN `scratch.orders` AS o
  ON o.customer_id = c.customer_id
  AND o.status = 'complete'
GROUP BY c.customer_id, c.name, c.country;
-- now anyone can query the logic without rewriting it
SELECT name, revenue
FROM `scratch.v_customer_revenue`
WHERE revenue > 100
ORDER BY revenue DESC;

name | revenue
-----+--------
Dan  | 280
Ana  | 185
Ben  | 150

A view stores the query text rather than the results, so it always reflects current data and costs nothing to keep, with the scan billed when someone selects from it. Around that, four habits control what you pay. Name your columns instead of SELECT *, because BigQuery scans only the columns referenced and a wide table punished by * is the single most common source of a surprise bill. Filter as early as possible, ideally on a partitioning column such as _PARTITIONDATE or a partitioned date field, since partition pruning skips whole blocks of storage rather than reading and discarding them. Use query parameters, written @start_date, rather than pasting values into the SQL, which keeps queries reusable and safe. And check the estimate BigQuery shows before running anything against a large table, because the validator tells you the bytes before you commit to them.

Work through these and you have the article in practice: the SELECT, FROM, WHERE skeleton with BigQuery’s backtick naming, sorting and limiting, aggregates with aliases, GROUP BY against HAVING, date filtering and extraction, inner and left joins with IFNULL and the ON versus WHERE trap, CASE banding, CTEs for readable multi-step logic, window functions that summarise without collapsing, and views plus the cost habits that make all of it affordable. The thread running through it is the one the article keeps returning to: BigQuery charges for what it reads, so the clearest query and the cheapest query are usually the same query, one that names the columns it needs and filters as early as it can.

See you soon.