Advanced SQL
Real world data is messy, confusing, and big. But most SQL courses teach with data that is artificially clean, simple, and small. This is not one of those courses. Over these four weeks, you’ll learn the advanced SQL skills necessary to dive into real datasets with confidence. We’ll clean up messy and nested data, learn about EAV schemas and pivots, and dive deep into advanced window functions. We’ll also cover performance, advanced joins, and other complex SQL patterns to optimize queries. You will walk away ready to be unleashed on any dataset, from your company’s production DB (a read replica, of course) to a public database of trees in your neighborhood.
Izzy Miller
Advocate at Hex, former Google and Looker
Izzy is a developer advocate and data nerd. He currently leads community at Hex, a collaborative data workspace. He previously built both the community and DevRel teams at Looker, where his guilty pleasure was not using Looker and writing SQL by hand. Before that, he studied Botany, Biology, and Environmental Studies, and outside of work still spends most of his time thinking about things that grow, crawl, and slither around.
- String and date functions for data cleaning
- Data types in SQL
- How to work with EAV schemas
- Pivoting & unpivoting
- Deduplicating & removing bad data
- Window functions explained
- Running totals, subtotals, rolling averages & ranks
- Cohort analysis
- Daily active / monthly active users
- Date spines & fanouts
- CTEs and subqueries
- SQL performance explained
- Tuning queries & joins
- Fanout
- Some complex special patterns: Outer join on false, summarizing data with defined start/end times, etc.
- Complex joins
- Unnesting and flattening
- Nested object querying
- Performance recap
- UDFs
Junior Analytics Engineer. Some experience with SQL, and want to get more comfortable
Data Scientist / SWE who wants to get more comfortable with SQL
Basic SQL proficiency; You are comfortable writing simple SQL queries but are not as comfortable with complicated joins, window functions, etc.
