SQL For Data Science With Google Big Query

Name: SQL For Data Science With Google Big Query
Rating: 4.7 (2463 reviews)

Learn SQL for Data Visualization, Data Analysis, and Data Science Using Google Cloud Platform

Created byChris Levy

Last updated 11/2020

English

What you'll learn

Use SQL for data mining, data analysis , data science, and data visualization.
Be confident in using the Google Big Query Tool and Ecosystem.
Build awesome dashboards with Google Data Studio and Google Big Query as the backend.

Course content

10 sections • 57 lectures • 6h 45m total length

Introduction3:20
Intro to the Course!
Creating Google Account3:40
You can skip this video if you already have a Google/Gmail account. You need a Google account to sign up for Google Cloud Platform which will then give us access to Google Big Query. Not the most exciting of videos! Just going through the process of creating a Google/Gmail account.
Signing up for 12 Month Free Trial of Google Cloud Platform and Google Big Query7:20
In this video we show how to sign up for a 12 month free trial of Google Cloud Platform which is needed to access Google Big Query. This step must be completed to follow through with the rest of the course so it's really important you take the time to do it. Although a credit card is needed (or banking information) you will not be charged anything. This is all explained in detail in the video. Again, not the most exciting video but its an important step in the set up for this course.
Getting Access to Big Query Public Data Sets and Pinning them Quick Access5:03
Through out this course we will be using the Big Query Open Public data sets. However, they may not show up by default in the right data set area. Be sure to follow the instructions in this video to find out how to make the Big Query public data sets show up and how to save them to the right area for quick and easy future access.
Introduction Big Query UI and Functionality10:47
Don't worry about following along with all these steps. Better just to watch so you can see at a high level the different UI elements. In other videos we will be using these features at a slower pace. Learn how to log into the Google Cloud Platform and get into the UI for Google Big Query. Learn where to locate data sets and tables and preview the data and the size and number of records for a specific table. Learn how to use the query editor and execute queries and view results. Learn how to format the query and view query history.
How Big Query Charges Per Query and How to Make Your Credit Last A Long Time19:07
In this video we learn how Google Big Query charges you per query. This is a very import lesson to understand as we go through a number of tips and examples to spend as little as possible when querying Big Query. Each query will use a little bit of your $300 free credit you received when signing up for Google Cloud Platform. After watching this video check out the attached questions and answer resource documents to help your understanding of the concepts.

Intro to Count and Count Distinct10:57
Count and Count Distinct with Null Records8:49
Quiz 1: Count and Count Distinct Quiz0:07
Quiz 1 Solutions
Returning Distinct Records and Ordering Records9:26
In this video we learn how to return the distinct set of records for a single column.
We also introduce the ORDER BY clause which can be used to order the results of a query by a column.
Quiz 2: Returning Distinct Records and Ordering With a Single Column0:05
Quiz 2 Solutions
Returning DIstinct Records and Order with Multiple Columns9:38
In this video we learn how to return the distinct set of records for multiple columns.
We also use the ORDER BY clause which can be used to order the results of a query by a column(s).

Introduction to Where Clause7:27
In this video we introduce the where clause which is used to filter records.
Where Clauses On Numerical Columns13:07
In this video we cover examples of queries with where clauses,
where the conditions involve filtering on a numerical column. We learn about the following operators in the context of acting on a numerical column:
equal
not equal
greater than and greater than or equal
less than and less than or equal
IN
Be sure to check out the attached resource which you can review after the video. It has all the examples we covered with a few notes.
Where Clause On String (Text) Columns9:59
In this video we cover examples of queries with where clauses,
where the conditions involve filtering on a string (text) column. We learn about the following operators in the context of acting on a numerical column:
equal
not equal
IN
LIKE
Be sure to check out the attached resource which you can review after the video. It has many of the examples we covered as well as some key points.
Quiz 3: Where Clause Quiz With Integer and String Columns0:12
Quiz 3 Solutions0:01
Where Clause On TIMESTAMP Columns And Some Useful Extract Functions13:28
In this video we cover examples of queries with where clauses, where the conditions involve filtering on a TIMESTAMP column. We also learn how to use the extract function to extract certain features from a TIMESTAMP field such as the day, year, month, hour, minute and week.
Be sure to check out the attached resource for this lesson which you can review after the video. It has many of the examples we covered as well as some key points.
Quiz 4: Where Clause Quiz With Timestamp Column0:25
Quiz 4: Solution
Where Clause: IS NULL & IS NOT NULL3:22
It is very common to have NULL values in a column or multiple commons within a table.
In this video we show how to filter on whether a column has NULL values or not.

