</>Code Temper

Intermediate · Free · In your browser

Learn SQL Intermediate

Go beyond the basics with a realistic shop database. Master LEFT and self-joins without double counting, conditional and time-based aggregation, running totals, LAG and moving averages, recursive queries, JSON columns, upserts and data updates, views and indexes, and build the query behind a monthly sales dashboard.

20 lessons6 modules3.9 hours of practice4 challenges
Start the SQL Intermediate course →

No installation. Your code runs right in the browser. Finish SQL Foundations first, or be comfortable with SELECT, WHERE, GROUP BY, and JOIN.

What you will be able to do

Course outline

Module 1

Joins beyond the basics

LEFT joins, self-joins, multi-table joins, and avoiding double counting.

  1. Keep the rows that have no matchLEFT JOIN & COALESCE · 10 min
  2. Join a table to itselfSelf-joins & aliases · 9 min
  3. Follow a chain of tablesMulti-table joins & revenue · 10 min
  4. Avoid double countingJoin fan-out & COUNT(DISTINCT) · 11 min
Module 2

Aggregation that answers real questions

Conditional counts, monthly reports, shares of the total, and text lists.

  1. Count several things at onceConditional aggregation & FILTER · 10 min
  2. Group by timeDates, strftime & monthly reports · 10 min
  3. Share of the wholeWindow aggregates over the entire result · 10 min
  4. Combine rows into textGROUP_CONCAT with ordering · 9 min
Module 3

Window functions in depth

Running totals, LAG and LEAD, moving averages, and a customer value report.

  1. Running totalsSUM() OVER (ORDER BY …) · 10 min
  2. Compare with the previous rowLAG, LEAD & date gaps · 10 min
  3. Smooth noisy numbersWindow frames & moving averages · 11 min
  4. Project: a customer value reportFoundation project · joins, aggregates & ranking · 20 min
Module 4

Hierarchies, series and JSON

Recursive queries, complete timelines, and JSON columns.

  1. Walk a hierarchyRecursive CTEs · 12 min
  2. Fill the gaps in a timelineGenerated series & LEFT JOIN · 11 min
  3. Query JSON inside a columnjson_extract & the ->> operator · 10 min
Module 5

Change data safely

Upserts, updates from other tables, and views.

  1. Insert or update in one statementUPSERT with ON CONFLICT · 10 min
  2. Update rows from other rowsUPDATE … FROM & aggregated changes · 11 min
  3. Save a query as a viewCREATE VIEW & reusable logic · 10 min
Module 6

Performance and the capstone

Indexes and query plans, and a monthly sales dashboard.

  1. Make lookups fastIndexes & EXPLAIN QUERY PLAN · 11 min
  2. Capstone: a monthly sales dashboardCapstone · CTEs, windows, dates & growth · 28 min

Ready for your first line?

Every lesson is short, hands-on, and checked automatically.

Start learning SQL Intermediate →