https://scosco.github.io/learn-bigquery/

Learn BigQuery!

Assuming Standard SQL is known, this material aims to provide a path to master SQL in semi-structured nested data.

Foundation

Foundational concepts are:

• unnest() - turning arrays into row format (and structs into columns)
• Structs - mastering them helps using unnest
• Arrays - mastering them helps using unnest
• Subqueries + Unnest - Run SQL on arrays!
• Flattening - Join arrays (unnested as rows) with their parent table

Data Invironment: Google Analytics Data

Export Schema
Example Data

Concepts focus on the data structure, not the possible settings of the tool:

• Session scoped fields
• Hit scoped fields
• Product scoped fields (Enhanced Ecommerce)
• Internal Promotion fields
• tbd …

Data Environment: Firebase Data

Export Schema
Example Data

Concepts focus on the data structure, not the possible settings of the tool:

• Events and event_params
• tbd …

Prerequisites

There plenty of SQL courses out there. You can have a look at w3schools.com/sql/ and revisit some of its contents if necessary.

Analytical SQL concepts can be categorized roughly as follows (brackets indicate expertise needed for this training):

• Retrieve data from a table (proficient)
• Aggregate Data (proficient)
  • Aggregation functions
  • Aggregation in combination with Grouping
• Analytical functions with Windows (nice to have)
• Combine tables using Joins and Unions (strong)
• Combinations of all the concepts above (strong)