SQL for Data Analytics with BigQuery

Section 1: Lab: Getting Started with BigQuery

• What BigQuery is and how it differs from analysing data in Excel

• Set up the BigQuery sandbox and get to know projects, datasets and tables

• Explore schemas, table previews and public datasets in the console

• Lab: run a first query and check how much data it will scan before running it

Section 2: Lab: Retrieving, Filtering and Sorting Data

• SELECT, aliases, DISTINCT and LIMIT

• Filtering with WHERE: comparisons, AND, OR, IN, BETWEEN and LIKE

• Sorting results with ORDER BY and handling NULL values

• BigQuery data types and converting them safely with SAFE_CAST

Section 3: Summarising Data with Aggregates and GROUP BY

• COUNT, SUM, AVG, MIN, MAX and COUNT DISTINCT

• GROUP BY on several columns, and filtering summaries with HAVING

• WHERE versus HAVING, and the logical order in which a query runs

• Lab: summarise sales, order counts and average order value by month and category

Section 4: Lab: Joining Tables

• When to use INNER, LEFT and FULL OUTER JOIN

• Duplicate rows from joins that inflate totals, and how to check for them

• Nested and repeated data in BigQuery, and using UNNEST

• Lab: combine orders, customers and products to find sales by customer segment

Section 5: CASE, Dates and Text

• Group data with CASE WHEN and count conditionally

• DATE_TRUNC, EXTRACT, DATE_DIFF and the Thailand time zone

• Text functions and REGEXP for cleaning data

• Handle missing values with IFNULL and COALESCE, and divide safely with SAFE_DIVIDE

• Lab: segment customers by spend and summarise weekly sales

Section 6: Lab: CTEs and Subqueries

• Subqueries in WHERE and FROM

• Break long queries into steps with WITH (CTEs) so they are easy to read and check

• Check the result of each step before building the next one

• Lab: find the top 10 products in each category with above-average sales

Section 7: Window Functions: Ranking Data

• The idea behind OVER, PARTITION BY and ORDER BY

• How ROW_NUMBER, RANK and DENSE_RANK differ

• Filter window function results with QUALIFY

• Lab: find each customer's first order and the top sellers in each branch

Section 8: Lab: Running Totals and Period Comparisons

• Running totals and moving averages with window frames

• LAG and LEAD to compare with the previous month and the previous year

• Share of total and growth rates

• Lab: a monthly sales report with MoM and YoY figures

Section 9: Lab: Cohort, Retention and Funnel Analysis

• Group customers by the month of their first purchase to build cohorts

• Calculate monthly retention and lay it out as a cohort table

• Funnels from event data: page view, add to cart and checkout

• Read the results to find where customers drop off, and the pitfalls of interpretation

Section 10: Writing Cost-Efficient Queries

• How BigQuery charges for queries and why bytes scanned matter

• Avoid SELECT * and choose only the columns you need

• Partitioned and clustered tables, and filtering so partitions are pruned

• The query cache, dry runs and setting maximum bytes billed

• Lab: compare bytes scanned before and after tuning a query

Section 11: Saving and Sharing Results, and Drafting SQL with Gemini

• Save queries, create views and set up scheduled queries

• Send results to Google Sheets and connect them to Data Studio

• Use Gemini in BigQuery to draft and explain SQL from plain language

• Review AI-written SQL for logic, figures and cost before using it

Section 12: Workshop: Capstone Business Analysis

• Take a business brief from a fictional executive and turn it into questions SQL can answer

• Write queries that analyse sales, customers and retention with the techniques covered

• Save the results as views and connect Data Studio to present them visually

• Present the findings, review the SQL together and wrap up with a query-writing checklist