Intro to Group By, Aggregation, and Google's Data Studio Visualization Tool11:03
In this video we do an example to introduce the group by statement and an aggregate function. We also visualize the results in Google's Data Studio visualization tool. The point of this video is not to go into details. The purpose of it is to just introduce the concept behind group by and aggregation as well as Google's tool, Data Studio.
Group By And Aggregation Functions: MAX MIN SUM COUNT AVG20:05
In this video we discuss GROUP BY and aggregate functions in detail. Please be sure to go through the corresponding resource as well and read it. In this video we explain the concept of grouping by a single column and then performing aggregated functions on top of the different groups.
Group By Example: COUNT with WHERE Clause and Visualization In Data Studio12:43
In this video we do a Group By example and Count Aggregation with a Where clause to find the total number of bike rides per hour. We also get to practice the extract function. Finally, we make a nice distribution graph in Data Studio.
Group By Example: SUM and Visualization In Data Studio7:03
In this video we do a Group By example and Sum Aggregation to find the total number of crimes by crime major category. Finally, we make a nice distribution graph in Data Studio.
Group By Example: MAX MIN AVG with WHERE and Visualization In Data Studio10:18
In this video we do a Group By example and MIN MAX AVG Aggregation to find some aggregate statistics for the home baseball team. Finally, we do some visuals in Data Studio.
Group BY Example: COUNT with HAVING and Visualization In Data Studio6:23
In this video we do a Group By example and Count Aggregation along with a Where clause and a Having clause to find the number of motor vehicle accidents by contributing factor where the number of accidents has to be greater than 10000. Finally, we do some visuals in Data Studio.
Quiz 5: Group By And Aggregate Functions Quiz0:10
Quiz 5 Solutions
Grouping By Multiple Columns Explanation8:03
In this video we explain how grouping by multiple columns works. Watch the video and then read through the attached resource document.
Grouping By Multiple Columns Example8:01
In this video we do two examples where we group by multiple columns and apply an aggregate. The first example calculates the most popular bike trip and the average trip duration. The second example involves making a nice time series visualization in Data Studio for London Fire Brigade Service calls and colouring the series by multiple groups.

Project Intro3:42
We introduce the project and what you will be building!
Quick Intro to Google Data Studio Reporting and Data Sources4:45
Navigate through Google Data Studio Reporting and Data Sources tools.
Prepare Data Set Question: NY Citi Bike By Day1:37
Read the attached resource for the question you need to go and answer.
Prepare Data Set Answer: NY Citi Bike By Day1:41
Read the attached resource for the answer to the previous lectures question.
Prepare Data Set Data Studio: NY Citi Bike By Day7:00
In this video we show how to take the query from the previous lesson and add the results as a data set in Google Studio. We also add any necessary calculated fields as well.
Prepare Remaining Three Data Sets In Data Studio: NY Citi Bike Project11:05
Write the three queries and add the three remaining aggregated data sets to Data Studio data sources
from Big Query. The three views are:
Bike stats by start station
Bike stats by Gender and User Type
Bike stats by BikeID
At some point in the video you will pause it, open up the attached resource, and write the three queries for the three questions. Then when you are done resume watching and we will review the answers.
Create Dashboard in Data Studio18:31
In this video we build a sample dashboard in Data Studio using the different bike data sources we built in Data Studio from the past videos in this section. After watching this, I encourage you go to ahead and try to make your own data sources and dashboard. After watching this video you will have a good idea of some of the things data studio can do.

