SQL For Data Science With Google Big Query
- 7 hours on-demand video
- 40 downloadable resources
- Full lifetime access
- Access on mobile and TV
- Certificate of Completion
Get your team access to 4,000+ top Udemy courses anytime, anywhere.Try Udemy for Business
- 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.
- 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.
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.
- 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.
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.
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.
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.
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.
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.
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:
greater than and greater than or equal
less than and less than or equal
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.
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:
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.
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
so take a look at it before you begin.
The WHERE clause filters will be done on columns of type INTEGER or STRING.
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.
For all the questions in this quiz, please use the data from the following table:
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:
COUNT(DISTINCT unique_key) AS num_trips
extract(week from trip_start_timestamp) = 52
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.
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.
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.
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.
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:
Read over the schema page as it will explain what each column in the dataset means.
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.
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.
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.
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.
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.