Skip to content
Tutorials Center
My learning

Topics

Lesson 37 of 37 ยท Projects and Practice

Practice: BigQuery SQL Exercises

Twelve BigQuery-style SQL exercises with runnable solutions, a GoogleSQL translation table, and a closing review of partitioning, cost and security decisions.

You learn a warehouse by using it. This lesson closes the chapter with practice: SQL exercises over the bookstore sample database, a cheat sheet that translates between the portable SQL you run here and BigQuery's GoogleSQL, and a set of design questions to test your judgment about partitioning, cost and security. Try each exercise before opening the solution code in the editor.

The exercises run in SQLite, so they use portable constructs: joins, aggregates, CTEs and window functions, which work the same way in BigQuery. A table afterwards lists where BigQuery spells things differently.

Cheat sheet: portable SQL to GoogleSQL

Task Run here (SQLite) BigQuery GoogleSQL
Month of a date strftime('%Y-%m', d) DATE_TRUNC(d, MONTH)
Days between dates julianday(a) - julianday(b) DATE_DIFF(a, b, DAY)
Safe division a * 1.0 / NULLIF(b, 0) SAFE_DIVIDE(a, b)
Keep first row per group subquery with ROW_NUMBER() QUALIFY ROW_NUMBER() OVER (...) = 1
Concatenate a || b CONCAT(a, b) or a || b
Cast safely CAST(x AS INTEGER) SAFE_CAST(x AS INT64)
Top N per group window RANK() in a CTE same, or QUALIFY
Boolean count SUM(x = 1) COUNTIF(x = 1)
Approximate distinct COUNT(DISTINCT x) APPROX_COUNT_DISTINCT(x)
Arrays no arrays ARRAY_AGG, UNNEST

Quoting also differs: BigQuery uses backticks for names with dashes (`my-project.shop.orders`) and INT64, FLOAT64, STRING, NUMERIC as type names.

Exercise set

1. Titles by genre (warm-up)

List each genre with the number of books and the average price, most expensive average first.

SQL Exercise 1: genre summary ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

2. Stock risk

Find books with fewer than 5 copies in stock, with their author names.

SQL Exercise 2: low stock with authors ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

3. Customers who never ordered

Use an anti-join to find customers with no orders. In the sample data every customer has ordered, so an empty result is the correct answer; try it after inserting a new customer row.

SQL Exercise 3: customers without orders ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

4. Revenue per author

Revenue is quantity * price. Show authors ranked by revenue.

SQL Exercise 4: author revenue ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

5. Best seller per genre

Use a window function to pick the book with the most units sold in each genre. The BigQuery version would end with QUALIFY rn = 1.

SQL Exercise 5: top book per genre ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

6. Customer share of revenue

Show each customer's spend as a percentage of the total, using a window sum.

SQL Exercise 6: percent of total ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

7. Orders per month and growth

Compare each month to the previous one with LAG.

SQL Exercise 7: month over month ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

8. Authors with more than one genre

Use HAVING with COUNT(DISTINCT ...).

SQL Exercise 8: multi-genre authors ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

9. Price bands with CASE

Bucket books into budget, mid and premium and count them.

SQL Exercise 9: price bands ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

10. Estimate a scan (no database needed)

A table has 400 days of data at 25 GB per day. A query reads two columns (4 GB and 9 GB per day) for the last 7 days. How many GiB does it process, and what would it cost at the example rate of 6.25 USD per TiB?

Python Exercise 10: cost estimate

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

11. Which policy? (design)

Read the questions in the quiz below and decide the tool before you look at the answers: partition, cluster, row policy, policy tag or authorized view.

12. Translate to GoogleSQL

Rewrite Exercise 5 for BigQuery, using QUALIFY and `project.dataset.table` names. A sketch:

SQL
SELECT b.genre, b.title, SUM(o.quantity) AS units
FROM `my-project.bookstore.orders` o
JOIN `my-project.bookstore.books` b ON b.id = o.book_id
GROUP BY b.genre, b.title
QUALIFY ROW_NUMBER() OVER (PARTITION BY b.genre ORDER BY units DESC, b.title) = 1;

Note that BigQuery requires every non-aggregated selected column in GROUP BY (you may group by column names or positions), and QUALIFY runs after aggregation and window evaluation.

Quick review of the chapter decisions

Cost levers

Partition Filter by date to prune
Cluster Filter by key to skip blocks
Columns Read only what you need
Cap maximum_bytes_billed

Bytes read is the bill.

1 / 2

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. A 20 TB event table is queried almost always for one day and one country. What design fits best?

    Date is the coarse filter for partitions and country is a good cluster column inside them.
  2. 2. Which tool lets a regional manager see only their region's rows in a shared table?

    Row access policies filter rows per principal.
  3. 3. How do you hide an email column from most analysts but let a few read it?

    Column-level security uses policy tags; only approved principals get the reader role.
  4. 4. Which keeps a retried daily load from duplicating rows?

    Rewriting exactly one partition or merging by key makes the load idempotent.

Summary

  • Practice with joins, aggregates, CTEs and window functions; they carry over directly to BigQuery.
  • Know the GoogleSQL spellings: DATE_TRUNC, SAFE_DIVIDE, COUNTIF, QUALIFY, APPROX_COUNT_DISTINCT.
  • Design questions come down to: what do queries filter on (partition, cluster), who may see what (IAM, policies), and how many bytes are read (cost).
  • Keep loads idempotent and cap automated jobs.
  • You have finished the second half of the Google BigQuery tutorial.

Next: back to the Google BigQuery track page