Skip to content
Varnook
All courses
SQLIntermediate

SQL for Data Analysis

Answer real business questions with SQL: summarise and join data with confidence, handle missing values, and use subqueries, CTEs and window functions on a small UK online shop.

Recommended first: SQL Fundamentals. This course builds on it.

Start courseView syllabus

Free to browse. You sign in at the first lesson so your progress is saved.

What you will be able to do

  • Summarise data by several columns and filter the groups with HAVING
  • Choose the right join, including finding rows with no match
  • Handle NULLs safely with IS NULL and COALESCE
  • Break long queries into readable steps with CTEs
  • Rank rows and build running totals with window functions

Syllabus

9 modules and 24 lessons. Each lesson is a short read followed by an exercise with tests.

Module 1

Grouping and summarising

Summarise data at more than one level, filter groups with HAVING and turn pence into pounds.3 lessons, about 36 min
  1. 1Grouping by more than one columnMake one summary row for every combination of two columns.12 min
  2. 2Filtering groups with HAVINGKeep only the groups whose totals meet a condition.12 min
  3. 3From pence to poundsConvert integer pence into pounds and round the result.12 min
Module 2

Joins in depth

Choose between inner and left joins, find rows with no match and join a table to itself.3 lessons, about 42 min
  1. 4Inner and left joinsKeep every row from one table, even when it has no match.14 min
  2. 5Finding missing matchesUse a left join to find rows that have no partner in another table.14 min
  3. 6Joining a table to itselfUse a self join to connect staff to their managers.14 min
Module 3

Handling NULL

Understand how missing values behave in comparisons and totals, and replace them with sensible defaults.2 lessons, about 26 min
  1. 7NULL in comparisons and countsSee why NULL never equals anything, and how aggregates skip it.14 min
  2. 8Filling gaps with COALESCEReplace NULL with a default value you choose.12 min
Module 4

Conditional logic with CASE

Label rows by rules you define, and count several conditions in a single query.2 lessons, about 26 min
  1. 9Labelling rows with CASETurn raw values into readable categories.12 min
  2. 10Counting with CASEPut CASE inside SUM or COUNT to count several things at once.14 min
Module 5

Subqueries

Use the result of one query inside another, from single values to row-by-row checks.4 lessons, about 55 min
  1. 11Comparing with a single valueUse a subquery that returns one value, such as an average.12 min
  2. 12Matching a list with INFilter rows against a list of values produced by another query.14 min
  3. 13Correlated subqueriesWrite a subquery that refers to the current row of the outer query.15 min
  4. 14Checking with EXISTSAsk whether any matching row exists, and use NOT EXISTS safely.14 min
Module 6

Common table expressions

Break long queries into named, readable steps with WITH.2 lessons, about 30 min
  1. 15Naming a step with WITHGive an intermediate result a name and query it like a table.14 min
  2. 16Chaining CTEsBuild a query from several named steps, each using the one before.16 min
Module 7

Window functions

Rank rows, total within groups, keep running totals and compare each row with the one before.4 lessons, about 1 hour
  1. 17Ranking rowsNumber and rank rows with ROW_NUMBER, RANK and DENSE_RANK.15 min
  2. 18Windows within groupsUse PARTITION BY to restart a calculation for each group.15 min
  3. 19Running totalsAdd up values row by row with SUM OVER and ORDER BY.15 min
  4. 20Comparing with the previous rowUse LAG to look back one row and measure the change.15 min
Module 8

Dates and text

Group by month, do date arithmetic and reshape text with SQLite functions.2 lessons, about 29 min
  1. 21Working with datesFormat, shift and group dates with strftime and date.15 min
  2. 22Working with textJoin, cut and change the case of text.14 min
Module 9

Project: a sales review

Answer real business questions about the shop by combining everything in the course.2 lessons, about 45 min
  1. 23Revenue by categoryFind out which kind of product brings in the most money.20 min
  2. 24A customer reportCombine CTEs, joins, NULL handling and CASE into one report.25 min

After this course

Where to go next

This course builds on what you learn in SQL for Data Analysis.

  • SQLIntermediate

    SQL Project: Shop Reporting

    Build a shop report from real relational data: select relevant orders, calculate line revenue and retain customers who have not ordered.

    Recommended first: SQL for Data Analysis

    • 3 lessons
    • About 1 hour