Google Biqquery

  1. Home
  2. Google-data-studio
  3. Google Biqquery

Tags: Google Data Studio

Master Google BigQuery Analytics with SQL used in Big Query to analyze Real data for data scientists, Developers, etc.

Last updated 2022-01-10 | 4.3

- What is google Bigquery?
- Google Bigquery in analytics
- Bigquery for Google Analytics

What you'll learn

What is google Bigquery?
Google Bigquery in analytics
Bigquery for Google Analytics
Master all the important concepts of Google BigQuery
Digital Marketers will be able to create their own analysis sheet using BigQuery
Students will be expert in Arrays
UNNEST
STRUCT
CTE
Derived Tables
Structed data
unstructured data
etc
Master all the concepts of SQL in Big Query
Can write all the commands like Joins
Group by
Order By
Having clause
SubQueries
etc.
Create tables with partitioning
Can easily connect the Google BigQuery to Google Data Studio
Learning based on real time project which helps students to apply the concepts in his job
Helps the students to be Job ready in Google BigQuery

* Requirements

* A Free Google account(Free Gmail account)
* PC with Internet connection
* Knowledge of Digital marketing will be even better although it is not required at all.

Description

  • What is google Bigquery?
  • Google Bigquery in analytics
  • Bigquery for Google Analytics
  • Master all the important concepts of Google BigQuery
  • Digital Marketers will be able to create their own analysis sheet using BigQuery
  • Students will be expert in Arrays, UNNEST, STRUCT, CTE, Derived Tables, Structed data, unstructured data, etc
  • Master all the concepts of SQL in Big Query
  • Can write all the commands like Joins, Group by, Order By, Having clause, SubQueries, etc.
  • Create tables with partitioning
  • Can easily connect the Google BigQuery to Google Data Studio
  • Learning based on real time project which helps students to apply the concepts in his job
  • Helps the students to be Job ready in Google BigQuery

Course content

13 sections • 162 lectures

Introduction 7 lectures

Introduction Preview 03:15

What is Google BigQuery and Why it is used? Preview 02:55

Setting up the BigQuery Sandbox account Preview 06:31

Explaining about Datasets and tables Preview 06:21

Uploading files from local to create tables under a dataset Preview 11:12

Overwrite a file Preview 02:15

Executing multiple Queries Preview 04:14

Retrieve the records 14 lectures

SELECT Statement and SELECT DISTINCT Preview 04:16

SQL Code used Preview 00:05

CASE with SELECT Statement Preview 05:16

SQL Code used Preview 00:09

WHERE Clause and Comparison operator in WHERE Clause Preview 06:14

SQL Code used Preview 00:13

Logical Operators in WHERE Clause Preview 05:58

SQL Code used Preview 00:16

Copy Table to another table Preview 03:54

Arithmetic operators Preview 09:42

SQL Code used Preview 00:16

Order of execution with WHERE clause Preview 02:14

COUNT the rows from table Preview 04:49

SQL Code used Preview 00:02

Special Operators 8 lectures

NULL and NOT NULL Preview 03:55

SQL Code used Preview 00:06

BETWEEN and NOT BETWEEN operator Preview 03:19

SQL Code used Preview 00:06

IN and NOT IN Operator Preview 04:21

SQL Code used Preview 00:18

LIKE Operator Preview 08:01

SQL Code used Preview 00:12

Sorting and grouping records 8 lectures

ORDER BY Clause Preview 07:33

SQL Code used Preview 00:13

ORDER BY column position Preview 01:44

SQL Code used Preview 00:11

GROUP BY clause Preview 09:58

SQL Code used Preview 00:22

ORDER OF execution with GROUP BY and ORDER BY Preview 02:27

SQL Code used Preview 00:03

Google BigQuery SQL Functions 16 lectures

Other Numerical functions Preview 06:23

SQL Code used Preview 00:10

String Functions Preview 06:47

SQL Code used Preview 00:17

Ltrim, rtrim, trim and strpos string functions Preview 05:08

SQL Code used Preview 00:09

replace, repeat, reverse and substr functions Preview 05:17

SQL Code used Preview 00:08

Date Functions in Google BigQuery Preview 09:02

SQL Code used Preview 00:21

Date Functions in Google BigQuery continued Preview 06:02

SQL Code used Preview 00:07

Strange behavior of BigQuery parser with GROUP BY and column Alias Preview 03:49

SQL Code used Preview 00:07

HAVING Clause Preview 05:57

SQL Code used Preview 00:20

Joins and Subqueries 23 lectures

Subquery Preview 05:54

SQL Code used Preview 00:12

Subqueries as column expressions Preview 05:45

SQL Code used Preview 00:04

UNION ALL operator Preview 08:32

SQL Code used Preview 00:08

INTERSECT DISTINCT operator Preview 02:56

SQL Code used Preview 00:06

EXCEPT DISTINCT operator Preview 03:05

SQL Code used Preview 00:05

