The Complete Google BigQuery Masterclass: Beginner to Expert

Master selecting all or specific columns with select star and select distinct in BigQuery, using project.dataset.table syntax to query data and retrieve unique records.

Apply case with select statements in Google BigQuery to categorize total due as less, medium, or too much, with an alias total_due_amount.

Learn to filter records with the where clause using comparison operators—equal to, not equal to, greater than, less than, and their inclusive forms—through practical examples.

Explore how to use and, or, and not in the where clause to filter SQL results, including syntax for strings and dates, combining conditions, and reading query outputs.

Master copying a table in Google BigQuery, including using copy table, saving queries, and renaming with the alter command. Track changes with job history and delete tables from the dashboard.

Learn how arithmetic operators plus, minus, multiply, and division are used in select and where clauses in BigQuery, with examples of tax, freight, totals, aliases, and null value considerations.

Learn how the order of execution in Google BigQuery SQL affects using aliases in the where clause, with from, where, then select determining when expressions are evaluated.

Learn to count rows in BigQuery tables, using count(*) vs count(column), interpret headers and null values, and understand memory implications when overwriting a table.

Explore how null values represent unknown or missing data and use the is null and is not null operators to filter records by product ID and on order quantity.

Master the between and not between operators in Google BigQuery, filtering data by ranges with inclusive endpoints, using examples like purchase order ID and due dates.

Learn how the in and not in operators test values against a fixed set and simplify sql queries with product ids in a purchase order detail table.

Explore how the like operator in a where clause enables pattern matching with wildcard characters such as percent and underscore, examples like green names and account numbers containing 001.

Learn to use the order by clause with column positions in sql, sorting by the first or second selected column using ascending or descending orders.

The group by clause groups records by columns and often uses aggregate functions—sum, avg, max, min, and count—illustrated with purchase order and vendor tax examples.

Learn to write a sql query with where, group by, and order by using vendor ID, and follow the recommended order for big query execution.

Explore other numerical functions in Google BigQuery, including abs for absolute values, ceiling and floor for integer bounds, rand for random numbers, and round for decimal precision, with nesting examples.

Explore string functions in Google BigQuery, including split, length, and concat. Apply lower and upper, see practical examples with name and account number, and learn nesting techniques.

Explains l trim to remove leading spaces, trim for trailing and both sides, and str_pos to locate the first occurrence of a pattern in a string with examples.

Explore string functions in Google BigQuery, including replace, repeat, reverse, and substr, applying them to account number fields with field, source, destination arguments and optional aliases.

Learn how to use date_add, date_diff, and current_date in Google BigQuery to add intervals, compute date differences, and view the current date.

Master extracting date parts with the extract function and constructing dates with the date function, using parts like day, week, month, quarter, and year, including date time.

Explore the strange behavior of the Google BigQuery parser with group by and column aliases, showing that select aliases can be used in group by despite apparent execution order.

Learn how the having clause filters results after applying aggregate functions like max, typically with group by, and why where cannot use aggregates, guiding query order and execution.

Explore subqueries by examining how an inner query sits inside a main query. Use equals and in operators with purchase order header and detail tables, highlighting scalar versus multiple results.

Learn subqueries as column expressions in BigQuery to obtain purchase order id and tax amount from the header, plus total order quantity from the detail table with grouping and aliases.

Master the union all operator, which joins rows from multiple select statements without removing duplicates. Understand the column and data type requirements and how to combine results across tables.

Apply the intersect distinct operator to return only the distinct records common to both tables, such as 20 and 25, with duplicates removed.

Learn how the except distinct operator compares records across tables and returns the distinct rows from the first query that do not appear in the others, with practical examples.

Explore inner joins and implicit joins to combine columns from multiple tables, using select from table A and table B on matching columns to return only common records.

Use inner join to combine columns from purchase_vendor and vendor, replacing implicit joins. Apply PV and V aliases on business_entity_id, product_id, and account_number to enhance clarity.

Learn left join basics: return all records from the left table with only matching right table records, using left join syntax and nulls for nonmatches.

Demonstrates a left join between the customer and person_credit_card tables in BigQuery, returning all customers with matching credit card data while highlighting left outer join equivalence and null handling.

Learn how right join, the opposite of left join, returns all records from the right table and matching records from the left, with nulls when no match.

Learn how a full outer join combines left and right joins, using syntax left table full outer join right table on columns, and output remains the same when swapping tables.

Learn how cross join creates a cartesian result by pairing every row from two tables, using comma or cross join syntax, and using aliases to disambiguate columns.

Explore derived tables in the from clause, a temporary alias-required table formed from a subquery to simplify complex queries.

Learn how to use derived tables as a table expression to simplify queries, avoid group by alias confusion, and apply a meaningful alias to from expressions for clearer results.

Master BigQuery SQL to calculate total quantity by product and profit using inner join and group by, returning product id, total quantity, and profit (list price minus standard cost).

