CodeRang
Dialects

BigQuery SQL: Examples and Practice

BigQuery SQL includes several GoogleSQL features that are especially useful for analytical workloads. Learn them through examples with QUALIFY, window functions, ARRAY, UNNEST, STRUCT, and nested data.

Updated · SQL Arena team

BigQuery courseAll problems

What you will practice

  • Filter window-function results with QUALIFY
  • Drop selected columns with SELECT * EXCEPT
  • Expand arrays with UNNEST and build ARRAY values
  • Query STRUCT fields and nested data
  • Practice in a browser-based BigQuery sandbox

A practice problem in the trainer

This is how a problem looks in SQL Arena: the task, the editor, the result and the database schema.

PrevNextRandom
TaskSubmissionsAI Mentor
Return the latest event for each customer

The customer_events table contains customer activity. Return each customer's latest event with customer_id, event_type, and event_time. Keep only the most recent row per customer and sort by customer_id.

Query 1BigQueryGoogleSQL
SELECT
  customer_id,
  event_type,
  event_time
FROM customer_events
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY event_time DESC) = 1
ORDER BY customer_id;
Submit
Resultcustomerscustomer_events
Answer accepted

BigQuery SQL Example With QUALIFY Explained

ROW_NUMBER() ranks events inside each customer_id. PARTITION BY restarts the numbering for every customer, while ORDER BY event_time DESC puts the newest event first. The row we want therefore receives row number 1, with older events numbered after it.

QUALIFY is the BigQuery feature worth noticing. It filters rows after window functions have been evaluated, so there is no need to wrap the query in another SELECT just to filter on ROW_NUMBER(). In dialects without QUALIFY, the same pattern often needs a CTE or subquery. Here the ranking and the filter can stay together.

The final ORDER BY customer_id only controls presentation; it does not decide which event is latest. If production data can contain identical event_time values, add another expression such as id DESC inside the window ORDER BY so ties are resolved deterministically.

BigQuery examples often become more useful when they include GoogleSQL features for nested and repeated data. ARRAY stores multiple values in one field, STRUCT groups named fields into a single value, and UNNEST expands array elements into rows that can be filtered, joined, or aggregated. QUALIFY is another important feature: it filters rows after window functions have been evaluated. This makes patterns such as selecting the latest event per customer more concise because an extra subquery or CTE is often unnecessary.

SQLBigQuery GoogleSQL
SELECT
  customer_id,
  event_type,
  event_time
FROM customer_events
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY event_time DESC) = 1
ORDER BY customer_id;
Query result
customer_idevent_typeevent_time
1purchase2026-04-04 12:30:00 UTC
2purchase2026-04-06 16:10:00 UTC
3signup2026-04-03 11:20:00 UTC
4trial2026-04-07 09:45:00 UTC
5purchase2026-04-08 18:00:00 UTC

Common BigQuery SQL Mistakes

01

Filtering a window function in WHERE

WHERE is evaluated before window-function results are available. Use QUALIFY when the condition depends on ROW_NUMBER(), RANK(), or another window calculation.

02

Forgetting UNNEST for repeated values

An ARRAY is not automatically treated as a set of rows. Use UNNEST when you need to filter, join, or aggregate the elements stored inside a repeated field.

03

Selecting every column by default

Analytical tables often include internal fields that do not belong in the result. SELECT * EXCEPT is useful when you want most of a row while removing a small number of columns explicitly.

Practice problems on this topic

BigQuery course

After QUALIFY, the most useful BigQuery exercises are the ones that make you use features that are easy to miss in generic SQL practice. You can work on removing internal fields with SELECT * EXCEPT, deriving weekdays from event logs, narrowing analytical scans, and then move into ARRAY, UNNEST, and nested structures. Basket-style exercises are especially useful because repeated values force you to think differently from a flat relational table. SQL Arena runs BigQuery in its own browser sandbox, so you can practice BigQuery online without configuring a personal cloud project or billing account. Solutions are checked against hidden data, not just one visible sample. Across the platform there are 1,192 exercises, with separate tables for each task. The free tier requires no card and includes 292 exercises across all four supported SQL dialects. A dedicated BigQuery Course provides a more structured path, with theory and a task in each lesson; the first chapters are free. You can inspect ER diagrams and EXPLAIN output when needed. The AI mentor provides hints and error explanations, with 10 free uses in total. Interview Mode removes hints, uses a server-side timer, and returns a report after the session; one session per day is free.

Reference on this topic

In-depth articles with syntax and examples in the SQL Arena reference.

Frequently asked questions

What is BigQuery SQL?

BigQuery uses GoogleSQL, a SQL dialect designed for querying analytical data. It includes standard SQL concepts as well as features such as QUALIFY, ARRAY, STRUCT, and UNNEST.

Can you show BigQuery examples?

Good BigQuery examples include filtering, aggregation, window functions, nested data, and array processing. They should also demonstrate GoogleSQL-specific features such as QUALIFY and UNNEST.

What are common BigQuery SQL examples?

Common examples include finding the latest row per entity, calculating rankings with window functions, expanding repeated fields with UNNEST, and aggregating event data. These patterns appear frequently in analytical queries.

What does QUALIFY do in BigQuery SQL?

QUALIFY filters rows after window functions are calculated. It is useful when you want to keep rows based on ROW_NUMBER, RANK, or another window-function result without wrapping the query in another SELECT.

How does UNNEST work in BigQuery?

UNNEST converts the elements of an ARRAY into rows. After that, the values can participate in filtering, joins, grouping, and other SQL operations like ordinary rows.

See also

Practice BigQuery SQL With Exercises

Start with free exercises in the BigQuery sandbox, then continue with the BigQuery Course when you want a structured path into arrays, nested data, and analytical patterns.

Practice BigQuery SQL

More practice from CodeRang