Advanced SQL

Curriculum

1. 01Readable SQL and NULL TrapsWrite queries other analysts can check, and avoid the NULL, integer-division and NOT IN traps that silently give wrong answers.20 min
2. 02Date LogicTruncate dates to weeks and months, measure gaps in days, report "as of" a date, and fill in the months with no activity using a calendar.20 min
3. 03Data Quality ChecksProfile a table before you trust it, test keys, relationships and business rules, and investigate suspicious rows instead of deleting them.20 min
4. 04Window Functions in DepthControl exactly which rows a window function sees with frames, and use moving averages, shares of a total, LAG, LEAD and FIRST_VALUE without the common traps.25 min
5. 05Top N per Group and DeduplicationReturn the top rows within every group, the latest record per entity, and one clean row out of several, choosing deliberately how ties are handled.20 min
6. 06Cohorts and RetentionGroup customers by when they started, track how many keep coming back, and avoid the traps of left-censored data and part periods.20 min
7. 07Pivots and Period ComparisonsTurn rows into columns with conditional aggregation, compare periods like for like, and calculate growth without dividing by zero.20 min
8. 08Advanced JoinsJoin a table to itself, test for existence with semi- and anti-joins, walk a hierarchy with a recursive CTE, join on ranges, and build complete grids with CROSS JOIN.20 min
9. 09Query PerformanceRead a query plan, understand what an index does, and write queries a database can run quickly on millions of rows.25 min
10. 10Business Analysis PatternsBuild three analyses finance and sales ask for again and again (receivables aging, Pareto concentration and RFM segmentation) by combining everything in this course.25 min
11. 11Final ProjectPlan your final project, a commercial health check of Harbourline Freight for its leadership team, and warm up with two of its queries.20 min
12. Final assessmentPass it (60% or more) to earn the Advanced SQL course completion badge.