SQL for Data Analysis: Advanced Techniques for Transforming Data into Insights

Part of my role involved crunching inventory data in spreadsheets, and thanks to early internet scale, the data sets were sometimes tens of thousands of rows. This was “big data” at the time, at least for me. I got in the habit of going for a cup of coffee or for lunch while my computer’s CPU was occupied with running its vlookup magic. One day my manager went on vacation and asked me to tend to the data warehouse he’d built on his laptop using Access. Refreshing the data involved a series of steps: running SQL queries in a portal, loading the resulting csv files into the database, and then refreshing the spreadsheet reports. After the first successful load, I started tinkering, trying to understand how it worked, and pestering the engineers to show me how to modify the SQL queries.

I was hooked, and even when I thought I might change directions with my career, I’ve kept coming back to data. Manipulating data, answering questions, helping my colleagues work better and smarter, and learning about businesses and the world through sets of data have never stopped feeling fun and exciting.

When I started working with SQL, there weren’t many learning resources. I got a book on basic syntax, read it in a night, and from there mostly learned through trial and error. Back in the days when I was learning, I queried production databases directly and brought the website down more than once with my overly ambitious (or more likely just poorly written) SQL. Fortunately my skills improved, and over the years I learned to work forward from the data in tables, and backward from the output needed, solving technical and logic challenges and puzzles to write queries that returned the right data. I ended up designing and building data warehouses to gather data from different sources and avoid bringing down critical production databases. I’ve learned a lot about when and how to aggregate data before writing the SQL query and when to leave data in a more raw form.

I’ve compared notes with others who got into data around the same time, and it’s clear we mostly learned in the same ad hoc way. The lucky among us had peers with whom to share techniques. Most SQL texts are either introductory and basic (there’s definitely a place for these!) or else aimed at database developers. There are few resources for advanced SQL users who are focused on analysis work. Knowledge tends to be locked up in individuals or small teams. A goal of this book is to change that, giving practitioners a reference for how to solve common analysis problems with SQL, and I hope inspiring new inquiries into data using techniques you might not have seen before.