Throughout this course, you will be using Mode Analytics and their SQL editor. We will help you set this up and connect to our database with test data.
You will learn what SQL actually is and why we use it. We also learn about relational databases and why they are useful.
You will write your first SQL query using the SELECT and FROM keyword. You'll learn to get data out of a table in a database.
WHERE and LIMIT are two simple filters that you will learn. Very simple statements but very powerful.
There are different ways of filtering, but in the end, it's all about giving conditions that can be either true or false. These are called booleans and with booleans, there are comparison operators.
More tools to allow you to specify filtering statements. You can be more specific with your constraints with AND OR NOT.
Text and strings are common data types stored in tables. Learn how to filter for them with LIKE and ILIKE and learn the difference between them.
You can also have arrays in tables, learn how to check if an element is in a list of elements.
Data is often not very clean. There are many instances where some rows do not have all entries in every column. You will see a lot of NULL values and you'll have to learn to filter them.
It's very often that you'll need to order your data. For example, if you want to know what your company spends most on? Which client contributes most to our revenue? All these questions you need ordering.
Now that you know how to query data, how do you make sense of it? Most of the time, we want to aggregate it to get insight. How much revenue did we make in total? You'll have to sum all the rows in your data.
Aggregations are even more useful if you're able to group by certain characteristics. For example, what is the total revenue generated by each age group? Or by each country?
Just like WHERE, you can filter your aggregated results by using the statement HAVING.
Data isn't always clean. Sometimes there are duplicate rows or two rows with the same user id. If you want to count unique users, you'll want to use distinct.
If Else statements are useful in programming. In SQL, the equivalent is CASE WHEN statements so you can write custom logic flows.
Stitching data from one query to another is useful when you have to query different data sources. With UNION you can do that.
The most important feature of SQL is the JOIN which is the basis of what relational database means. Data from different tables have relationships with each other. With JOIN, you can unify them.
Learn the different kinds of joins that you might want to use. Knowing the difference is vital whenever debugging your queries.
Let's do a few examples that are very similar to what you'll do in a big tech company. I go trhough common joins you'll need to perform.
What if you want to get data that's somehow relates to other rows previously? Most of the time, we can't, but with WINDOW functions, you might be able to get things like Rolling Sum.
You can now write 99% of the queries. The last 1%, there are tons of little tricks, but it's different for every company. You'll learn the keywords to search for when you need to get something done.
SQL for Data Analytics is a beginners' course that will teach you everything you need to know to start using SQL to query data. I'm going to teach it in the way that I learned it through working at big tech companies. I'll be using tools that are very similar to what you'll use at a company like Facebook. My assignments try to mimic what you'll encounter when you work at a company with lots of user data.
JomaClass is $8/month (billed annually)
Enter your email and we’ll send you SQL for Data Analytics videos and some samples of our favorite courses.
Data Science is a combination of computer science and statistics. The most common way you'll work with data is to query them out of a database. Hence you'll need SQL for that.
Even as a software engineer, at big tech companies, you'll have to monitor your features you've built and perform analysis on them. There's no escaping SQL, it's one of the easiest way to query data. You'll be looking at your logs often which is stored in a database.
This is especially important for Product Analysts or roles that are similar. You will act as the person who most understands your product through data. You need to be able to questions like "what's our user retention like", "where does the user traffic come from", etc.
The assignments will allow you to apply what you've learned throughout the course. They are problems meant to challenge you, allowing you to learn useful tools when working as an analyst.
Note: The assignment requires you to access our internal database for the students. Also, this assignment references older assignments.
We will be using Mode Analytics, which many companies like Twitch, Shopify, and Reddit use. Big tech companies use in-house tools that they built themselves, and they're very similar to Mode.
Think of it like a Patreon subscription where you get access to all my educational videos on programming, computer science, and data science. I make sure to teach the right things in the right order since a lot of students tend to give up or they don't build the right foundations.
I post multiple videos weekly and I'm constantly working on a new course. In addition, you have full access to a private group where you can interact with me and other students in the community.
$8/month paid yearly.
A typical course will have around 20 video lessons.