INNER JOIN - Theory Preview 03:11

INNER JOINs Preview 05:29

SQL Code used Preview 00:09

LEFT Join - Theory Preview 03:45

Note about next Lecture Preview 00:10

LEFT JOIN Example Preview 10:03

SQL Code used Preview 00:12

RIGHT JOIN Preview 03:37

SQL Code used Preview 00:10

FULL JOIN Preview 05:23

SQL Code used Preview 00:03

CROSS JOIN Preview 04:39

SQL Code used Preview 00:05

Derived Tables and CTE 22 lectures

Derived Tables - Part 1 Preview 05:27

SQL Code used Preview 00:05

Derived Tables - Part 2 Preview 03:02

SQL Code used Preview 00:10

Creating 2 tables using CSV for Project 1 Preview 05:53

Get productid, Total Qty and profit using INNER JOIN and GROUP BY Preview 05:25

SQL Code used Preview 00:11

Getting the same result using derived tables Preview 07:14

SQL Code used Preview 00:14

Apply HAVING Clause on derived tables Preview 03:32

SQL Code used Preview 00:16

Derived Table with CASE with SELECT statement - Task and Solution Preview 09:06

SQL Code used Preview 00:28

Project 2 - Load 3 tables Preview 02:02

CTE - Theory Preview 02:31

Write SQL Query to get businessentityid and full name Preview 01:07

Solution and CTE Example Preview 04:26

SQL Code used Preview 00:07

CTE Example with 2 Tables Preview 11:38

SQL Code used Preview 00:16

CTE Example with 3 Tables Preview 05:45

SQL Code used Preview 00:15

Arrays, UNNEST, Struct, etc. 36 lectures

Project 3 - Arrays Preview 00:56

Arrays in BigQuery Preview 10:10

SQL Code used Preview 00:11

Write the same query using CTE Preview 01:28

SQL Code used Preview 00:11

UNNEST in Arrays Preview 03:39

SQL Code used Preview 00:03

UNNEST() with CROSS JOIN Preview 04:59

SQL Code used Preview 00:12

GENERATE_ARRAY() function Preview 02:54

SQL Code used Preview 00:02

GENERATE_DATE_ARRAY() Function Preview 03:28

SQL Code used Preview 00:09

ARRAY_LENGTH() function Preview 01:18

SQL Code used Preview 00:02

STRUCT() Function Preview 04:12

SQL Code used Preview 00:11

Multiple Struct Statements Preview 02:52

SQL Code used Preview 00:07

Multiple Struct statements as arrays Preview 07:48

SQL Code used Preview 00:07

Joining multiple rows in Struct statements which has arrays Preview 04:21

SQL Code used Preview 00:18

Save Results of the Query Preview 04:05

Save the Result and get the output as flat tables Preview 04:04

SQL Code used Preview 00:14

Assignment - Using Multiple UNNEST functions Preview 09:36

SQL Code used Preview 00:11

Using Multiple UNNEST functions using CTE Preview 04:08

SQL Code used Preview 00:07

Assignment - UNNEST Function with SUM function Preview 05:14

SQL Code used Preview 00:28

ARRAY_AGG() Function Preview 11:15

SQL Code used Preview 00:24

Assignment - ARRAY_AGG() Function multiple times Preview 08:52

SQL Code used Preview 00:31

Final Project and Queries 11 lectures

Project using bigquery-public-datasets Preview 07:31

Assignment - Analyzing the GA data using SELECT statement, group by, etc. Preview 08:42

SQL Code used Preview 00:07

Selecting Records for all the dates - not by partition. Preview 02:18

SQL Code used Preview 00:07

UNNEST multiple levels - Contains Assignment Preview 15:17

SQL Code used Preview 00:34

Exporting the BigQuery Data to Google Data Studio Preview 06:20

Keyboard Shortcuts in BigQuery Preview 02:18

SQL Dialects in BigQuery Preview 03:41

Optional - Identity and Access Management Preview 05:32

Create Tables 7 lectures

Available Data types in BigQuery Preview 07:34

CREATE Table Statement Preview 09:56

SQL Code used Preview 00:05

CREATE IF NOT EXISTS and CREATE OR REPLACE TABLE Preview 03:30

SQL Code used Preview 00:12

CREATE TABLE with NOT NULL and Partition Preview 07:38

SQL Code used Preview 00:10

Views 6 lectures

CREATE VIEWS Preview 05:33

SQL Code used Preview 00:24

CREATE or REPLACE VIEW or CREATE VIEW IF NOT EXISTS Preview 01:39

SQL Code used Preview 00:47

Dropping a view Preview 00:58

SQL Code used Preview 00:00

Insert Records 3 lectures

CREATE TABLE with columns manually Preview 03:11

INSERT Statement Preview 10:37

SQL Code used Preview 00:15

Bonus Section - Thank You 1 lectures

Bonus Lesson : Let us stay connected Preview 00:51