The Work Gym

Courses / Data and analytics tools

BigQuery SQL

BigQuery SQL in a BigQuery Studio-style editor: nested data, GA4 exports, partitions and what queries cost.

Start lesson 1 Practice interview Take the assessmentPractice the first unit of every course. No account needed.

57lessons

405checked exercises

13projects, cases and exams

Prepares you for Analytics roles, GA4 work

Projects, case studies and mock exams

Case study · BeginnerA replayed order report

Reconcile duplicate feed rows and identify the channel with the highest observed refund share.

6 steps · 40 min Project · IntermediateA GA4 ecommerce funnel

From raw GA4 events to a session funnel with conversion rates by device, across two weeks of daily tables.

6 steps · 35 min Project · IntermediateA fulfillment basket report

Build a customer report that reconciles basket contents, shipping and empty-item records.

7 steps · 40 min Project · AdvancedA daily order mart

Build a partitioned day-and-channel mart with explicit status rules and reusable reporting.

7 steps · 40 min Case study · ExpertDispatch capacity

Compare daily order demand with an explicit fictional capacity rule and return a checked priority.

6 steps · 35 min Project · ExpertCapacity model validation

Build a prediction register and a checked holdout validation report for a fictional planning model.

6 steps · 45 min Case study · ExpertSubscription churn

Measure active subscriptions, cancellations and churn by plan, and say what the numbers can and can't tell you.

6 steps · 30 min Mock exam · ExpertBigQuery

Forty original questions on BigQuery architecture, storage, security, pipelines and SQL, with a two-hour clock.

40 questions · 120 min Mock exam · ExpertData engineering with BigQuery

Answer 40 original BigQuery scenarios in a two-hour mock with single-select and multiple-select questions.

40 questions · 120 min

Practice GoogleSQL in a BigQuery Studio-style console with table paths, nested records and an estimate of the bytes each query reads. Start with filters, joins and reliable report totals. Then work with arrays, GA4-style exports, windows, partitioned tables and incremental merges. Later units cover metadata, scripts, procedures, materialized results, approximate aggregates, geographic distances and small regression models. Build reports through continuing projects, investigate business cases and prepare for interviews at three difficulty levels. Two timed scenario sets review BigQuery decisions in a shorter certification-style format. Runnable answers are checked on changed data, so a query must express the rule rather than hard-code the visible result.

Beginner

Query foundations

Read tables, filter rows and establish the report grain.

4 lessons

1The query editor and table pathsRun queries in BigQuery, name tables by project, dataset and table, and read what a query will process before you run it.5 exercises · 10 minFree

2Filter and sortFilter rows with WHERE, LIKE, IN and BETWEEN, sort them, and put NULLs where you want them.6 exercises · 12 minFree

3Counts, totals and COUNTIFCount and total by group with GROUP BY, COUNTIF and HAVING, and count distinct values.5 exercises · 12 minFree

Practice setBigQuery basicsTen mixed tasks on table paths, filters, aggregates, dates, joins and safe functions.Free

Clean reporting

Handle missing values, text and date boundaries.

5 lessons

5Dates: DATE_TRUNC, DATE_DIFF and FORMAT_DATERound dates to months, measure gaps, add intervals and format dates, with BigQuery's argument order.6 exercises · 14 minWith a plan

6IF, IFNULL, SAFE_DIVIDE and SAFE_CASTHandle missing values and errors the BigQuery way: IF, IFNULL, SAFE_DIVIDE, SAFE_CAST and NULLIF.5 exercises · 12 minWith a plan

7NULLs and report grainKeep missing values visible and count at the grain a report promises.5 exercises · 18 minWith a plan

8Text cleanup and usable keysNormalize imported text while keeping missing contact details distinct from valid values.5 exercises · 18 minWith a plan

Practice setClean reportingCombine cleaned contact keys, missing-value counts and customer-grain joins.With a plan

Relational analysis

Combine tables and audit the resulting totals.

4 lessons

10JoinsJoin orders to customers, find what has no match, and join a table to itself.5 exercises · 12 minWith a plan

11CTEs and subqueriesSeparate calculations into named stages and compare rows with aggregate benchmarks.5 exercises · 18 minWith a plan

Case studyA replayed order reportReconcile duplicate feed rows and identify the channel with the highest observed refund share.With a plan

Practice setQuery foundationsApply filters, dates, grouping and report-grain checks to a replayed order feed.With a plan

Intermediate

Nested order data

Read repeated records without losing or multiplying orders.

5 lessons

14Records: STRUCT columnsRead fields inside RECORD columns with a dot, expand them with .*, and group and filter on them.5 exercises · 10 minWith a plan

15Arrays and UNNESTTurn repeated fields into rows with UNNEST, keep rows whose array is empty, index into arrays, and test membership.6 exercises · 16 minWith a plan

16Array positions and safe accessRead array positions safely and keep their order when expanding repeated records.5 exercises · 18 minWith a plan

17Correlated array queriesSummarize and filter repeated records without changing the parent row count.5 exercises · 18 minWith a plan

Practice setNested order analysisCombine parent-grain summaries, safe access and product-level analysis on fresh baskets.With a plan

Event pipelines

Read sharded exports and build a checked funnel.

4 lessons

19GA4 exports: event_paramsRead GA4 event parameters with the UNNEST subquery pattern, count sessions and users, and turn microsecond timestamps into times.5 exercises · 16 minWith a plan

20Wildcard tables and _TABLE_SUFFIXQuery many daily tables at once with events_*, choose the days with _TABLE_SUFFIX, and keep the bytes down.5 exercises · 12 minWith a plan

