SQL For Data Science With Google Big Query
4.5 (748 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
3,297 students enrolled

SQL For Data Science With Google Big Query

Learn SQL for Data Visualization, Data Analysis, and Data Science Using Google Cloud Platform
4.5 (748 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
3,297 students enrolled
Created by Chris Levy
Last updated 1/2019
English
English [Auto-generated]
Current price: $64.99 Original price: $99.99 Discount: 35% off
3 hours left at this price!
30-Day Money-Back Guarantee
This course includes
  • 7 hours on-demand video
  • 40 downloadable resources
  • Full lifetime access
  • Access on mobile and TV
  • Assignments
  • Certificate of Completion
Training 5 or more people?

Get your team access to 4,000+ top Udemy courses anytime, anywhere.

Try Udemy for Business
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.
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

I am a data scientist with years of experience working in industry. As a data scientist I use a ton of different tools/techniques but the one I use the most is probably SQL! 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.
Course content
Expand all 47 lectures 06:44:42
+ Introduction
6 lectures 49:17

Intro to the Course!

Preview 03:20

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.

Preview 03:40

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.

Signing up for 12 Month Free Trial of Google Cloud Platform and Google Big Query
07:20

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.

Getting Access to Big Query Public Data Sets and Pinning them Quick Access
05:03

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.

Preview 10:47

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.

How Big Query Charges Per Query and How to Make Your Credit Last A Long Time
19:07
+ Counting Records, Returning Distinct Records, and Ordering Records
4 lectures 38:50
Intro to Count and Count Distinct
10:57
Count and Count Distinct with Null Records
08:49

In this quiz you will be querying the `bigquery-public-data.new_york.311_service_requests` dataset

and using the count and count distinct functions to learn the concepts from the last couple videos on count functions in SQL.

Count and Count Distinct Quiz
5 questions

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.

Returning Distinct Records and Ordering Records
09:26

A quiz to practice returning a distinct list of values from a column as well as ordering by column.

Returning Distinct Records and Ordering With a Single Column
8 questions

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).

Returning DIstinct Records and Order with Multiple Columns
09:38
+ Where Clause
5 lectures 47:23

In this video we introduce the where clause which is used to filter records.

Introduction to Where Clause
07:27

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 Clauses On Numerical Columns
13:07

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.

Where Clause On String (Text) Columns
09:59

In this quiz you will get to practice writing queries with where clauses. The questions will also possibly cover material from previous lectures such as counting, distinct, and ordering.


All the questions on this quiz will make use of the data set

`bigquery-public-data.austin_bikeshare.bikeshare_trips`

so take a look at it before you begin.


The WHERE clause filters will be done on columns of type INTEGER or STRING.

Where Clause Quiz With Integer and String Columns
12 questions

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.

Where Clause On TIMESTAMP Columns And Some Useful Extract Functions
13:28

For all the questions in this quiz, please use the data from the following table:

`bigquery-public-data.chicago_taxi_trips.taxi_trips`

In this quiz you will practice where clauses on a TIMESTAMP field.

You will also practice the extract function to extract a feature (such as hour for example) from a TIMESTAMP column. In this quiz you will also be counting taxi trips. You can use the column  unique_key, which is an unique identifier for each trip, along with count distinct to count the number of taxi trips in a given scenario. For example:


SELECT

  COUNT(DISTINCT unique_key) AS num_trips

FROM

  `bigquery-public-data.chicago_taxi_trips.taxi_trips`

WHERE

   extract(week from trip_start_timestamp) = 52


Where Clause Quiz With Timestamp Column
5 questions

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.

Where Clause: IS NULL & IS NOT NULL
03:22
+ Group By And Aggregate Functions
8 lectures 01:23:39

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.

Intro to Group By, Aggregation, and Google's Data Studio Visualization Tool
11:03

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 And Aggregation Functions: MAX MIN SUM COUNT AVG
20:05

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: COUNT with WHERE Clause and Visualization In Data Studio
12:43

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: SUM and Visualization In Data Studio
07:03

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: MAX MIN AVG with WHERE and Visualization In Data Studio
10:18

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.

Group BY Example: COUNT with HAVING and Visualization In Data Studio
06:23

This quiz will give you practice on group by statements and aggregate functions when grouping by a single column. For this quiz please use the data from here:

`bigquery-public-data.new_york_trees.tree_census_2015`

Read over the schema page as it will explain what each column in the dataset means.


Group By And Aggregate Functions Quiz
5 questions

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 Explanation
08:03

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.

Grouping By Multiple Columns Example
08:01
+ Project 1: New York Citi Bike Dashboard
7 lectures 48:21

We introduce the project and what you will be building!

Project Intro
03:42

Navigate through Google Data Studio Reporting and Data Sources tools.

Quick Intro to Google Data Studio Reporting and Data Sources
04:45

Read the attached resource for the question you need to go and answer.

Prepare Data Set Question: NY Citi Bike By Day
01:37

Read the attached resource for the answer to the previous lectures question.

Prepare Data Set Answer: NY Citi Bike By Day
01:41

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 Data Set Data Studio: NY Citi Bike By Day
07:00

Write the three queries and add the three remaining aggregated data sets to Data Studio data sources

from Big Query. The three views are:

  1. Bike stats by start station

  2. Bike stats by Gender and User Type

  3. 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.

Prepare Remaining Three Data Sets In Data Studio: NY Citi Bike Project
11:05

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.

Create Dashboard in Data Studio
18:31
+ Joins (Joining Multiple Tables Together)
6 lectures 01:08:51

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.

Intro to Joins
15:38
Left Join Example Big Query Census Data
10:16

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.

Joining Tables with Duplicate Records Leads to Duplicate Results Example
08:28

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.

Trimming and Lowering When Joining on Columns of Type String
08:56

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.

Joins & Where Clause & Group By and Aggregate: Putting it all together Examples
12:52

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.

Joining On Multiple Columns With Multiple Tables (More than 2)
12:41
+ Census International Data Dashboard Project
1 lecture 18:20

This is the intro video for the international census dashboard project in data studio.

Project Intro
18:20
+ Big Query Standard SQL Functions
7 lectures 36:23

Intro to the documentation page for standard sql functions.

Intro to Big Query Standard SQL Functions
02:08

Few examples using the CAST function to convert data types.

Examples With Cast Function
04:07

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 Mathematical Functions
09:05

In this video we learn about different common functions for operating on strings.

Examples with String Functions
07:43

In this video we get to know lots of functions for working with dates, times, timestamps, and datetimes.

Examples with Date and Timestamp Functions
12:44
Practice Questions for SQL Functions Section
00:23

See the attached resource with the solutions.


Solutions for SQL Functions Questions
00:13
+ Case Statements
2 lectures 11:00
Intro to Case Statement Syntax and Example
05:11
Case Statement Examples
05:49