Intro to Joins15:38
In this lesson we introduce the concept of joining tables. After the video please take time to review the attached resource and understand the concepts before moving on.
Left Join Example Big Query Census Data10:16
Joining Tables with Duplicate Records Leads to Duplicate Results Example8:28
In this lesson we show an example with a NY taxi trip data set where we join two tables and one table happens to have a couple duplicate records. This leads to duplicates in the join query result. This is a good lesson to learn early when doing joins.
Trimming and Lowering When Joining on Columns of Type String8:56
In this video we do an example of joining two tables on string columns. In order for the join to work we need to lower case the columns before joining. We also add in the trim function for good measure. Don't forget to check out the attached resource.
Joins & Where Clause & Group By and Aggregate: Putting it all together Examples12:52
In this video we are putting all our tools/skills together in single examples.
Joins
Where Clauses
Group By
Aggregate Functions
Order By
Having
Check out the attached resource after the video to review the examples we did.
Joining On Multiple Columns With Multiple Tables (More than 2)12:41
In this video we do an example with international census data. We join multiple tables (more than 2) and several of the joins are on more than one column.

Intro to Big Query Standard SQL Functions2:08
Intro to the documentation page for standard sql functions.
Examples With Cast Function4:07
Few examples using the CAST function to convert data types.
Examples with Mathematical Functions9:05
Intro to the mathematical function documentation and an example of using SQRT and POWER to find the distance between lat/lng points using pythagorean theorem. This approximation only works for lat/lng points relatively close because it assumes the earth is flat.
Examples with String Functions7:43
In this video we learn about different common functions for operating on strings.
Examples with Date and Timestamp Functions12:44
In this video we get to know lots of functions for working with dates, times, timestamps, and datetimes.
Practice Questions for SQL Functions Section0:23
Solutions for SQL Functions Questions0:13
See the attached resource with the solutions.

Requirements

Able to follow instructions and passion to learn.
Credit card or bank account to sign up for Google Cloud Platform Free Trial. Your card is not charged. Its just for identifying you and is required during the sign up process.

Description

This course assumes you know nothing about SQL and teaches you all the basics as well as more advanced features/methods. You will also get to learn Google Big Query, a very popular cloud based tool for querying large amounts of data at scale in the cloud. Not only will you learn SQL, but you will learn a lot about data visualization and how to gather insights from the data. You will also have access to hundreds of huge real world data sets that come along with the Google Big Query Ecosystem. Even after finishing the course content, you can still continue to improve by doing all sorts of projects on the data made available.

This course requires a Google account (which is Free) and a free 12 month trial for Google Cloud Platform (GCP). This is how you access Google Big Query which is one of the services offered through GCP. When signing up for the 12 month free trial of GCP you get $300 USD to spend on any GCP services. This is more than enough to query the data sets in this course. I used less than 5% of the credits in preparing as well as completing all the materials. As part of the sign up process you will require a credit card or bank account details so GCP can verify your identity. You will not be charged or billed during your free 12 month trial. Instructions for signing up will be available in introductory videos for the course.

Who this course is for:

Anyone interested in data and mining insights from data.
Anyone wanting to learn SQL.
Anyone wanting to learn cloud technology, and in particular Google Big Query.
Anyone wanting to Practice SQL further on awesome real world data sets in the cloud.
Anyone wanting to learn more about building dashboards and data visualizations.

SQL For Data Science With Google Big Query

What you'll learn

Explore related topics

Course content

Introduction6 lectures • 49min

Counting Records, Returning Distinct Records, and Ordering Records8 lectures • 39min

Where Clause9 lectures • 48min

Group By And Aggregate Functions10 lectures • 1hr 24min

Project 1: New York Citi Bike Dashboard7 lectures • 48min

Joins (Joining Multiple Tables Together)6 lectures • 1hr 9min

Census International Data Dashboard Project1 lecture • 18min

Big Query Standard SQL Functions7 lectures • 36min

Case Statements2 lectures • 11min

Next Steps: Start Your Own Projects!1 lecture • 3min

Requirements

Description

Who this course is for: