Advanced SQL

Programme overview

SQL for Data Analysis taught you to get answers out of a database. This course teaches you to get answers you can defend. Back at Harbourline Freight, you'll catch the NULL, integer-division and NOT IN traps that silently give wrong numbers, handle dates and fill missing periods with a calendar, and check a database before you trust it. Then you'll go deeper into window functions (frames, moving averages, LAG and LEAD), return the top rows per group, build cohort and retention tables, compare periods like for like, walk hierarchies with recursive CTEs, read query plans and write queries that stay fast on millions of rows. The course ends with the analyses companies ask for again and again: receivables aging, Pareto concentration and RFM segmentation, and a commercial health check for a board.

Curriculum

1. 01Readable SQL and NULL Traps20 min

   • Readable SQL and NULL trapsWrite queries other analysts can check, and avoid the NULL, integer-division and NOT IN traps that silently give wrong answers.
2. 02Date Logic20 min

   • Date 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.
3. 03Data Quality Checks20 min

   • Data quality checks in SQLProfile a table before you trust it, test keys, relationships and business rules, and investigate suspicious rows instead of deleting them.
4. 04Window Functions in Depth25 min

   • Window 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.
5. 05Top N per Group and Deduplication20 min

   • Top 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.
6. 06Cohorts and Retention20 min

   • Cohorts and retentionGroup customers by when they started, track how many keep coming back, and avoid the traps of left-censored data and part periods.
7. 07Pivots and Period Comparisons20 min

   • Pivots and period comparisonsTurn rows into columns with conditional aggregation, compare periods like for like, and calculate growth without dividing by zero.
8. 08Advanced Joins20 min

   • Advanced 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.
9. 09Query Performance25 min

   • Query performanceRead a query plan, understand what an index does, and write queries a database can run quickly on millions of rows.
10. 10Business Analysis Patterns25 min

    • Business 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.
11. 11Final Project20 min

    • Final project: commercial health checkPlan your final project, a commercial health check of Harbourline Freight for its leadership team, and warm up with two of its queries.

Assessment and certificate

You are assessed with a final assessment (pass mark 60%) and a final project. Your certificate is awarded when you meet those requirements. Paying for the programme does not award it on its own.