Learn to reproduce the same results with a derived table in BigQuery, using an inner join, group by product id, and alias access like prods dot product id.

Write SQL code to categorize products by list price using a case statement, first without and then with a derived table, and group results by product type to show counts.

Load three CSV files—address, business entity address, and person—to create a dataset named person detail and tables via autodetect and create table, and learn about common table expressions (CTE).

Learn common table expressions (CTE) and their use with select statements, how CTEs store results as a temporary table, and how they differ from derived tables for complex queries.

Write a sql query on the person table to return business entity id and a full name from first, middle, and last names using concat, sort by business entity id.

Use concat to form a name from first, middle, and last names, and compare a query with a with clause CTE on the person table, ordered by business entity ID.

Learn to write a sql query joining the person and business entity address tables on business entity id, building a full name from first, middle, and last names, excluding nulls.

Learn to build a three-table cte in sql by joining the person, business entity address, and address tables to construct full name and complete address, filtering out nulls.

Explore arrays in BigQuery, mastering array creation and manipulation with unnest, array length, generate array, and struct handling, plus short practice tasks.

Learn arrays in BigQuery, an ordered collection of same-type items for handling one-to-many and semi-structured data. Practice array syntax with square brackets and numeric or string examples.

Learn to write the same query using a common table expression, or cte, and verify the results. Expect future coverage of arrays in the next video lesson.

Discover how to use the unnest function to turn array items into separate rows. Flatten nested data into flat tables and observe how row counts increase.

Learn to use unnest with cross join to convert an array column into separate rows while preserving related columns, performing a cartesian join between unnest results and original data.

Master generating date arrays in BigQuery with the generate_date_array() function. Use start date, end date, interval, and a date part like day, week, month, quarter, or year.

Master the array_length function in Google BigQuery to determine the length of an array, with practical SQL examples showing lengths of 3 and 4.

Explore the struct function in arrays to store multiple data types and nested records, using struct constructors and subqueries, and reference fields via dot notation and aliases.

Explore how to use multiple struct statements and form a struct array with square brackets, alias as name details of employee, and observe resulting columns and rows.

Learn to build semi-structured data by combining multiple struct statements into arrays, mapping the computer science stream to students and their courses with aliases and nested structs.

Learn to join multiple rows in struct statements that contain arrays by using the union all operator in google bigquery, creating multi-row results with names, roll numbers, and courses.

Save query results to a BigQuery table by creating a dataset and naming the table, such as Array demo.student_details, with struct fields and semi-structured data.

Save query results to a BigQuery table and convert arrays into flat tables using the unnest function. Choose between cross join or comma syntax while preserving other column values.

Demonstrate how to use unnest and cross join in BigQuery to convert student details arrays into individual rows of name, roll number, and courses.

Leverage a common table expression (cte) and multiple unnest operations to disaggregate courses from student details via cross join. Select stream, name, roll number, and courses from the derived table.

Apply BigQuery's unnest and sum to calculate total fees per student from an array of course fees, using cross join, nest, and group by.

Learn to use array_agg to convert unnest results into arrays, and to build record data types with struct, including grouping by necessary columns and saving nested results as structured tables.

Master using array_agg to create nested arrays with struct fields, derive a table alias, and aggregate stream, name, roll number, and courses into a single result.

Explore the BigQuery public data project by loading a large Google Analytics sample dataset, navigate nested record fields like traffic source and hits, and practice querying operations on underscore sessions.

Group g_sessions by channel grouping and traffic source in BigQuery. Sum total hits, page views, and time on site, filtering non-null transaction revenue and positive time, order by hits, descending.

Explore how partitioning by date improves query performance on large datasets and learn to fetch all dates efficiently using a star query versus selecting a single date.

Master how to unnest nested and array data in BigQuery, using cross join and multi-level nesting to structure hits, product, and custom dimensions into clear results.

Learn how to export and connect BigQuery data to Google Data Studio, create reports, run queries, and explore visualizations such as pie and column charts.

Explore keyboard shortcuts in BigQuery: run queries with ctrl+enter, use tab for autosuggest, and select options like stream. For formatting, use the formatting menu, not keyboard shortcuts.

Learn to switch between standard sql and legacy sql in BigQuery using query settings, with standard sql as the default, and hash standard sql or hash legacy sql to toggle.

Master identity and access management in Google BigQuery by assigning project and dataset roles, such as BigQuery admin or data editor, to individuals, groups, or service accounts.

Explore BigQuery data types, including Int64, numeric, float 64, bool, string, date, date time, time, timestamp, array, struct, and geography.

Learn to create a BigQuery table using create table statement in DDL, defining dataset.table and columns like store_id int64, store_name string, date_founded date, and address struct with array of string.

explore creating tables in BigQuery with create table if not exists and create or replace table, including adding a phone number field as string.

Learn to create a not null, partitioned table by a pseudo column partition date, with options like expiration timestamp, partition expiration days, description, and labels.