Learn SQL fundamentals using DuckDB and Python. A practical series for Python developers who want to level up their data skills.
SQL is the language of data — and if you work in Python, learning it will make you better at nearly everything you already do. This series walks you through SQL from first principles using DuckDB, a modern, local database that runs inside Python with no server setup required. No credentials, no Docker, no configuration. Just Python and data.
Each post builds on the last. By the end you’ll be writing queries, filtering and aggregating data, joining related tables, and using advanced features like CTEs and subqueries — all explained from a Python developer’s perspective.
Window Functions: The Feature Python Developers Miss Most
This week, we are going to focus on window functions, which are very powerful data manipulation tools. It’s something that you can do in Python, but in SQL, it is super simple and very powerful. We’ll continue using the same orders and customers tables from previous posts in our DuckDB sample database.
Before I continue, I have a confession to make. While I have used window functions, I haven’t used them nearly as much as I should have. I only discovered them in the past few years. Prior to that, I used some other SQL tricks for these types of queries, but this method is much cleaner.
...
NULL: The Value That Isn't
This post is about nothing, or rather, it’s about the unknown. By now, you’ve bumped into NULL several times. Let’s finally make sense of it.
The SQL NULL is sort of like None in Python. After all, they both represent the lack of a value, right?
value = None if value == None: # True (though `is None` is preferred) print("It's None") print(None == None) # True In Python, None is a thing you can compare. Let’s see how NULL compares.
...
CTEs: Making Your SQL Readable
We learned last week about subqueries, which are like helper functions for your SQL code. They can bring back temporary values used in larger calculations or find additional data points from an id.
While subqueries are very powerful, they do add complexity to your code. This complexity adds to the cognitive load when trying to read and understand your code.
I’ve been the person who has to dust off an old piece of SQL code that has been running in production for years and just recently started returning invalid rows or experiencing massive latency.
...
Subqueries: When SQL Needs Helper Functions
Last week, we talked about the superpower of relational databases, the ability to join tables to make data storage more efficient. In fact, we have covered much of the syntax that you would use on a daily basis already. But SQL’s simplicity hides surprising flexibility. You can model data in many ways, and you can often get the same results with different syntax.
The art of SQL is optimizing your queries so that they run well. This comes with experience, so I encourage you to start playing around with the queries and data we are working with. We will see some of this flexibility with today’s topic: subqueries.
...
JOINs Explained for Python Developers
So far in this series we have covered all the core SQL clauses: SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY. We can do quite a bit with those tools, but we have been working with a single table. SQL is the language of relational databases, and it is time to talk about the relational part.
JOINs connect related tables. It’s like looking up values in a Python dictionary or merging pandas DataFrames, except that the database handles the matching. Today we are going to see how this works, but first we need a little setup.
...
HAVING: Filtering Grouped Results
When I first encountered HAVING, I thought, “Why do we need this? It’s just like WHERE.”
Then I tried filtering on COUNT() and hit a strange error. That’s when it clicked: HAVING filters after grouping, not before. It’s what you need when WHERE won’t work because the thing you want to filter on doesn’t exist until after GROUP BY runs.
Let’s start with a simple query of customer count by city. But there are a lot of cities and we only care about those with more than ten customers.
...
GROUP BY: Aggregating Your Data
Last week, we learned to use WHERE to efficiently return only the rows that we want from a database. But what if you want to summarize the data more efficiently?
It turns out that you can have the database do the summarization for you with the GROUP BY keyword.
Like Python’s collections.Counter or pandas groupby(), SQL’s GROUP BY lets you summarize data by category. It allows you to count, sum, and average across groups.
...
WHERE: Filtering Your Data
We have come a long way in the past couple of months, working through the core SQL keywords. So far, we can SELECT columns, specify FROM where our data lives, and ORDER BY to sort results.
That is quite a lot, and today we are going to unlock the real power of SQL by giving you the ability to filter your results before they are returned from the server.
...
ORDER BY: Sorting Your Results
We now have a firm grasp on how to use SELECT: Choosing Your Columns and FROM: Where Your Data Lives to tell the database where to find data and how to format the columns when it returns it. With this knowledge, we can pull back all of the data from a table in a database.
There is still a problem with the data that we receive from a query. It can come back in any order. It may return in the same order 9 times out of 10, but there is no guarantee that it will come back in the same order next time. This happens because database engines optimize execution plans based on factors like data volume, indexes, and available memory, and those optimizations can change between queries.
...
SELECT: Choosing Your Columns
You have written SELECT * many times by now. It works, but it’s a bit like asking for everything in the fridge when you just want milk. This week, we will look at the SELECT clause and see that it does more than just pick columns. It transforms your output.
Previously, we looked closely at the FROM clause, which tells the database where the query will find the data. The SELECT clause defines which columns will be returned, and you can reshape data on the way out.
...
