lesson
Scalar and Set Subqueries
Use nested queries when scalar or one-column set shape is clearest. This public lesson explains the mental model, syntax pattern, result grain, mistakes, and next practice step for M21.
Practice this lesson
Answer first mental model
Use nested queries when scalar or one-column set shape is clearest.
Mental model: name the intended output grain for Scalar and Set Subqueries before writing SQL so later checks can distinguish a correct query from a coincidentally matching result.
Learning objectives and prerequisites
Learning objectives are stated as observable behaviors, and prerequisite links are public pages rather than hidden app state.
Plan about 20 minutes for the public read, worked example, prediction, and first app attempt; delayed review can add more practice later.
- Objective: Use nested queries when scalar or one-column set shape is clearest.
- Skill focus: subqueries
- Prerequisite: review Fan-Out, Pre-Aggregation, Self Joins, and Non-Equi Joins before this lesson.
Original worked query and result grain
The worked query is a compact SQL Trail example that can be read without loading the interactive engine.
Input grain: one staged analytical row per entity/period.
Output grain: one analytical result row with explicit ordering and frame behavior.
SELECT customer_id, order_id, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at, order_id) AS order_rank FROM orders ORDER BY customer_id, order_rank;| Example output | Meaning |
|---|---|
| C001 | O1001 | 1 | first order for the customer |
| C001 | O1005 | 2 | next order after deterministic tie handling |
Syntax pattern
Use the syntax pattern as a shape, not as a memorized answer. Replace table, column, condition, grouping, and ordering names according to the stated grain.
WITH staged AS (SELECT ... ) SELECT ... FROM staged;| Input grain | Output grain | Validation focus |
|---|---|---|
| one staged analytical row per entity/period | one analytical result row with explicit ordering and frame behavior | Result comparison, ordering, duplicates, nulls, and edge cases |
Common mistakes and why they fail
A common mistake is matching the visible rows while ignoring ordering, duplicate policy, null behavior, tie behavior, or the stated result grain.
Why it fails: Scalar and Set Subqueries checks meaning across deterministic variants, so a query that only copies the visible rows can break when row counts, labels, nulls, or ties change.
PostgreSQL dialect notes stay explicit when syntax, date handling, transaction behavior, or comparison semantics matter.
Lightweight public prediction
Before opening the app, predict the output grain, the first column, and one edge case that could change the answer.
This is a public reading prompt only; it does not load PGlite, learner history, drafts, or browser-only state.
- Prediction: name one row that should appear or one row that should be excluded.
- Check: explain whether nulls, duplicates, ties, or missing relationships affect the result.
- Transfer: say what would change on a second dataset with different labels and counts.
Practice and related resources
Move to the app when you want the editor, local SQL worker, variant checks, hints, and Solution Studio comparison.