SQL for Analysis
Go beyond the basics with subqueries, CTEs, window functions and the analytics patterns analysts use every day, practised in your browser.
Leads to: Data Analyst, Data Scientist, Systems Analyst, Database Administrator, Back-End Developer, Data Engineer
Before you start
This course builds on SQL Fundamentals. Take it first if you are new to the topic.
About this course
This intermediate course takes you from writing single SELECT statements to answering real business questions in SQL, the way data analysts, data scientists, systems analysts and database administrators do at work. It builds on SQL Fundamentals and uses SQLite in your browser, so there is nothing to install. The first module covers advanced queries: subqueries (including correlated subqueries and EXISTS), common table expressions that turn nested queries into readable steps, and set operations such as UNION ALL and EXCEPT. The second module teaches window functions: ranking rows within groups with ROW_NUMBER, RANK and DENSE_RANK, running totals and moving averages with window frames, and period-over-period change with LAG and LEAD. The third module applies these tools to the analytics patterns employers ask about most: cohort retention tables, conversion funnels and data quality checks. The last module is about SQL other people can trust: a practical style guide, how indexes and query plans affect speed, and a capstone that answers a business question end to end and communicates the result. Every lesson except one ends with a query you write and check against realistic data, and lessons point out where PostgreSQL, MySQL, SQL Server and cloud warehouses differ.
Course content
Module 1
Advanced Queries
3 lessons · 1h 22mAnswer multi-step questions with subqueries, organise them into readable CTEs, and combine result sets with UNION, INTERSECT and EXCEPT.
- 33mSubqueriesReading
Use a query inside a query to filter by computed values, lists from other tables and per-group averages.
- 28mCTEsReading
Rewrite nested queries as named, top-to-bottom steps with WITH, and chain several steps into one readable query.
- 21mUNION and set operationsReading
Stack and compare result sets with UNION, UNION ALL, INTERSECT and EXCEPT, and know when each one is right.
Module 2
Window Functions
3 lessons · 1h 57mRank rows within groups, compute running totals and moving averages, and measure period-over-period change, all without losing row detail.
- 49mROW_NUMBER and RANKReading
Rank rows within groups with ROW_NUMBER, RANK and DENSE_RANK, and use the top-N-per-group pattern.
- 35mRunning totals and moving averagesReading
Turn SUM and AVG into window functions to compute running totals, shares of total and moving averages with window frames.
- 33mLAG and LEADReading
Compare each row with the previous or next one to compute month-over-month change and gaps between events.
Module 3
Analytics Patterns
3 lessons · 1h 52mBuild the queries analysts are asked for most: cohort retention tables, conversion funnels and data quality checks.
- 47mCohort and retention queriesReading
Group users by when they started and measure what share stay active in later months with a retention table.
- 33mFunnelsReading
Count distinct users through each step of a conversion funnel and find where people drop out.
- 32mData quality checksReading
Write queries that catch missing values, duplicates, invalid values and orphan rows before they reach a report.
Module 4
Readable and Fast SQL
3 lessons · 2h 7mWrite SQL others can read and trust, understand how indexes and query plans affect speed, and finish with an end-to-end analysis project.
- 24mSQL style and commentsReading
Refactor queries so others can read, review and trust them, with clear layout, aliases, CTEs and comments.
- 28mIndexes and EXPLAINReading
Understand what an index is, when the database can use one, and how to read a query plan to tell a scan from a search.
- 1h 15mAnalysis project in SQLReading
Answer a business question end to end: define the terms, explore the data, build the query in steps and communicate the result.