Learn BigQuery SQL Mastery taught by Googler

This lesson guides you through the fundamental steps of creating tables in Google BigQuery using SQL. You'll learn how to define table schemas, specify data types, and successfully set up new tables to store your data, making table creation simple and efficient.

This lesson offers a clear, step-by-step guide on how to safely and efficiently delete tables in Google BigQuery using SQL. You'll learn the essential commands and best practices to remove unwanted tables from your dataset with confidence.

This lesson provides a straightforward, step-by-step guide on seamlessly uploading your data from Google Sheets directly into BigQuery. Learn practical methods to connect your spreadsheets and import data, making your data migration process quick and easy.

This lesson covers the crucial SQL techniques for adding new rows of data to an already existing table in BigQuery. You'll learn how to effectively append or insert single and multiple records, ensuring your tables are kept up-to-date.

This lesson dives into advanced methods for efficiently inserting multiple rows of data into an existing BigQuery table. Learn optimized SQL commands and strategies to batch insert data, saving time and improving performance when populating your datasets.

This lesson focuses on mastering the UPDATE statement in BigQuery SQL. You'll learn how to modify and change existing values within specific rows or columns of your tables, ensuring your data remains accurate and current.

This lesson teaches you the essential SQL commands for removing specific rows or sets of data from an existing table in BigQuery. Learn how to precisely target and delete unwanted information while preserving the table structure.

This lesson demonstrates the ultimate way to clean up your BigQuery workspace by showing you how to delete all data within a table and then completely drop (remove) the table itself using SQL, ensuring a thorough cleanup.

This lesson introduces the most fundamental SQL operation: SELECT. You'll learn how to precisely choose and retrieve only the specific columns you need from your BigQuery tables, mastering the basics of data extraction.

This lesson teaches you how to use the AS keyword in BigQuery SQL to rename columns in your query results. Learn to create more readable and meaningful output by assigning temporary, descriptive aliases to your columns.

This lesson explains the fundamental WHERE clause in BigQuery SQL, teaching you how to filter your data based on specified conditions. You'll learn to retrieve only the rows that meet your exact criteria, making your queries more precise.

This tutorial expands on the WHERE clause by teaching you how to combine multiple filtering conditions using the AND and OR logical operators in BigQuery SQL. Learn to create complex and powerful filters to retrieve highly specific datasets.

This lesson delves into the LIKE and NOT LIKE operators in BigQuery SQL, crucial for advanced string pattern matching and filtering. You'll learn how to use wildcards (% and _) to search for and exclude data based on specific text patterns within your columns.

This lesson focuses on mastering the IN and NOT IN operators in BigQuery SQL, providing efficient ways to filter data. You'll learn how to select or exclude rows based on whether a column's value matches any item within a specified list.

This lesson teaches you how to effectively use the BETWEEN operator in BigQuery SQL to simplify your data filtering. You'll learn to easily select rows where a value falls within a specified inclusive range, streamlining your date, number, or text-based queries.

This lesson shows you how to effectively use the DISTINCT keyword in BigQuery SQL to easily eliminate duplicate rows or values from your query results. Learn to quickly clean and refine your datasets, ensuring you only retrieve unique records.

This lesson provides a quick and easy guide to counting the number of unique values within a specific column in BigQuery SQL. You'll learn how to use aggregate functions with DISTINCT to gain insights into the diversity of your data.

This lesson explains the fundamental SQL aggregation functions in BigQuery, such as COUNT, SUM, AVG, MIN, and MAX. You'll learn how to summarize and derive meaningful insights from your data by performing calculations across groups of rows.

This lesson is crucial for understanding how to group your data in BigQuery SQL. You'll learn to use the GROUP BY clause in conjunction with aggregation functions to perform calculations on distinct categories or segments within your dataset.

This lesson empowers you to filter aggregated data like a pro using the HAVING clause in BigQuery SQL. You'll learn how to apply conditions to the results of your GROUP BY aggregations, giving you precise control over your summarized data.

This lesson will turn you into a data sorting expert by mastering the ORDER BY clause in BigQuery SQL. You'll learn how to arrange your query results in ascending or descending order based on one or multiple columns, making your data presentation clear and insightful.

This lesson demonstrates how to efficiently skip rows in your BigQuery SQL queries using the OFFSET keyword. You'll learn to paginate results or fetch specific data segments by defining how many initial rows to bypass before returning your desired output.

