SQL for Data Analytics with BigQuery is a hands-on course in writing SQL to analyse business data on Google BigQuery, from SELECT, GROUP BY and joins to CTEs, window functions, and cohort, retention and funnel analysis, with an eye on query cost. It suits data and business analysts, marketing and sales teams, and Excel users whose data has outgrown spreadsheets.
Much of an organisation's sales, customer and usage data now lives in cloud data warehouses such as Google BigQuery, yet the people who need those numbers to make decisions still wait for the data team to pull them, or download them into Excel until the laptop grinds to a halt. SQL has become a core skill for today's analysts because it lets them answer business questions themselves, from the full data set, quickly and in a way others can check.
This course starts with the free BigQuery sandbox and builds SQL from SELECT, WHERE and ORDER BY through summarising with GROUP BY and HAVING, joining tables, working with dates and text, grouping with CASE and breaking long queries into readable CTEs. It then moves into analysis with window functions for ranking, running totals and period-over-period comparison, cohort, retention and funnel analysis, cost-aware querying, saving views for Data Studio (formerly Looker Studio), and using Gemini in BigQuery to draft SQL safely, before closing with a business analysis project. (2 days, 6 hours per day, 12 hours in total, Beginner to Intermediate level.)
Course Details
This course runs for 2 days, 6 hours per day (12 hours in total, 09:00-16:00), as lectures with labs on a sample online store data set and BigQuery public datasets. Beginner to Intermediate level, with no prior SQL required. All labs run in the BigQuery sandbox, which is free within Google's limits and needs no credit card; Gemini in BigQuery depends on the organisation's project settings. The course focuses on writing SQL for analysis and does not cover data pipeline design or data warehouse administration. Learners take home a lab guide, solution SQL for every lab and template queries for cohort, retention and funnel analysis.
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
No public rounds are open right now. Join the waiting list and we will contact you first when the next round opens, or ask us on LINE. Or call 02-570-8449 or 088-807-9770
A window function calculates across a set of rows without collapsing them as GROUP BY does. It uses OVER, with PARTITION BY to split rows into groups and ORDER BY to set their order. Typical uses are ranking with ROW_NUMBER or RANK, running totals and comparing with the previous month using LAG. In BigQuery you can filter on window function results with QUALIFY.
First find each customer's first purchase month as their cohort. Join that back to all purchases and use DATE_DIFF to work out how many months after the first purchase each order falls. Then group by cohort month and months elapsed, count distinct customers, and divide by the cohort's starting size to get monthly retention.
With on-demand billing BigQuery charges by the amount of data a query scans, so select only the columns you need instead of SELECT *, filter on the partition column of partitioned tables, and check the estimated bytes before running or use a dry run. Setting maximum bytes billed stops queries that would scan too much. Note that LIMIT does not reduce the data scanned.
It suits data and business analysts who want to pull data themselves, marketing, sales and product teams tracking figures from large datasets, and advanced Excel users hitting limits as data grows. No prior SQL is needed, but you should be comfortable with filtering, formulas and pivot tables in Excel or Google Sheets, and have a Google account to open a BigQuery sandbox.
You will be able to write SQL to retrieve, filter and summarise data, join tables without inflating totals, structure long queries with CTEs, rank and compare month on month and year on year with window functions, analyse cohorts, retention and funnels, and write cost-aware queries whose results feed Data Studio. Participants who complete the training receive a Certificate of Completion with its own number, which can be verified online.
Yes. It can be run in-house for your team, with examples adapted to your own work, such as practising queries on the analysis questions your team answers regularly. You can request a quotation from this page, or contact us on LINE @itgenius or by phone on 02-570-8449.
Articles are published in Thai.
Many organisations still build reports by copying data out of several systems into Excel every week and passing the files around by email. The numbers never quite match, managers wait for their reports, and the people who make them spend their time on…
PostgreSQL has become the first-choice database for modern applications, from backend systems and SaaS products to workloads that keep JSON or vector data alongside relational tables. Yet many developers still treat PostgreSQL as a generic database behind an…
Many organizations already own BI tools such as Power BI or Tableau, yet still struggle with data preparation: pulling data from several systems by hand, cleaning it in Excel, and constantly finding that numbers do not match between reports. This course fills…
