AllonData Academy, Simplify the Complexity

BigQuery

117 items

The analytical warehouse, where raw data becomes business insight.

  • Fundamentals94 items
    • Basic SELECT & Filtering6 items
      • SELECT
      • WHERE
      • ORDER BY
      • DISTINCT
      • LIMIT
      • Column aliases
    • Operators18 items
      • Arithmetic Operators4 items
        • +
        • -
        • *
        • /
      • Comparison Operators6 items
        • =
        • <
        • >
        • <=
        • >=
        • <>
      • Logical Operators3 items
        • AND
        • OR
        • NOT
      • Membership Operators2 items
        • IN
        • NOT IN
      • Range Operator1 item
        • BETWEEN ... AND
      • Pattern Matching Operators2 items
        • LIKE
        • NOT LIKE
    • Data Types5 items
      • Numeric data types
      • String data types
      • Date and time data types
      • Boolean values
      • NULL
    • Single-Row Functions36 items
      • CASE / Conditional Logic2 items
        • CASE
        • IF
      • Character Functions15 items
        • UPPER()
        • LOWER()
        • INITCAP()
        • SUBSTR()
        • INSTR()
        • LPAD()
        • RPAD()
        • LTRIM()
        • RTRIM()
        • TRIM()
        • CONCAT()
        • REPLACE()
        • TRANSLATE()
        • REVERSE()
        • LENGTH()
      • Number Functions5 items
        • ROUND()
        • TRUNC()
        • MOD()
        • POWER()
        • ABS()
      • Date Functions9 items
        • CURRENT_DATE()
        • DATE_ADD()
        • DATE_SUB()
        • DATE_DIFF()
        • DATE_TRUNC()
        • EXTRACT()
        • LAST_DAY()
        • FORMAT_DATE()
        • PARSE_DATE()
      • Conversion Functions2 items
        • CAST()
        • EXTRACT()
      • General / NULL Functions3 items
        • IFNULL()
        • NULLIF()
        • COALESCE()
    • Aggregate Functions & Grouping7 items
      • Aggregate Functions5 items
        • MIN()
        • MAX()
        • COUNT()
        • AVG()
        • SUM()
      • Grouping2 items
        • GROUP BY
        • HAVING
    • Joins4 items
      • INNER JOIN
      • LEFT JOIN
      • RIGHT JOIN
      • FULL OUTER JOIN
    • Subqueries5 items
      • Subqueries in SELECT
      • Subqueries in WHERE
      • Subqueries in FROM
      • IN subqueries
      • Correlated subqueries
    • Window / Analytical Functions12 items
      • Window-Specific Functions5 items
        • ROW_NUMBER()
        • RANK()
        • DENSE_RANK()
        • LEAD()
        • LAG()
      • Aggregate Functions Used as Window Functions5 items
        • MIN()
        • MAX()
        • COUNT()
        • AVG()
        • SUM()
      • Window Clauses2 items
        • OVER
        • PARTITION BY
    • CTEs (WITH Clause)1 item
      • WITH clause
  • BigQuery & Table Concepts18 items
    • DDL4 items
      • CREATE
      • ALTER
      • DROP
      • TRUNCATE
    • DML4 items
      • INSERT
      • UPDATE
      • DELETE
      • MERGE
    • BigQuery Tables4 items
      • Native Tables
      • Temporary Tables
      • GCS External Tables
      • BigLake Tables
    • Views & Materialized Views4 items
      • Views
      • Materialized Views
      • When to use
      • When not to use
    • Partitioning & Clustering2 items
      • Partitioning
      • Clustering
  • BigQuery Optimization5 items
    • Selecting only required columns
    • Efficient filtering
    • Proper join usage
    • Avoiding unnecessary data processing
    • General BigQuery query optimization practices

You'll learn to: design and query a serverless data warehouse at scale, and write cost-efficient SQL that a senior engineer would approve.

Full curriculumDownload Curriculum