How-toData QualityDoneStarted
Find and remove duplicate rows in SQL
Count the copies, number them with ROW_NUMBER, delete all but one per group, and add a unique index so they cannot come back.
Learning pathIntermediate
SQL for data work
From removing duplicates and window functions to modelling for reporting, cohort analysis, indexes and query plans: the SQL a working analyst or engineer uses every week, each lesson with queries that were run.
- 8lessons
- ~90 minin total
- Intermediatelevel
- 7topics
Outcomes
What you will be able to do
Explain what an index is and how it speeds up a lookup
Say what each of the four ACID properties guarantees
Choose the column order of a composite index
Use a transaction so a multi-step change succeeds or fails as one
Recognise queries that cannot use an index
Know what isolation levels trade off
Covered
Topics and formats in this path
- Relational
- Data Quality
- Python & SQL
- Data Modeling
- Cohort Analysis
- Indexing
- Query Optimization
- How-to
- Reference
- Tutorial
- Explainer
- Troubleshooting
Learning plan
8 lessons, in order
Each lesson is short enough for one sitting and ends by pointing to the next.
- How-to
ReferencePython & SQLDoneStarted
SQL window functions: a cheat sheet with worked results
The window functions you will use every week (ranking, running totals, previous row, share of total), each with a short query and the result it returns.
TutorialData ModelingDoneStarted
Design a star schema for sales reporting
Turn a flat sales export into one fact table and three dimensions, decide the grain, and answer questions with a single join pattern.
TutorialCohort AnalysisDoneStarted
Build a cohort retention table in SQL
Group users by the month they first appeared, count how many return in each later month, and read the result as a retention table.
ExplainerIndexingDoneStarted
Database indexes explained: why a lookup stops scanning the whole table
What an index is, what it costs, how to read the difference between a scan and a search, and the four reasons a query ignores the index you made.
- Explain what an index is and how it speeds up a lookup
- Choose the column order of a composite index
How-toQuery OptimizationDoneStarted
Read a query plan and find the slow part
Ask the database how it will run a query, read the plan from the inside out, and use it to decide whether an index, a rewrite or nothing at all is the fix.
ExplainerRelationalDoneStarted
ACID and transactions in plain language
What atomicity, consistency, isolation and durability promise, shown with a money transfer that fails half-way and leaves nothing behind.
- Say what each of the four ACID properties guarantees
- Use a transaction so a multi-step change succeeds or fails as one
- Troubleshooting
RelationalDoneStarted
SQLite: 'database is locked' and how to fix it
Why SQLite refuses a second writer, how to reproduce the error in six lines, and the changes (shorter transactions, a timeout, WAL mode) that stop it.
Questions
Before you start
Who is this for?
From removing duplicates and window functions to modelling for reporting, cohort analysis, indexes and query plans: the SQL a working analyst or engineer uses every week, each lesson with queries that were run. It is written for intermediate readers.
How long does it take?
About 90 minutes of reading and doing across 8 lessons. Each lesson shows its own time, so you can do one at a sitting.
Do I need an account?
No. Everything here is free to read. An account is only needed to ask or answer a question in the community.
What if I get stuck on a lesson?
Ask in the community: say which lesson, what you tried and what happened. Every lesson also shows when it was last verified.
Ready when you are
Open the first lesson now: nothing to install, nothing to sign up for.