Minimum Viable SQL Patterns
I’m obsessed with patterns.
They help you build on the combined experience of practitioners and codify best practices that have been battle-tested in the real world. Every pattern deals with a specific, recurring problem in the domain and provides proven solutions.
When experts work on a particular problem, it’s unusual for them to tackle it by creating new solutions. They often recall similar problems they (or others) have already solved and reuse the essence of the solution. That essence is codified as a pattern.
Who am I
I’ve been writing SQL for ~15 years. I’ve seen and written hundreds of thousands of lines of code. Over time I noticed a set of patterns and best practices I always come back to when writing queries. These patterns made my code more efficient, easier to understand and a breeze to maintain.
Why did I write this book
I have a background in computer science. As part of the curriculum we learn how to make our code more efficient, more readable and easy to debug. As I started to write SQL, I applied many of these lessons to my own code. When reviewing other people’s code I would often spot the same mistakes.
Their queries were long, complex and slow. I would often have rewrite them just so I could understand what they were doing. I thought this was a known problem. So when people asked me how they could learn to do what I did, I looked around for a book or course that taught these patterns but couldn’t find one. I decided to write it myself and the result is this book.
Is this book for you?
This book is for anyone who is familiar with SQL and wants to take their skills to the next level. I don't cover any of the basic syntax here so make sure you have that down pat. I expect you to already know how to join tables and do basic filtering and aggregation.
If you find that your SQL code is often inefficient and slow and you want to make it faster, this book is for you!
If you find that your SQL code is long, messy and hard to understand and you want to make it cleaner, this book is for you!
If you find that your SQL code breaks easily when data changes and you want to make it more resilient, this book is for you!
What you'll learn in this book
I'm a huge fan of project-based learning. You can learn anything if you can come up with an interesting project to use it thing in. I used a project when I taught myself data science.
That's why for this book I designed an interesting and useful data project to organize it around. I explain each pattern as I walk you through the project. This will ensure that you learn the material better and remember it the next time you need to apply it.
Contents
- Introduction
- Chapter 1: Introducing the Project
- Understanding the Data Model
- Chapter 2: Core Concepts
- Granularity
- Granularity Manipulation
- Granularity Multiplication
- Chapter 3: Query Decomposition
- Common Table Expressions (CTEs)
- Decomposing Queries with CTEs
- Chapter 4: Query Maintainability
- Reusability Principle
- DRY Principle
- Chapter 5: Query Performance
- Reducing Rows
- Reducing Columns
- Delaying Ordering
- Bounded Time Windows
- Chapter 6: Query Robustness
- Dealing with Formatting Issues
- Chapter 7: Finishing the Project