ProjectA GA4 ecommerce funnelFrom raw GA4 events to a session funnel with conversion rates by device, across two weeks of daily tables.With a plan

Practice setNested data and GA4Eight mixed tasks on records, UNNEST, event_params, wildcard tables and window functions.With a plan

Analytical windows

Define frames and publish a basket report at the right grain.

5 lessons

23Window functions and QUALIFYRank, number and compare rows with window functions, and filter on them directly with QUALIFY.5 exercises · 14 minWith a plan

24Build arrays and recordsCollect rows into arrays with ARRAY_AGG, build records with STRUCT, join text with STRING_AGG, and generate arrays of dates.5 exercises · 12 minWith a plan

25Window frames and running measuresChoose explicit row frames and deterministic ordering for cumulative and moving reports.5 exercises · 18 minWith a plan

ProjectA fulfillment basket reportBuild a customer report that reconciles basket contents, shipping and empty-item records.With a plan

Practice setDispatch windowsReview ordered windows on a dispatch extract without changing its row grain.With a plan

Advanced

Storage and metadata

Design scan-aware tables and inspect the warehouse catalog.

6 lessons

28Cost: bytes, partitions and clusteringRead the dry-run estimate, cut bytes with column choice and partition filters, and know what LIMIT, clustering and the cache do.5 exercises · 14 minWith a plan

29Create tables and viewsCreate tables from queries and column lists, partition and cluster them, and save queries as views, in your own dataset.5 exercises · 14 minWith a plan

30Partitioned and clustered tablesCreate partitioned tables and use selective filters without treating estimates as guarantees.5 exercises · 18 minWith a plan

31INFORMATION_SCHEMA auditsInspect tables, column types and physical design through metadata views.5 exercises · 18 minWith a plan

ProjectA daily order martBuild a partitioned day-and-channel mart with explicit status rules and reusable reporting.With a plan

Practice setWarehouse pipelinesCombine metadata checks, deduplicated inputs, selective scans and reusable tables.With a plan

Reliable changes

Deduplicate source changes and validate JSON and merges.

4 lessons

34INSERT, UPDATE, DELETE and MERGEChange rows in your own tables, with BigQuery's rule that UPDATE and DELETE need a WHERE, and upsert with MERGE.5 exercises · 14 minWith a plan

35JSON in BigQueryPull values out of JSON text with JSON_VALUE and JSON_QUERY, cast them, and unnest JSON arrays.5 exercises · 12 minWith a plan

36Reliable MERGE sourcesReduce an update feed to one row per key before applying a repeatable upsert.5 exercises · 18 minWith a plan

Practice setControlled warehouse changesApply bounded account changes and inspect incoming payment events.With a plan

Repeatable operations

Use scripts, procedures and materialized results for recurring work.

5 lessons

38Scripts and variablesReuse typed variables across a script and keep the final result separate from intermediate statements.4 exercises · 18 minWith a plan

39Stored proceduresSave a repeatable table build in a procedure and call it explicitly.4 exercises · 20 minWith a plan

40Materialized viewsBuild reusable aggregate results and distinguish a materialized view from a logical view or report table.4 exercises · 18 minWith a plan

41Scheduled query designChoose run parameters, rerun behavior, ownership and monitoring for a scheduled report.4 exercises · 16 minWith a plan

Practice setWarehouse operationsCombine typed reporting windows, repeatable builds and stored aggregates on fresh operational cuts.With a plan

Expert

Scale and location

Choose approximations, geographic filters and resource controls.

4 lessons

43Approximate aggregatesUse approximate distinct counts and quantiles with explicit accuracy limits.4 exercises · 18 minWith a plan

44Geography and delivery distanceCreate geographic points, calculate distances in meters and filter a delivery radius.4 exercises · 20 minWith a plan

Case studyDispatch capacityCompare daily order demand with an explicit fictional capacity rule and return a checked priority.With a plan

Practice setExpert warehouse reviewReview report grain, explicit time boundaries, nested revenue and operational evidence.With a plan

Small predictive models

Train a regression and evaluate it on held-out observations.

3 lessons

47BigQuery ML basicsFit a small linear model, evaluate unseen observations and return predictions with their identifiers.5 exercises · 25 minWith a plan

ProjectCapacity model validationBuild a prediction register and a checked holdout validation report for a fictional planning model.With a plan

Practice setEstimates and modelsChoose exact or approximate measures, check spatial units and evaluate predictions on held-out rows.With a plan

Cases and assessments

Defend SQL decisions and complete timed scenario practice.

8 lessons

Case studySubscription churnMeasure active subscriptions, cancellations and churn by plan, and say what the numbers can and can't tell you.With a plan

Interview setBigQueryThe BigQuery questions analytics interviews ask: deduplication, top N per group, UNNEST, GA4 sessions, date spines, partitions and cost.With a plan

Interview setBigQuery fundamentalsExplain grain, NULLs and scan choices, then demonstrate the corresponding SQL.With a plan

Interview setAnalytical SQLHandle window ties, nested totals, repeatable ingestion and approximate counts.With a plan

Interview setWarehouse decisionsDefend temporal windows, report grain, leakage controls and production operating choices.With a plan

Mock examBigQueryForty original questions on BigQuery architecture, storage, security, pipelines and SQL, with a two-hour clock.With a plan

Mock examData engineering with BigQueryAnswer 40 original BigQuery scenarios in a two-hour mock with single-select and multiple-select questions.With a plan

Practice setWarehouse decisionsReview the evidence behind warehouse design and operations decisions.With a plan