As you progress, you’ll find situations where you need to combine more than one data pull to answer your question. In these situations, you’ll use subqueries and join or filter the results from these. Actually, you already have seen some subqueries in the examples from the last 2 posts. This post aims to provide an
Category: Mid-Career Tech
Resources to help mid-career professionals upskill to keep up with data science, without a programming background. Learn SQL and Python for free, at the speed of business, while remaining on your job.
Window functions : Automate lengthy Excel tasks
“Find our 5 best-selling products by country.””What was the 7-day moving average revenue for every day last month?” Chances are, you’ve come across one of these business questions before. And previously, you’ve probably solved them either by creating a ticket for your analytics team, or by writing manual formulas in Excel. Instead of doing these
Sort your data with “ORDER BY”
Most of you will use Excel to manipulate and plot the data you pull with SQL. Eventually, you’re likely to create charts for presentations to your management. If you sort your data with “ORDER BY” when you pull it in SQL, you’ll save time. As a result, you can skip the “Sort and Filter” step
When to use more complex joins
As you pull your data more often, you may encounter situations where you need more complex joins than what we’ve previously discussed. Here are some examples: You’re looking for something that did not happen: e.g. products without orders in the past 7 days. In this case, you need an outer join. Specifically, you would pull
Define your own metrics: Operators & conditionals
As you become confident with pulling data, you’ll start wanting more control over the metrics you report on. Hence, you’ll want to define your own metrics. Therefore, you’ll want to learn how to use operators and conditional statements. This Kaggle notebook contains all the code you need to follow along with the examples. Run cells
Aggregating metrics: Using “GROUP BY”
Earlier, we’ve discussed that your database stores data at its most granular level. This means that every order creates one row in your orders table. Likewise, every click a customer makes creates one row in your clicks table. And every review you receive creates one row in your product reviews table. When this builds up
Filtering the data you want: The “WHERE” clause
Why is SQL necessary and useful? In essence, we have huge amounts of data and SQL enables us to extract what we need for specific business insights. So, filtering with the “WHERE” clause is a crucial part of using SQL. This Kaggle notebook contains all the code for following along with the examples. Run cells
Your first SQL joins: reading the “FROM” clause
You’ve got a first query from your company, and you’re ready to start making sense of it. First of all, you’ll want to look at the FROM clause, which tells you where your data is coming from. Then you’ll find that you are looking at your first SQL joins. But most SQL books aren’t going
Using a beginner SQL book efficiently
Perhaps you have started your SQL journey by trying to read a beginner SQL book from start to end. In fact, if you feel that this is slow and frustrating, you’re not alone. Similarly, I worked for 7 years as a business analyst without finishing any beginner SQL books! Instead, you will learn faster by
Learning SQL Mid-Career: Why Survival SQL?
In your industry, you’ve been around the block, and then some. The value that your business acumen, experience, and domain expertise bring is immense. Yet, businesses are digitizing with increasing speed, especially since e-commerce is important for survival in a post-COVID-19 world. Therefore, you’ve probably thought about learning SQL – but as a busy mid-career