Once you're comfortable with SELECT, GROUP BY, and joins, the next step is writing queries that answer harder questions in a single pass. This module starts with subqueries, queries nested inside other queries, and then shows how CTEs make the same logic easier to read, including recursive CTEs.
You'll also use window functions such as ROW_NUMBER, LAG, and LEAD to compare rows without collapsing them, CASE expressions to add if/then logic to a query, views to save and reuse queries, and UNION to combine the results of several queries into one.
With these techniques you'll be ready for the next module, Python for Data Analysis, where you move from querying data to analysing it in code.
A hands-on demo that goes beyond the Big 6 clauses: UNION and UNION ALL, subqueries, LEFT versus INNER JOIN, CTEs and recursive CTEs, date expressions, CAST, COALESCE, ROUND, and the ROW_NUMBER, LAG, and LEAD window functions.
Writing a query inside a query: subqueries in WHERE, SELECT, and FROM, using a subquery to build an IN list, and what correlated subqueries are and why they can be slow.
Adding if/then/else logic to a query with CASE, and saving a query as a view to hide complexity and control what users can see.
Combining several answer sets into one with UNION, the rules for matching columns, and how auto-increment columns generate unique keys.