This lesson provides an in-depth exploration of essential aggregate functions in BigQuery SQL, including SUM, AVG, COUNT, MIN, MAX, and more. You'll learn how to powerfully summarize large datasets, extracting key statistics and insights with ease.

This lesson teaches you to master the LIMIT clause in BigQuery SQL for efficient data retrieval. You'll learn how to restrict the number of rows returned by your query, enabling you to quickly fetch and analyze only the top or a specific count of results.

This lesson introduces you to the powerful WITH clause (Common Table Expressions or CTEs) in BigQuery SQL. You'll learn how to simplify complex queries, break them down into readable, manageable steps, and improve query organization for better performance and clarity.

This lesson builds on the basics of CTEs, teaching you advanced techniques for using multiple WITH clauses in BigQuery SQL. You'll learn how to chain CTEs together, reference previous common table expressions, and tackle even more complex data manipulation challenges with enhanced clarity and efficiency.

This tutorial is designed for beginners to master subqueries in BigQuery SQL. You'll learn how to embed one query within another to perform advanced filtering, retrieve related data, and solve complex problems that single queries cannot address.

This crucial lesson explains the logical order in which SQL queries are processed by BigQuery. Understanding this execution flow is key to writing correct, efficient, and optimized SQL, helping you anticipate results and debug queries effectively.

This lesson demystifies table schemas in BigQuery, explaining the fundamental data structure of your tables. You'll gain a clear understanding of what a schema is, its components (column names, data types, modes), and why it's crucial for effective data management and querying.

This lesson provides a practical guide on how to modify your table's schema and change data types for columns in BigQuery. You'll learn essential SQL commands to alter existing table structures, adapt to evolving data needs, and ensure data integrity.

This lesson provides practical SQL techniques to effectively exclude NULL values and blank (empty string) values from your BigQuery query results. You'll learn how to write precise filters to ensure your analysis only includes complete and meaningful data.

This lesson teaches you how to effectively handle and replace NULL values in BigQuery SQL using the IFNULL and COALESCE functions. You'll learn to substitute NULLs with specified default values, making your data more presentable and useful for analysis.

This lesson specifically explores how NULL values are handled by various aggregation functions (like SUM, AVG, COUNT, MIN, MAX) in BigQuery SQL. You'll learn the default behaviors and potential impacts of NULLs on your aggregated results.

This lesson specifically addresses how the SUM aggregation function behaves when encountering NULL values in your BigQuery SQL queries. You'll learn to understand its default behavior and explore methods to effectively manage NULLs to ensure accurate summation of your numerical data.

This lesson clarifies a critical concept in SQL: the distinct difference between NULL values and blank (empty string) values. You'll understand their unique meanings, storage, and how they behave differently in your BigQuery queries.

This lesson provides a comprehensive guide on how to use the CAST function in SQL to explicitly change the data type of a column or expression. You'll learn essential techniques for converting data types in BigQuery, crucial for data manipulation and ensuring compatibility in your queries.

This lesson highlights the critical differences between CAST and SAFE_CAST in BigQuery SQL. You'll learn when to use each function based on your error handling needs, understanding how SAFE_CAST prevents query failures during data type conversions by returning NULL on invalid casts.

This lesson provides a comprehensive guide to mastering CASE WHEN statements in BigQuery SQL. You'll learn how to implement conditional logic within your queries, allowing you to categorize data, create new columns based on conditions, and perform complex transformations with flexibility.

This lesson focuses on fundamental string manipulation in BigQuery SQL, teaching you how to easily convert text to either lowercase or uppercase. You'll learn to use essential string functions to standardize and prepare your textual data for analysis.

This comprehensive lesson guides you through mastering a wide array of string functions available in BigQuery SQL. You'll learn to manipulate, parse, search, and transform text data with precision, enhancing your ability to work with textual information effectively.

This lesson is dedicated to mastering the essential integer and float functions in BigQuery SQL. You'll learn how to perform various numerical operations, including rounding, truncation, mathematical calculations, and type conversions to precisely manipulate your numeric data.

This complete guide provides a thorough understanding of working with Boolean data types in BigQuery SQL. You'll learn how to store, query, and manipulate TRUE, FALSE, and NULL Boolean

This lesson teaches you essential SQL date functions in BigQuery to extract specific components like the date, month, and year from a timestamp or date column. You'll gain practical skills to break down date information for more granular analysis.

This lesson is your comprehensive guide to mastering various date functions in BigQuery SQL. You'll learn essential techniques for manipulating, comparing, and extracting information from date values, crucial for any time-series analysis and reporting.

