SQL Quest

Lessons, hints, explained solutions, the in-browser PostgreSQL editor and practice answer checks in your browser are available. Graded assessments, recorded progress from answer checks and new certificates aren't available yet.

Subqueries and Advanced Filtering

Real business questions are rarely one step: “products above their category's average”, “customers who bought in 2024 but not in 2025”, “net revenue after refunds, by channel”, “everyone in the commercial organisation”. This track teaches you to reason in steps and to write those steps clearly. You will learn what each kind of subquery returns (a value, a list or a table) and how it feeds the outer query; how EXISTS and NOT EXISTS answer has-any and has-none questions, and why NOT IN silently returns nothing when a NULL is involved; what correlated subqueries cost; how derived tables and common table expressions turn nested logic into named, testable steps; how recursive CTEs walk category trees, referral chains and management lines; and how UNION, INTERSECT and EXCEPT combine and reconcile whole lists. You practise on Wadi Market's online shop, Rawabi Bank's accounts and transactions, and Masar Holding's org chart, and finish by rewriting working-but-unreadable queries into SQL a colleague can audit.

Start the track

Lessons

  1. Scalar subqueries: one query feeding anotherPut a query inside a query so that one computed value, such as an average or a maximum, drives the outer filter or calculation.18 min · 4 exercisesFree
  2. IN with a subquery: filtering by a listUse a subquery that returns a single column of values as the list on the right of IN, and see why it never duplicates rows the way a join can.18 min · 4 exercises Members
  3. EXISTS: does at least one matching row exist?Test, for each row, whether a related row exists in another table, with a correlated EXISTS subquery.20 min · 4 exercises Members
  4. NOT EXISTS and the NOT IN + NULL trapFind rows with no matching row elsewhere using NOT EXISTS, and see on real data why NOT IN silently returns nothing when its list contains a NULL.22 min · 4 exercises Members
  5. IN versus EXISTS (and when to join)Compare IN, EXISTS and JOIN for membership questions: when they are interchangeable, when they differ, and how PostgreSQL executes each.20 min · 4 exercises Members
  6. Correlated subqueries and what they costCompare each row with its own group (its category, its customer, its product) using a subquery that refers to the outer row, and learn when that is slow.22 min · 4 exercises Members
  7. Derived tables: a subquery in FROMTreat a query's result as a temporary table in FROM to aggregate in two stages and to pre-aggregate before joining.22 min · 4 exercises Members
  8. Common table expressions (WITH)Name intermediate results with WITH … AS (…) so a query reads top to bottom, and reuse a named step more than once.20 min · 4 exercises Members
  9. Multi-step CTE pipelinesChain CTEs into a pipeline that mirrors a business calculation step by step: base rows, per-entity totals, combination, final metrics.25 min · 4 exercises Members
  10. Recursive CTEs: hierarchies, chains and date spinesWalk trees and chains of unknown depth (category trees, referral chains, management lines) with WITH RECURSIVE, and build date spines with recursion or generate_series.25 min · 4 exercises Members
  11. Set operations: UNION, INTERSECT, EXCEPTStack, intersect and subtract whole query results, and use EXCEPT for reconciliation-style comparisons between two lists.22 min · 4 exercises Members
  12. Choosing a readable query structurePick between nested subqueries, EXISTS, joins, derived tables and CTEs for clarity, and rewrite working but hard-to-read queries into ones a colleague can check.22 min · 3 exercises Members