This lesson focuses on mastering the CURRENT_DATE, CURRENT_TIME, CURRENT_DATETIME, and CURRENT_TIMESTAMP functions in BigQuery SQL. You'll learn how to dynamically retrieve current time-based values, essential for real-time reporting, logging, and time-sensitive data analysis.

This lesson teaches you to master datetime precision in BigQuery SQL by demonstrating how to round off and truncate DATETIME values. You'll learn to align your data to specific time units (e.g., hour, day) for cleaner analysis and reporting.

This lesson simplifies date arithmetic in BigQuery SQL, teaching you how to easily add or subtract days from existing date columns. You'll learn essential functions to perform calculations like finding a date in the past or future, crucial for time-based analysis.

This lesson is dedicated to mastering TIME functions in BigQuery SQL, enabling you to work with time-only data like a pro. You'll learn to extract components, perform arithmetic, and manipulate time values independently of dates, crucial for analyzing hourly or daily patterns.

This complete guide to mastering TIMESTAMP functions in BigQuery SQL covers everything from creation to manipulation. You'll learn to handle time zones, extract components, perform precise arithmetic, and work with the nuances of timestamp data for robust time-series analysis.

This lesson provides essential techniques for comprehensive date and time manipulation in BigQuery SQL. You'll learn how to add or subtract various time units like years, months, days, hours, and minutes from your date and timestamp values, enabling precise temporal calculations.

This complete guide to mastering the LEFT JOIN in BigQuery SQL will teach you how to combine data from two or more tables. You'll learn to retrieve all records from the left table and matching records from the right, ensuring no data from the primary table is lost.

This lesson provides a comprehensive guide to mastering INNER JOINs in BigQuery SQL. You'll learn how to combine rows from two or more tables based on a common, matching column, retrieving only the records that exist in all joined tables.

This lesson focuses on mastering the RIGHT JOIN in BigQuery SQL, an essential technique for combining data. You'll learn how to retrieve all records from the right table and matching records from the left table, ensuring complete data from the secondary table is included.

This lesson provides a comprehensive guide to mastering the FULL JOIN (or FULL OUTER JOIN) in BigQuery SQL. You'll learn how to combine data by returning all rows when there is a match in either the left or the right table, including non-matching rows from both sides.

This lesson guides you through mastering the CROSS JOIN in BigQuery SQL. You'll learn how this join operation creates a Cartesian product, combining every row from the first table with every row from the second table, a powerful technique for generating all possible combinations.

This lesson demonstrates how to effectively use the UNION ALL operator in BigQuery SQL. You'll learn to combine the result sets of two or more SELECT statements into a single output, efficiently stacking data from multiple tables while retaining all rows, including duplicates.

This lesson teaches you to master the SELF JOIN technique in BigQuery SQL, where a table is joined to itself. You'll learn how to use this powerful method to query hierarchical data or compare rows within the same table, solving complex relationship problems.

This lesson focuses on the EXCEPT keyword in BigQuery SQL, teaching you how to exclude data from one query's result set that exists in another. You'll learn to identify and return distinct rows from the first query that are not present in the second, a powerful technique for data comparison and anomaly detection.

This lesson provides a clear guide on the ROW_NUMBER() window function in BigQuery SQL. You'll learn how to assign a unique sequential integer to each row within a partitioned and ordered result set, which is essential for ranking and precise data ordering.

This lesson delves into the RANK() window function in BigQuery SQL, teaching you how to assign a rank to each row within a partition. You'll learn how RANK() handles ties by assigning the same rank, creating gaps in the numbering, crucial for precise data ranking.

This lesson focuses on the DENSE_RANK() window function in BigQuery SQL, demonstrating how it assigns ranks to rows within partitions without leaving any gaps after ties. You'll learn its distinct behavior compared to RANK(), making it ideal for continuous ranking scenarios.

This lesson explores the powerful LEAD() and LAG() window functions in BigQuery SQL, enabling you to access data from subsequent or preceding rows within your result set. You'll learn to analyze trends, calculate differences, and compare values across rows, moving data forward and backward for insightful analysis.

This lesson focuses on using the SUM() aggregate function within window frames in BigQuery SQL to calculate running totals (cumulative sums). You'll learn how to track progressive totals across your data, an essential technique for financial analysis, trend monitoring, and more.

This lesson teaches you how to effectively use the COUNT() aggregate function with window frames in BigQuery SQL. You'll learn to count rows within specific, defined windows or partitions of your data, providing localized counts for richer analytical insights.

This lesson provides a comprehensive understanding of the CUME_DIST() window function in BigQuery SQL. You'll learn how to calculate the cumulative distribution of a value within an ordered set of data, indicating the relative position of each row and helping you analyze data percentiles.

This step-by-step guide teaches you how to calculate the standard deviation of your data directly within BigQuery SQL. You'll learn to use the appropriate aggregate functions to measure the dispersion or spread of values around

This lesson teaches you to use the FIRST_VALUE() and LAST_VALUE() window functions in BigQuery SQL. You'll learn how to extract the first or last value from an ordered set of rows within a specific partition, invaluable for identifying boundary values in your data.

This lesson focuses on the PERCENT_RANK() window function in BigQuery SQL, teaching you how to calculate the percent rank of each row within a partition. You'll understand how it indicates the relative rank of a row as a percentage (from 0 to 1), essential for percentile analysis and data distribution insights.

This lesson demonstrates how to calculate moving averages using the AVG() aggregate function within window frames in BigQuery SQL. You'll learn to smooth out data fluctuations and identify trends by computing the average over a specified sliding window of rows.

This comprehensive guide walks you through calculating variance in BigQuery SQL. You'll learn how to use aggregate functions to measure how far each number in the set is from the mean, providing a critical statistical insight into your data's spread.

This step-by-step guide helps you understand and calculate correlation directly within BigQuery SQL. You'll learn how to use statistical functions to measure the strength and direction of a linear relationship between two numerical

This lesson focuses on mastering array aggregations in BigQuery SQL. You'll learn various techniques and best practices for creating and manipulating arrays from your data, including functions that gather multiple values into a single array column for advanced analysis.

This lesson focuses on mastering string aggregations in BigQuery SQL, specifically teaching you how to concatenate, group, and analyze text data from multiple rows into a single string. You'll learn powerful techniques to combine text values for reporting and further analysis.

This comprehensive guide to working with Arrays in BigQuery SQL covers everything from understanding array concepts to practical manipulation. You'll learn how to create, store, and query array data, providing a solid foundation for handling complex, repeating data structures.

This lesson is dedicated to mastering various array functions in BigQuery SQL. You'll explore essential techniques for manipulating and querying array data, including functions for checking array properties, accessing elements, and performing operations on array contents for diverse use cases.

This lesson explains an alternative, less preferred method for unnesting arrays in BigQuery SQL, specifically the scalar subquery approach. You'll understand how this technique works, but more importantly, learn why it's generally not recommended compared to more efficient and standard practices.

This lesson provides a clear guide on the crucial process of unnesting arrays in BigQuery SQL. You'll learn how to transform elements within an array into individual rows, making nested data accessible and queryable for traditional relational analysis.

This lesson provides a foundational understanding of STRUCT data types in BigQuery SQL. You'll learn their usage for grouping related fields into a single column, how to access their elements, and best practices for organizing complex, semi-structured data efficiently.

This lesson offers a deep dive into the advanced use case of embedding Structs within Arrays in BigQuery SQL. You'll gain detailed insights into querying and manipulating these highly nested data structures, allowing for sophisticated analysis of complex, interconnected datasets.

This lesson delves into advanced techniques for using array subqueries in BigQuery SQL. You'll learn best practices for embedding subqueries that interact with array elements, enabling highly sophisticated filtering, transformation, and analysis of your nested data.

This lesson identifies and demonstrates the preferred method for unnesting arrays in BigQuery SQL, emphasizing industry best practices. You'll learn the most efficient and recommended SQL syntax to transform array data into rows, ensuring optimal query performance and readability.

This lesson provides essential tips and techniques for modifying data within STRUCT data types in BigQuery SQL. You'll learn how to update specific fields nested inside a struct, crucial for maintaining and refining your complex, semi-structured datasets.

This lesson explores advanced techniques for working with Multiple Nested Structs in BigQuery SQL. You'll learn how to navigate, query, and manipulate highly complex, deeply nested data structures, enabling you to extract insights from intricately organized information.

This lesson delves into working with complex nested data types, specifically Arrays of Structs, in BigQuery SQL. You'll learn how to effectively query and manipulate these intricate structures where each element in an array is itself a structured record, enabling deep analysis of rich, hierarchical data.

This lesson covers advanced data analysis techniques by demonstrating how to use aggregate functions with STRUCT data types in BigQuery SQL. You'll learn to perform complex aggregations on structured fields within your data, unlocking deeper insights from your nested information.