M00
Workspace Orientation and First Run
SQL Trail treats every query as an evidence trail: identify source grain, transform rows deliberately, then compare output to a shared contract.
PostgreSQL-compatible local syntax; statement policy: select-only
Syntax and examples
Syntax template
SELECT <requested_columns>
FROM <source_table>
WHERE <source_population_filter>
GROUP BY <result_grain_columns>
ORDER BY <deterministic_tie_breakers>;
Minimal example
SELECT product_id, product_name FROM products ORDER BY product_id;
Realistic example
SELECT product_id, product_name FROM products ORDER BY product_id;
Notes and mistakes
Null, tie, and boundary notes
- Preserve NULL, empty string, zero, and false as distinct values unless the contract says to display a fallback.
- Use every ordering rule in the contract and end tied business metrics with deterministic secondary keys when needed.
- Preserve duplicate facts unless the prompt explicitly asks for distinct tuples or set semantics.
Common mistakes
- Run is not completion: The result grid appears, but the activity still needs a Check result. Repair: Use Run to explore visible rows, then use Check when the answer should be graded against visible and hidden variants.
- Visible rows are not the whole database: A copied visible result passes Run but fails when hidden variants add or change rows. Repair: Write the query rule, not the displayed rows, and let hidden variants test the rule.
- Schema panel is not the result: Column names in the schema are mistaken for rows returned by the query. Repair: Use the schema panel to choose tables and columns, then use Run to inspect actual result rows.
- Syntax errors do not destroy drafts: After an error, the learner expects their SQL to be erased or the attempt to be unrecoverable. Repair: Keep the draft, fix the highlighted SQL, and rerun locally before using Check again.
M01
Tables, Rows, Columns, Keys, and Grain
SQL Trail treats every query as an evidence trail: identify source grain, transform rows deliberately, then compare output to a shared contract.
PostgreSQL-compatible local syntax; statement policy: select-only
Syntax and examples
Syntax template
SELECT <requested_columns>
FROM <source_table>
WHERE <source_population_filter>
GROUP BY <result_grain_columns>
ORDER BY <deterministic_tie_breakers>;
Minimal example
SELECT table_name, CASE table_name WHEN 'customers' THEN 'one row per customer account' WHEN 'orders' THEN 'one row per order header' WHEN 'products' THEN 'one row per sellable product' END AS row_grain FROM information_schema.tables WHERE table_schema = 'public' AND table_name IN ('customers', 'orders', 'products') ORDER BY table_name;
Realistic example
SELECT table_name, CASE table_name WHEN 'customers' THEN 'one row per customer account' WHEN 'orders' THEN 'one row per order header' WHEN 'products' THEN 'one row per sellable product' END AS row_grain FROM information_schema.tables WHERE table_schema = 'public' AND table_name IN ('customers', 'orders', 'products') ORDER BY table_name;
Notes and mistakes
Null, tie, and boundary notes
- Preserve NULL, empty string, zero, and false as distinct values unless the contract says to display a fallback.
- Use every ordering rule in the contract and end tied business metrics with deterministic secondary keys when needed.
- Preserve duplicate facts unless the prompt explicitly asks for distinct tuples or set semantics.
Common mistakes
- Not every table is customer-grained: Orders, products, and relationship tables are described as if each row were a customer. Repair: State the grain table by table before choosing columns or joins.
- Names are labels, not keys: A repeated human-readable name is used as if it uniquely identified a row. Repair: Prefer declared identifiers such as customer_id or composite primary keys over labels.
- Physical row order is not evidence: The first visible row is treated as meaningful without an ORDER BY or key rule. Repair: Use explicit keys and deterministic ordering when the output order matters.
- Foreign keys can repeat or be optional: A child-table foreign key is assumed to be unique or always present. Repair: Check cardinality and nullability separately from whether a column references a parent key.
PostgreSQL-specific label SQL Trail treats every query as an evidence trail: identify source grain, transform rows deliberately, then compare output to a shared contract.
PostgreSQL-compatible local syntax; statement policy: select-only
Syntax and examples
Syntax template
SELECT <requested_columns>
FROM <source_table>
WHERE <source_population_filter>
GROUP BY <result_grain_columns>
ORDER BY <deterministic_tie_breakers>;
Minimal example
SELECT * FROM products ORDER BY product_id;
Realistic example
SELECT * FROM products ORDER BY product_id;
Notes and mistakes
Null, tie, and boundary notes
- Preserve NULL, empty string, zero, and false as distinct values unless the contract says to display a fallback.
- Use every ordering rule in the contract and end tied business metrics with deterministic secondary keys when needed.
- Preserve duplicate facts unless the prompt explicitly asks for distinct tuples or set semantics.
Common mistakes
- A table name is not a column: The query puts a table identifier in the SELECT list or a column identifier after FROM. Repair: Select columns after SELECT and name exactly one source table after FROM.
- Star means this table, not every table: SELECT star is expected to return columns from unrelated tables. Repair: Read star as every column from the FROM table or joined source only.
- Exact identifiers matter: Singular names, friendly labels, or guessed spellings produce unknown identifier errors. Repair: Copy table and column identifiers from the schema explorer exactly.
- SQL does not guess: A query is expected to silently choose the closest matching table or column. Repair: Use the database error and schema suggestions to repair the identifier explicitly.
M03
Multiple Columns, Column Order, and Aliases
SQL Trail treats every query as an evidence trail: identify source grain, transform rows deliberately, then compare output to a shared contract.
PostgreSQL-compatible local syntax; statement policy: select-only
Syntax and examples
Syntax template
SELECT <requested_columns>
FROM <source_table>
WHERE <source_population_filter>
GROUP BY <result_grain_columns>
ORDER BY <deterministic_tie_breakers>;
Minimal example
SELECT customer_id, customer_name, city FROM customers ORDER BY customer_id;
Realistic example
SELECT customer_id, customer_name, city FROM customers ORDER BY customer_id;
Notes and mistakes
Null, tie, and boundary notes
- Preserve NULL, empty string, zero, and false as distinct values unless the contract says to display a fallback.
- Use every ordering rule in the contract and end tied business metrics with deterministic secondary keys when needed.
- Preserve duplicate facts unless the prompt explicitly asks for distinct tuples or set semantics.
Common mistakes
- Aliases do not rename the source schema: The learner tries to reference a result alias as if it were a stored column. Repair: Use source column names in expressions and reserve aliases for output headers or supported ORDER BY positions.
- Output order follows SELECT order: The result columns are expected to follow physical table order instead of the requested contract. Repair: Arrange expressions in the SELECT list exactly as the output contract states.
- Commas separate result expressions: Two adjacent column names are parsed as an alias or produce a syntax error. Repair: Place commas between every projected expression before adding aliases.
- Aliases have limited scope: An alias is reused inside another expression in the same SELECT list. Repair: Repeat the source expression or stage the query later when alias reuse is needed.
PostgreSQL-specific label SQL Trail treats every query as an evidence trail: identify source grain, transform rows deliberately, then compare output to a shared contract.
PostgreSQL-compatible local syntax; statement policy: select-only
Syntax and examples
Syntax template
SELECT <requested_columns>
FROM <source_table>
WHERE <source_population_filter>
GROUP BY <result_grain_columns>
ORDER BY <deterministic_tie_breakers>;
Minimal example
SELECT product_id, (price + 1)::numeric(10,2) AS price_plus_one FROM products WHERE product_id = 1;
Realistic example
SELECT product_id, (price + 1)::numeric(10,2) AS price_plus_one FROM products WHERE product_id = 1;
Notes and mistakes
Null, tie, and boundary notes
- Preserve NULL, empty string, zero, and false as distinct values unless the contract says to display a fallback.
- Use every ordering rule in the contract and end tied business metrics with deterministic secondary keys when needed.
- Preserve duplicate facts unless the prompt explicitly asks for distinct tuples or set semantics.
Common mistakes
- Row-level calculations do not need GROUP BY: A calculation is aggregated or grouped even though each input row should stay visible. Repair: Compute expressions directly in the SELECT list when the output grain remains one row per input row.
- Percentages need decimal form: A 10 percent adjustment is written as 10 instead of 0.10 or 1.10. Repair: Convert percentages to decimal multipliers before multiplying.
- Operator precedence is not left-to-right: Multiplication applies before addition and produces a plausible but wrong number. Repair: Use parentheses to make the intended subtotal or percentage scope explicit.
- Derived fields need aliases: A computed output column has an unstable or unreadable expression header. Repair: Add a clear snake_case alias for every derived expression in a graded contract.
M05
WHERE Comparisons and Precise Boundaries
SQL Trail treats every query as an evidence trail: identify source grain, transform rows deliberately, then compare output to a shared contract.
PostgreSQL-compatible local syntax; statement policy: select-only
Syntax and examples
Syntax template
SELECT <requested_columns>
FROM <source_table>
WHERE <source_population_filter>
GROUP BY <result_grain_columns>
ORDER BY <deterministic_tie_breakers>;
Minimal example
SELECT product_id, product_name, stock_count FROM products WHERE stock_count > 10 ORDER BY product_id;
Realistic example
SELECT product_id, product_name, stock_count FROM products WHERE stock_count > 10 ORDER BY product_id;
Notes and mistakes
Null, tie, and boundary notes
- Preserve NULL, empty string, zero, and false as distinct values unless the contract says to display a fallback.
- Use every ordering rule in the contract and end tied business metrics with deterministic secondary keys when needed.
- Use the stated inclusive or exclusive boundary, and prefer half-open time periods when requested.
- Preserve duplicate facts unless the prompt explicitly asks for distinct tuples or set semantics.
Common mistakes
- After does not always mean inclusive: A boundary row is included or excluded without matching the exact wording. Repair: Translate on or after to >=, before to <, and half-open windows to >= lower and < upper.
- Text literals need quotes: A word such as completed is parsed as a column instead of a value. Repair: Wrap text values in single quotes and leave numeric values unquoted.
- Filter the stored source column: The predicate is written against a similar-looking column that does not store the requested value. Repair: Use the schema to find where the value lives before choosing the WHERE column.
- One sample row does not prove a boundary: Visible data passes while hidden rows exactly on a lower or upper boundary fail. Repair: Check the contract wording against lower-bound, upper-bound, and just-outside cases.
M06
AND, OR, NOT, and Parentheses
SQL Trail treats every query as an evidence trail: identify source grain, transform rows deliberately, then compare output to a shared contract.
PostgreSQL-compatible local syntax; statement policy: select-only
Syntax and examples
Syntax template
SELECT <requested_columns>
FROM <source_table>
WHERE <source_population_filter>
GROUP BY <result_grain_columns>
ORDER BY <deterministic_tie_breakers>;
Minimal example
SELECT product_id, product_name, (price >= 10 AND NOT discontinued) AS passes_filter FROM products ORDER BY product_id;
Realistic example
SELECT product_id, product_name, (price >= 10 AND NOT discontinued) AS passes_filter FROM products ORDER BY product_id;
Notes and mistakes
Null, tie, and boundary notes
- Preserve NULL, empty string, zero, and false as distinct values unless the contract says to display a fallback.
- Use every ordering rule in the contract and end tied business metrics with deterministic secondary keys when needed.
- Use the stated inclusive or exclusive boundary, and prefer half-open time periods when requested.
- Preserve duplicate facts unless the prompt explicitly asks for distinct tuples or set semantics.
Common mistakes
- SQL does not evaluate all predicates left to right: A mixed AND/OR filter returns rows from an alternative branch without the required condition. Repair: Remember AND binds before OR, then add parentheses around intended alternatives.
- OR is not a loose AND: A row is expected to satisfy both alternatives when the contract says either. Repair: Use OR for alternatives and AND for requirements that every row must meet.
- NOT needs an explicit scope: Only the nearest comparison is negated when the whole business phrase should be excluded. Repair: Put the excluded phrase in parentheses before applying NOT.
- Parentheses are semantic: Removing parentheses changes which rows survive hidden crossover cases. Repair: Treat parentheses as the source of truth for grouped alternatives.
M07
IN, BETWEEN, and Range Semantics
SQL Trail treats every query as an evidence trail: identify source grain, transform rows deliberately, then compare output to a shared contract.
PostgreSQL-compatible local syntax; statement policy: select-only
Syntax and examples
Syntax template
SELECT <requested_columns>
FROM <source_table>
WHERE <source_population_filter>
GROUP BY <result_grain_columns>
ORDER BY <deterministic_tie_breakers>;
Minimal example
SELECT order_id, status FROM orders WHERE status IN ('completed', 'returned') ORDER BY order_id;
Realistic example
SELECT order_id, status FROM orders WHERE status IN ('completed', 'returned') ORDER BY order_id;
Notes and mistakes
Null, tie, and boundary notes
- Preserve NULL, empty string, zero, and false as distinct values unless the contract says to display a fallback.
- Use every ordering rule in the contract and end tied business metrics with deterministic secondary keys when needed.
- Use the stated inclusive or exclusive boundary, and prefer half-open time periods when requested.
- Preserve duplicate facts unless the prompt explicitly asks for distinct tuples or set semantics.
Common mistakes
- IN lists contain values: The column name is repeated inside the IN list or mixed with literal values. Repair: Write the source column once before IN, then list only quoted text values or typed literals inside parentheses.
- BETWEEN includes both endpoints: Rows exactly on the lower or upper bound are excluded as if BETWEEN were exclusive. Repair: Treat BETWEEN a AND b as >= a and <= b for scalar values.
- NOT IN is not automatically null-safe: A missing candidate value is expected to behave like a normal value in anti-list logic. Repair: Keep NULL handling explicit; this module flags the warning before later anti-join and null lessons.
- Timestamp periods need the next boundary: A date-time period ends at a guessed 23:59:59 value and misses later precision. Repair: Use >= period_start and < next_period_start for half-open timestamp periods.
M08
Text Matching and Normalization
SQL Trail treats every query as an evidence trail: identify source grain, transform rows deliberately, then compare output to a shared contract.
PostgreSQL-compatible local syntax; statement policy: select-only
Syntax and examples
Syntax template
SELECT <requested_columns>
FROM <source_table>
WHERE <source_population_filter>
GROUP BY <result_grain_columns>
ORDER BY <deterministic_tie_breakers>;
Minimal example
SELECT product_id, product_name, (product_name LIKE 'Camp%') AS matches_prefix FROM products ORDER BY product_id;
Realistic example
SELECT product_id, product_name, (product_name LIKE 'Camp%') AS matches_prefix FROM products ORDER BY product_id;
Notes and mistakes
Null, tie, and boundary notes
- Preserve NULL, empty string, zero, and false as distinct values unless the contract says to display a fallback.
- Use every ordering rule in the contract and end tied business metrics with deterministic secondary keys when needed.
- Use the stated inclusive or exclusive boundary, and prefer half-open time periods when requested.
- Preserve duplicate facts unless the prompt explicitly asks for distinct tuples or set semantics.
Common mistakes
- Percent is arbitrary length: A percent wildcard is treated as if it matched exactly one character. Repair: Use percent for any-length prefixes, suffixes, or substrings, and underscore for a single unknown character.
- LIKE is case-sensitive here: A lower-case pattern is expected to match Austin without requesting case-insensitive behavior. Repair: Use ILIKE when the requirement explicitly says case-insensitive.
- Wildcards are not equality text: A pattern such as percent Trail percent is compared with equals and returns no contains matches. Repair: Use LIKE for wildcard patterns and equals only for exact stored text.
- Normalize the stored value: Only the literal is lowercased or trimmed, leaving mixed-case or spaced source rows unmatched. Repair: Apply LOWER and TRIM to the city column for comparison while selecting the original city for display.
M09
NULL and Three-Valued Logic
SQL Trail treats every query as an evidence trail: identify source grain, transform rows deliberately, then compare output to a shared contract.
PostgreSQL-compatible local syntax; statement policy: select-only
Syntax and examples
Syntax template
SELECT <requested_columns>
FROM <source_table>
WHERE <source_population_filter>
GROUP BY <result_grain_columns>
ORDER BY <deterministic_tie_breakers>;
Minimal example
SELECT customer_id, loyalty_tier, (loyalty_tier = NULL) AS equals_null_result, (loyalty_tier IS NULL) AS is_null_result FROM customers ORDER BY customer_id;
Realistic example
SELECT customer_id, loyalty_tier, (loyalty_tier = NULL) AS equals_null_result, (loyalty_tier IS NULL) AS is_null_result FROM customers ORDER BY customer_id;
Notes and mistakes
Null, tie, and boundary notes
- Preserve NULL, empty string, zero, and false as distinct values unless the contract says to display a fallback.
- Use every ordering rule in the contract and end tied business metrics with deterministic secondary keys when needed.
- Use the stated inclusive or exclusive boundary, and prefer half-open time periods when requested.
- Preserve duplicate facts unless the prompt explicitly asks for distinct tuples or set semantics.
Common mistakes
- NULL is not a comparable value: A missing value is tested with equals or not equals and no rows behave as expected. Repair: Use IS NULL for missing values and IS NOT NULL for known values.
- UNKNOWN is not false: Rows with NULL comparisons are treated as if the comparison returned ordinary false. Repair: Classify TRUE, FALSE, and UNKNOWN separately before deciding which rows survive a WHERE clause.
- Fallback display is not filtering: COALESCE is used to show a label and assumed to have selected missing rows. Repair: Filter with IS NULL or IS NOT NULL, then use COALESCE only when the output should display a fallback.
- Empty zero and false are still values: Empty text, zero counts, or false booleans are treated as missing information. Repair: Keep NULL separate from empty strings, whitespace, zero, and false unless the contract explicitly normalizes them.
M10
DISTINCT, ORDER BY, LIMIT, and Stable Top-N
SQL Trail treats every query as an evidence trail: identify source grain, transform rows deliberately, then compare output to a shared contract.
PostgreSQL-compatible local syntax; statement policy: select-only
Syntax and examples
Syntax template
SELECT <requested_columns>
FROM <source_table>
WHERE <source_population_filter>
GROUP BY <result_grain_columns>
ORDER BY <deterministic_tie_breakers>;
Minimal example
SELECT DISTINCT customer_name, city FROM customers ORDER BY customer_name, city;
Realistic example
SELECT DISTINCT customer_name, city FROM customers ORDER BY customer_name, city;
Notes and mistakes
Null, tie, and boundary notes
- Preserve NULL, empty string, zero, and false as distinct values unless the contract says to display a fallback.
- Use every ordering rule in the contract and end tied business metrics with deterministic secondary keys when needed.
- Use the stated inclusive or exclusive boundary, and prefer half-open time periods when requested.
- Preserve duplicate facts unless the prompt explicitly asks for distinct tuples or set semantics.
Common mistakes
- DISTINCT works on tuples: A duplicate value in the first selected column is expected to collapse even when another selected column differs. Repair: Match the DISTINCT grain to every selected output column, not only the first expression.
- Table order is not stable evidence: A query looks right because the visible seed happens to appear in the desired order. Repair: Add ORDER BY for every ordered contract before relying on row positions.
- LIMIT is not top by itself: The first N rows are treated as highest rows without defining a descending sort. Repair: Sort by the requested metric and tie-breakers, then apply LIMIT.
- Ties need a unique final key: Rows tied on a metric or name swap order across hidden variants. Repair: Add deterministic secondary keys and end with a unique identifier.
M11
Scalar Functions, Types, and Casting
SQL Trail treats every query as an evidence trail: identify source grain, transform rows deliberately, then compare output to a shared contract.
PostgreSQL-compatible local syntax; statement policy: select-only
Syntax and examples
Syntax template
SELECT <requested_columns>
FROM <source_table>
WHERE <source_population_filter>
GROUP BY <result_grain_columns>
ORDER BY <deterministic_tie_breakers>;
Minimal example
SELECT customer_id, customer_name, LOWER(TRIM(city)) AS normalized_city FROM customers ORDER BY customer_id;
Realistic example
SELECT customer_id, customer_name, LOWER(TRIM(city)) AS normalized_city FROM customers ORDER BY customer_id;
Notes and mistakes
Null, tie, and boundary notes
- Preserve NULL, empty string, zero, and false as distinct values unless the contract says to display a fallback.
- Use every ordering rule in the contract and end tied business metrics with deterministic secondary keys when needed.
- Use the stated inclusive or exclusive boundary, and prefer half-open time periods when requested.
- Preserve duplicate facts unless the prompt explicitly asks for distinct tuples or set semantics.
Common mistakes
- Scalar functions preserve row grain: A text or length function is treated like an aggregate and expected to collapse rows. Repair: Apply scalar functions per row and keep the original row grain unless an aggregate is requested.
- Round after the full calculation: Source values are rounded before multiplication, producing near-boundary drift. Repair: Calculate first, then round the final expression at the requested precision.
- Cast before division: An integer division result is cast after the fractional part has already been discarded. Repair: Cast an operand to numeric before dividing.
- Not all text casts safely: Every raw text sample is cast directly and invalid or empty text aborts the query. Repair: Guard controlled text before casting and flag invalid rows explicitly.
M12
CASE, COALESCE, and Conditional Labels
SQL Trail treats every query as an evidence trail: identify source grain, transform rows deliberately, then compare output to a shared contract.
PostgreSQL-compatible local syntax; statement policy: select-only
Syntax and examples
Syntax template
SELECT <requested_columns>
FROM <source_table>
WHERE <source_population_filter>
GROUP BY <result_grain_columns>
ORDER BY <deterministic_tie_breakers>;
Minimal example
SELECT product_id, product_name, price, CASE WHEN price < 6.50 THEN 'low' WHEN price <= 14.00 THEN 'standard' ELSE 'premium' END AS price_band FROM products ORDER BY product_id;
Realistic example
SELECT product_id, product_name, price, CASE WHEN price < 6.50 THEN 'low' WHEN price <= 14.00 THEN 'standard' ELSE 'premium' END AS price_band FROM products ORDER BY product_id;
Notes and mistakes
Null, tie, and boundary notes
- Preserve NULL, empty string, zero, and false as distinct values unless the contract says to display a fallback.
- Use every ordering rule in the contract and end tied business metrics with deterministic secondary keys when needed.
- Use the stated inclusive or exclusive boundary, and prefer half-open time periods when requested.
- Preserve duplicate facts unless the prompt explicitly asks for distinct tuples or set semantics.
Common mistakes
- CASE stops at the first match: A row is expected to collect every matching label or reach a later branch after an earlier true condition. Repair: Order WHEN branches from most specific to broadest and trace exact boundary values top to bottom.
- ELSE is part of the contract: Unexpected statuses silently return NULL because only visible status values were listed. Repair: Add an explicit ELSE label whenever hidden or future values need a readable fallback.
- COALESCE is display fallback: A displayed fallback is assumed to change stored data or filter out missing rows. Repair: Use COALESCE for the selected expression, and keep filtering logic separate with IS NULL or CASE.
- Conditional outputs need compatible types: A CASE or COALESCE mixes text labels with numeric or boolean results and fails type resolution. Repair: Keep every returned branch in the same type family, such as text labels with text fallbacks.
M13
Aggregate Functions and Null Semantics
SQL Trail treats every query as an evidence trail: identify source grain, transform rows deliberately, then compare output to a shared contract.
PostgreSQL-compatible local syntax; statement policy: select-only
Syntax and examples
Syntax template
SELECT <requested_columns>
FROM <source_table>
WHERE <source_population_filter>
GROUP BY <result_grain_columns>
ORDER BY <deterministic_tie_breakers>;
Minimal example
SELECT COUNT(*)::int AS customer_rows, COUNT(loyalty_tier)::int AS known_loyalty_tier_rows FROM customers;
Realistic example
SELECT COUNT(*)::int AS customer_rows, COUNT(loyalty_tier)::int AS known_loyalty_tier_rows FROM customers;
Notes and mistakes
Null, tie, and boundary notes
- Preserve NULL, empty string, zero, and false as distinct values unless the contract says to display a fallback.
- Use every ordering rule in the contract and end tied business metrics with deterministic secondary keys when needed.
- Use the stated inclusive or exclusive boundary, and prefer half-open time periods when requested.
- Preserve duplicate facts unless the prompt explicitly asks for distinct tuples or set semantics.
Common mistakes
- COUNT star and COUNT expression differ: A nullable column count is expected to equal the total row count even when some values are missing. Repair: Use COUNT star for all input rows and COUNT column only for known non-null expression values.
- Global aggregates stay one row: A detail column is selected beside MIN, MAX, AVG, SUM, or COUNT without grouping. Repair: Remove detail columns for a one-row report, or group deliberately when the contract asks for one row per dimension.
- AVG does not treat NULL as zero: Missing values are included as zeros in an average or distinct known-value count. Repair: Let aggregate functions ignore NULL unless the prompt explicitly defines a fallback population.
- SUM over empty input is NULL: An empty filtered population is expected to produce a raw sum of zero. Repair: Preserve the raw NULL result and add COALESCE only when the output contract asks for a displayed zero.
M14
GROUP BY and Report Grain
SQL Trail treats every query as an evidence trail: identify source grain, transform rows deliberately, then compare output to a shared contract.
PostgreSQL-compatible local syntax; statement policy: select-only
Syntax and examples
Syntax template
SELECT <requested_columns>
FROM <source_table>
WHERE <source_population_filter>
GROUP BY <result_grain_columns>
ORDER BY <deterministic_tie_breakers>;
Minimal example
SELECT status, 'one row per order status' AS row_meaning, COUNT(*)::int AS order_count FROM orders GROUP BY status ORDER BY status;
Realistic example
SELECT status, 'one row per order status' AS row_meaning, COUNT(*)::int AS order_count FROM orders GROUP BY status ORDER BY status;
Notes and mistakes
Null, tie, and boundary notes
- Preserve NULL, empty string, zero, and false as distinct values unless the contract says to display a fallback.
- Use every ordering rule in the contract and end tied business metrics with deterministic secondary keys when needed.
- Use the stated inclusive or exclusive boundary, and prefer half-open time periods when requested.
- Preserve duplicate facts unless the prompt explicitly asks for distinct tuples or set semantics.
Common mistakes
- Grouped rows have a new meaning: A grouped result is read as if each output row still represented one source order or product. Repair: State the grouped row grain before writing or interpreting aggregate values.
- Every selected dimension defines grain: An extra selected column is added to GROUP BY and fragments the report into too many rows. Repair: Keep only the requested dimensions in SELECT and GROUP BY.
- GROUP BY is not DISTINCT with math: The grouping column is treated as duplicate suppression instead of the row promise for aggregate metrics. Repair: Use GROUP BY to define one output row per dimension value, then aggregate all input rows inside each group.
- Grouped reports still need deterministic order: Rows tied on metrics or containing NULL dimensions move around across hidden variants. Repair: Order by the grouped dimensions and state NULLS FIRST or NULLS LAST when null groups can appear.
M15
HAVING, Conditional Aggregation, and Safe Ratios
SQL Trail treats every query as an evidence trail: identify source grain, transform rows deliberately, then compare output to a shared contract.
PostgreSQL-compatible local syntax; statement policy: select-only
Syntax and examples
Syntax template
SELECT <requested_columns>
FROM <source_table>
WHERE <source_population_filter>
GROUP BY <result_grain_columns>
ORDER BY <deterministic_tie_breakers>;
Minimal example
SELECT condition_no, condition_text, clause_choice FROM (VALUES (1, 'order status equals completed', 'WHERE'), (2, 'ordered_at is in January', 'WHERE'), (3, 'customer_id is not null', 'WHERE'), (4, 'order_count is at least 2', 'HAVING'), (5, 'completed_order_count is greater than 0', 'HAVING'), (6, 'revenue is greater than 25', 'HAVING')) AS choices(condition_no, condition_text, clause_choice) ORDER BY condition_no;
Realistic example
SELECT condition_no, condition_text, clause_choice FROM (VALUES (1, 'order status equals completed', 'WHERE'), (2, 'ordered_at is in January', 'WHERE'), (3, 'customer_id is not null', 'WHERE'), (4, 'order_count is at least 2', 'HAVING'), (5, 'completed_order_count is greater than 0', 'HAVING'), (6, 'revenue is greater than 25', 'HAVING')) AS choices(condition_no, condition_text, clause_choice) ORDER BY condition_no;
Notes and mistakes
Null, tie, and boundary notes
- Preserve NULL, empty string, zero, and false as distinct values unless the contract says to display a fallback.
- Use every ordering rule in the contract and end tied business metrics with deterministic secondary keys when needed.
- Use the stated inclusive or exclusive boundary, and prefer half-open time periods when requested.
- Preserve duplicate facts unless the prompt explicitly asks for distinct tuples or set semantics.
Common mistakes
- WHERE cannot see aggregate values: A COUNT, SUM, or rate condition is placed in WHERE and fails before groups are formed. Repair: Filter source rows in WHERE, then filter grouped aggregate results in HAVING.
- Conditional counts need explicit zeroes: A CASE expression omits ELSE 0 and nonmatching rows stop contributing to the intended count. Repair: Return 1 for matching rows and 0 for nonmatches, or use FILTER on the aggregate.
- Integer division hides real rates: A rate looks like 0 or 1 because both operands stayed integer until after division. Repair: Cast the numerator or denominator to numeric before dividing.
- Population definitions must match the metric: A numerator, denominator, or HAVING condition uses a plausible but different set of rows. Repair: Name each population explicitly and keep the same denominator for the reported rate.
M16
Keys, Relationships, Cardinality, and Join Planning
SQL Trail treats every query as an evidence trail: identify source grain, transform rows deliberately, then compare output to a shared contract.
PostgreSQL-compatible local syntax; statement policy: select-only
Syntax and examples
Syntax template
SELECT <requested_columns>
FROM <source_table>
WHERE <source_population_filter>
GROUP BY <result_grain_columns>
ORDER BY <deterministic_tie_breakers>;
Minimal example
SELECT table_name, primary_key, foreign_keys FROM (VALUES ('customers', 'customer_id', 'none'), ('orders', 'order_id', 'customer_id -> customers.customer_id'), ('order_items', 'order_item_id', 'order_id -> orders.order_id; product_id -> products.product_id'), ('products', 'product_id', 'category_id -> categories.category_id')) AS key_map(table_name, primary_key, foreign_keys) ORDER BY table_name;
Realistic example
SELECT table_name, primary_key, foreign_keys FROM (VALUES ('customers', 'customer_id', 'none'), ('orders', 'order_id', 'customer_id -> customers.customer_id'), ('order_items', 'order_item_id', 'order_id -> orders.order_id; product_id -> products.product_id'), ('products', 'product_id', 'category_id -> categories.category_id')) AS key_map(table_name, primary_key, foreign_keys) ORDER BY table_name;
Notes and mistakes
Null, tie, and boundary notes
- Preserve NULL, empty string, zero, and false as distinct values unless the contract says to display a fallback.
- Use every ordering rule in the contract and end tied business metrics with deterministic secondary keys when needed.
- Use the stated inclusive or exclusive boundary, and prefer half-open time periods when requested.
- Preserve duplicate facts unless the prompt explicitly asks for distinct tuples or set semantics.
Common mistakes
- Similar names are not relationships: Columns are joined because their names or types look compatible, not because a foreign key path exists. Repair: Start from primary keys and foreign keys before choosing any join condition.
- Parent rows repeat through children: A customer or order is expected to stay unique after joining to one-to-many child rows. Repair: Predict how many child rows each parent can produce before trusting row counts.
- Bridge tables can own facts: The bridge is treated as pure plumbing and quantity is looked up on a dimension table. Repair: Name the bridge grain and keep bridge facts such as quantity on that row grain.
- Cartesian products are row-count evidence: A huge joined row count is treated as business volume instead of a missing relationship condition. Repair: Compare actual rows with parent times child rows and add the missing ON predicate.
M17
INNER JOIN and Qualified Columns
SQL Trail treats every query as an evidence trail: identify source grain, transform rows deliberately, then compare output to a shared contract.
PostgreSQL-compatible local syntax; statement policy: select-only
Syntax and examples
Syntax template
SELECT <requested_columns>
FROM <source_table>
WHERE <source_population_filter>
GROUP BY <result_grain_columns>
ORDER BY <deterministic_tie_breakers>;
Minimal example
SELECT o.order_id, c.customer_name FROM orders o JOIN customers c ON c.customer_id = o.customer_id ORDER BY o.order_id;
Realistic example
SELECT o.order_id, c.customer_name FROM orders o JOIN customers c ON c.customer_id = o.customer_id ORDER BY o.order_id;
Notes and mistakes
Null, tie, and boundary notes
- Preserve NULL, empty string, zero, and false as distinct values unless the contract says to display a fallback.
- Use every ordering rule in the contract and end tied business metrics with deterministic secondary keys when needed.
- Use the stated inclusive or exclusive boundary, and prefer half-open time periods when requested.
- Preserve duplicate facts unless the prompt explicitly asks for distinct tuples or set semantics.
Common mistakes
- ON needs the real foreign key: The join condition pairs similarly named primary keys instead of the child foreign key. Repair: Use the verified relationship from the schema: child foreign key to parent primary key.
- One-to-many rows are expected repeats: Repeated parent labels in a joined result are treated as duplicates to remove. Repair: Name the output grain; parent values repeat when each child row is still represented.
- Ambiguous columns need table aliases: A shared column name is selected without qualification after joining tables. Repair: Qualify source columns with table aliases and add readable output aliases when needed.
- Relationship and row filters are different: A status rule is mixed into the join condition and the path becomes hard to audit. Repair: Put key relationships in ON and row filters in WHERE for inner joins.
M18
LEFT JOIN, Missing Relationships, and Zero Counts
SQL Trail treats every query as an evidence trail: identify source grain, transform rows deliberately, then compare output to a shared contract.
PostgreSQL-compatible local syntax; statement policy: select-only
Syntax and examples
Syntax template
SELECT <requested_columns>
FROM <source_table>
WHERE <source_population_filter>
GROUP BY <result_grain_columns>
ORDER BY <deterministic_tie_breakers>;
Minimal example
SELECT join_type, joined_rows, unmatched_customer_rows FROM (VALUES ('inner_join', (SELECT COUNT(*)::int FROM customers c JOIN orders o ON o.customer_id = c.customer_id), 0), ('left_join', (SELECT COUNT(*)::int FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id), (SELECT COUNT(*)::int FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id WHERE o.order_id IS NULL))) AS join_counts(join_type, joined_rows, unmatched_customer_rows) ORDER BY join_type;
Realistic example
SELECT join_type, joined_rows, unmatched_customer_rows FROM (VALUES ('inner_join', (SELECT COUNT(*)::int FROM customers c JOIN orders o ON o.customer_id = c.customer_id), 0), ('left_join', (SELECT COUNT(*)::int FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id), (SELECT COUNT(*)::int FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id WHERE o.order_id IS NULL))) AS join_counts(join_type, joined_rows, unmatched_customer_rows) ORDER BY join_type;
Notes and mistakes
Null, tie, and boundary notes
- Preserve NULL, empty string, zero, and false as distinct values unless the contract says to display a fallback.
- Use every ordering rule in the contract and end tied business metrics with deterministic secondary keys when needed.
- Use the stated inclusive or exclusive boundary, and prefer half-open time periods when requested.
- Preserve duplicate facts unless the prompt explicitly asks for distinct tuples or set semantics.
Common mistakes
- LEFT preserves the written left side: The physically smaller table is assumed to be preserved regardless of query order. Repair: Put the parent population that must survive before LEFT JOIN.
- Right filters in WHERE erase no-matches: Rows with no qualifying child disappear even though the prompt asks for zeroes. Repair: Move right-side qualification into ON or into aggregate FILTER depending on the requested metric.
- COUNT star is not a zero-child count: A parent with no child reports 1 because the NULL-extended preserved row is counted. Repair: Count the right-side key when the metric is number of matched children.
- Nullable attributes do not prove absence: A matched child with a NULL delivered_at or other attribute is treated as if no row matched. Repair: Use the right-side key NULL test for anti-joins and absence checks.
M19
Multi-Table Joins and Fact Grain
SQL Trail treats every query as an evidence trail: identify source grain, transform rows deliberately, then compare output to a shared contract.
PostgreSQL-compatible local syntax; statement policy: select-only
Syntax and examples
Syntax template
SELECT <requested_columns>
FROM <source_table>
WHERE <source_population_filter>
GROUP BY <result_grain_columns>
ORDER BY <deterministic_tie_breakers>;
Minimal example
SELECT step_no, from_table, to_table, join_condition, output_grain FROM (VALUES (1, 'customers', 'orders', 'orders.customer_id = customers.customer_id', 'customers repeat once per matched order'), (2, 'orders', 'order_items', 'order_items.order_id = orders.order_id', 'order rows repeat once per line item'), (3, 'order_items', 'products', 'products.product_id = order_items.product_id', 'line-item grain remains unchanged'), (4, 'products', 'categories', 'categories.category_id = products.category_id', 'optional category context may be missing')) AS path(step_no, from_table, to_table, join_condition, output_grain) ORDER BY step_no;
Realistic example
SELECT step_no, from_table, to_table, join_condition, output_grain FROM (VALUES (1, 'customers', 'orders', 'orders.customer_id = customers.customer_id', 'customers repeat once per matched order'), (2, 'orders', 'order_items', 'order_items.order_id = orders.order_id', 'order rows repeat once per line item'), (3, 'order_items', 'products', 'products.product_id = order_items.product_id', 'line-item grain remains unchanged'), (4, 'products', 'categories', 'categories.category_id = products.category_id', 'optional category context may be missing')) AS path(step_no, from_table, to_table, join_condition, output_grain) ORDER BY step_no;
Notes and mistakes
Null, tie, and boundary notes
- Preserve NULL, empty string, zero, and false as distinct values unless the contract says to display a fallback.
- Use every ordering rule in the contract and end tied business metrics with deterministic secondary keys when needed.
- Use the stated inclusive or exclusive boundary, and prefer half-open time periods when requested.
- Preserve duplicate facts unless the prompt explicitly asks for distinct tuples or set semantics.
Common mistakes
- Dimensions do not own historical facts: Revenue is calculated from the current product price because product names live on products. Repair: Use product dimensions for context and order_items for captured quantity and unit price.
- Every join can change grain: A multi-table query is trusted without comparing line counts before and after each relationship. Repair: Add one join at a time and compare row counts against the intended fact grain.
- Optional dimensions need preservation: Uncategorized product facts disappear when categories are joined with INNER JOIN. Repair: LEFT JOIN optional dimensions and label missing dimension values explicitly.
- DISTINCT is not a grain proof: Repeated rows are hidden with DISTINCT instead of proving the final key stayed unique. Repair: Report final rows beside distinct fact keys when the checkpoint asks for uniqueness proof.
M20
Fan-Out, Pre-Aggregation, Self Joins, and Non-Equi Joins
SQL Trail treats every query as an evidence trail: identify source grain, transform rows deliberately, then compare output to a shared contract.
PostgreSQL-compatible local syntax; statement policy: select-only
Syntax and examples
Syntax template
SELECT <requested_columns>
FROM <source_table>
WHERE <source_population_filter>
GROUP BY <result_grain_columns>
ORDER BY <deterministic_tie_breakers>;
Minimal example
SELECT o.order_id, COUNT(DISTINCT oi.order_item_id)::int AS item_rows, COUNT(DISTINCT pay.payment_id)::int AS payment_rows, (COUNT(DISTINCT oi.order_item_id) * COUNT(DISTINCT pay.payment_id))::int AS naive_join_rows FROM orders o LEFT JOIN order_items oi ON oi.order_id = o.order_id LEFT JOIN payments pay ON pay.order_id = o.order_id GROUP BY o.order_id HAVING COUNT(DISTINCT oi.order_item_id) > 1 AND COUNT(DISTINCT pay.payment_id) > 1 ORDER BY o.order_id;
Realistic example
SELECT o.order_id, COUNT(DISTINCT oi.order_item_id)::int AS item_rows, COUNT(DISTINCT pay.payment_id)::int AS payment_rows, (COUNT(DISTINCT oi.order_item_id) * COUNT(DISTINCT pay.payment_id))::int AS naive_join_rows FROM orders o LEFT JOIN order_items oi ON oi.order_id = o.order_id LEFT JOIN payments pay ON pay.order_id = o.order_id GROUP BY o.order_id HAVING COUNT(DISTINCT oi.order_item_id) > 1 AND COUNT(DISTINCT pay.payment_id) > 1 ORDER BY o.order_id;
Notes and mistakes
Null, tie, and boundary notes
- Preserve NULL, empty string, zero, and false as distinct values unless the contract says to display a fallback.
- Use every ordering rule in the contract and end tied business metrics with deterministic secondary keys when needed.
- Use the stated inclusive or exclusive boundary, and prefer half-open time periods when requested.
- Preserve duplicate facts unless the prompt explicitly asks for distinct tuples or set semantics.
Common mistakes
- Independent children multiply: Items and payments are joined directly and the resulting SUM looks plausible but repeats facts. Repair: Predict child-count multiplication and pre-aggregate each child collection to the parent grain.
- DISTINCT after SUM cannot repair fan-out: Repeated equal payment or item amounts are deduplicated instead of preserving real repeated facts. Repair: Stage facts at the correct grain before SUM rather than wrapping SUM inputs in DISTINCT.
- Self joins need role names: The same table is joined without clear aliases, making referred and referring customers interchangeable. Repair: Alias each table instance by its role and qualify every selected column.
- Range bands must not overlap: A boundary amount can match two bands or no band because both endpoints are inclusive. Repair: Use lower-inclusive and upper-exclusive predicates for adjacent ranges.
M21
Scalar and Set Subqueries
SQL Trail treats every query as an evidence trail: identify source grain, transform rows deliberately, then compare output to a shared contract.
PostgreSQL-compatible local syntax; statement policy: select-only
Syntax and examples
Syntax template
SELECT <requested_columns>
FROM <source_table>
WHERE <source_population_filter>
GROUP BY <result_grain_columns>
ORDER BY <deterministic_tie_breakers>;
Minimal example
SELECT product_id, product_name, current_price FROM products WHERE current_price > (SELECT AVG(current_price) FROM products) ORDER BY product_id;
Realistic example
SELECT product_id, product_name, current_price FROM products WHERE current_price > (SELECT AVG(current_price) FROM products) ORDER BY product_id;
Notes and mistakes
Null, tie, and boundary notes
- Preserve NULL, empty string, zero, and false as distinct values unless the contract says to display a fallback.
- Use every ordering rule in the contract and end tied business metrics with deterministic secondary keys when needed.
- Use the stated inclusive or exclusive boundary, and prefer half-open time periods when requested.
- Preserve duplicate facts unless the prompt explicitly asks for distinct tuples or set semantics.
Common mistakes
- Scalar means one value: A detail subquery is placed where equality or comparison expects one result. Repair: Use an aggregate or another predicate so the nested SELECT returns one value for scalar comparison.
- Outer filters do not leak inward: The outer query filters active rows, but the nested average still includes inactive rows. Repair: Repeat the intended population filter inside the subquery.
- IN needs one comparable column: The set subquery returns two columns because both look useful for debugging. Repair: Return only the key column being compared by the IN predicate.
- Membership is not payload: A join is used only to test related-row presence, causing duplicates or unnecessary columns. Repair: Use a set subquery for membership and reserve joins for related columns that must appear in output.
M22
EXISTS, NOT EXISTS, and Correlation
SQL Trail treats every query as an evidence trail: identify source grain, transform rows deliberately, then compare output to a shared contract.
PostgreSQL-compatible local syntax; statement policy: select-only
Syntax and examples
Syntax template
SELECT <requested_columns>
FROM <source_table>
WHERE <source_population_filter>
GROUP BY <result_grain_columns>
ORDER BY <deterministic_tie_breakers>;
Minimal example
SELECT c.customer_id, c.customer_name, EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND o.status = 'completed') AS has_completed_order FROM customers c ORDER BY c.customer_id;
Realistic example
SELECT c.customer_id, c.customer_name, EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND o.status = 'completed') AS has_completed_order FROM customers c ORDER BY c.customer_id;
Notes and mistakes
Null, tie, and boundary notes
- Preserve NULL, empty string, zero, and false as distinct values unless the contract says to display a fallback.
- Use every ordering rule in the contract and end tied business metrics with deterministic secondary keys when needed.
- Use the stated inclusive or exclusive boundary, and prefer half-open time periods when requested.
- Preserve duplicate facts unless the prompt explicitly asks for distinct tuples or set semantics.
Common mistakes
- EXISTS needs correlation: Every outer row qualifies because the inner query finds any matching row anywhere. Repair: Add the outer-to-inner key predicate inside the EXISTS or NOT EXISTS subquery.
- EXISTS ignores selected payload: Changing SELECT 1 to SELECT star is expected to change which outer rows qualify. Repair: Treat EXISTS as a Boolean presence test; the inner SELECT list is only payload syntax.
- NOT IN is not null-safe: An anti-match query returns no rows when the subquery set contains NULL. Repair: Use NOT EXISTS with a correlated predicate when nullable values can appear.
- DISTINCT can hide duplicate children: A join creates duplicate parent rows and DISTINCT is used as the main repair. Repair: Use EXISTS when the child table only proves presence and contributes no output columns.
M23
Common Table Expressions and Query Pipelines
SQL Trail treats every query as an evidence trail: identify source grain, transform rows deliberately, then compare output to a shared contract.
PostgreSQL-compatible local syntax; statement policy: select-only
Syntax and examples
Syntax template
SELECT <requested_columns>
FROM <source_table>
WHERE <source_population_filter>
GROUP BY <result_grain_columns>
ORDER BY <deterministic_tie_breakers>;
Minimal example
WITH product_revenue AS (SELECT p.category_id, p.product_id, p.product_name, SUM(oi.quantity * oi.unit_price)::numeric(10,2) AS revenue FROM products p JOIN order_items oi ON oi.product_id = p.product_id GROUP BY p.category_id, p.product_id, p.product_name) SELECT category_id, product_id, product_name, revenue, DENSE_RANK() OVER (PARTITION BY category_id ORDER BY revenue DESC, product_id) AS revenue_rank FROM product_revenue ORDER BY category_id NULLS LAST, revenue_rank, product_id;
Realistic example
WITH product_revenue AS (SELECT p.category_id, p.product_id, p.product_name, SUM(oi.quantity * oi.unit_price)::numeric(10,2) AS revenue FROM products p JOIN order_items oi ON oi.product_id = p.product_id GROUP BY p.category_id, p.product_id, p.product_name) SELECT category_id, product_id, product_name, revenue, DENSE_RANK() OVER (PARTITION BY category_id ORDER BY revenue DESC, product_id) AS revenue_rank FROM product_revenue ORDER BY category_id NULLS LAST, revenue_rank, product_id;
Notes and mistakes
Null, tie, and boundary notes
- Preserve NULL, empty string, zero, and false as distinct values unless the contract says to display a fallback.
- Use every ordering rule in the contract and end tied business metrics with deterministic secondary keys when needed.
- Use the stated inclusive or exclusive boundary, and prefer half-open time periods when requested.
- Preserve duplicate facts unless the prompt explicitly asks for distinct tuples or set semantics.
Common mistakes
- Wrong grain: The row count looks plausible but duplicates or missing zero rows appear. Repair: Name the intended grain, then inspect joins and GROUP BY clauses against that grain.
- Unstable order: The same rows appear in a different order during checks. Repair: Add a deterministic secondary sort key when ties are possible.
- Match the exact output contract: calculate product revenue first; rank inside each category; use deterministic order for ties.
- This result suggests the query grain drifted from one row per sold product. Check joins, grouping, and filters before changing display details.
M24
Set Operations and Compatible Result Shapes
SQL Trail treats every query as an evidence trail: identify source grain, transform rows deliberately, then compare output to a shared contract.
PostgreSQL-compatible local syntax; statement policy: select-only
Syntax and examples
Syntax template
SELECT <requested_columns>
FROM <source_table>
WHERE <source_population_filter>
GROUP BY <result_grain_columns>
ORDER BY <deterministic_tie_breakers>;
Minimal example
WITH order_revenue AS (SELECT o.order_id, SUM(oi.quantity * oi.unit_price)::numeric(10,2) AS ordered_revenue FROM orders o JOIN order_items oi ON oi.order_id = o.order_id WHERE o.status = 'completed' GROUP BY o.order_id), captured AS (SELECT order_id, SUM(amount)::numeric(10,2) AS captured_amount FROM payments WHERE payment_status = 'captured' GROUP BY order_id) SELECT r.order_id, r.ordered_revenue, COALESCE(c.captured_amount, 0)::numeric(10,2) AS captured_amount FROM order_revenue r LEFT JOIN captured c ON c.order_id = r.order_id ORDER BY r.order_id;
Realistic example
WITH order_revenue AS (SELECT o.order_id, SUM(oi.quantity * oi.unit_price)::numeric(10,2) AS ordered_revenue FROM orders o JOIN order_items oi ON oi.order_id = o.order_id WHERE o.status = 'completed' GROUP BY o.order_id), captured AS (SELECT order_id, SUM(amount)::numeric(10,2) AS captured_amount FROM payments WHERE payment_status = 'captured' GROUP BY order_id) SELECT r.order_id, r.ordered_revenue, COALESCE(c.captured_amount, 0)::numeric(10,2) AS captured_amount FROM order_revenue r LEFT JOIN captured c ON c.order_id = r.order_id ORDER BY r.order_id;
Notes and mistakes
Null, tie, and boundary notes
- Preserve NULL, empty string, zero, and false as distinct values unless the contract says to display a fallback.
- Use every ordering rule in the contract and end tied business metrics with deterministic secondary keys when needed.
- Use the stated inclusive or exclusive boundary, and prefer half-open time periods when requested.
- Preserve duplicate facts unless the prompt explicitly asks for distinct tuples or set semantics.
Common mistakes
- Wrong grain: The row count looks plausible but duplicates or missing zero rows appear. Repair: Name the intended grain, then inspect joins and GROUP BY clauses against that grain.
- Unstable order: The same rows appear in a different order during checks. Repair: Add a deterministic secondary sort key when ties are possible.
- Match the exact output contract: pre-aggregate revenue to order grain; pre-aggregate captured payments to order grain; order by order_id.
- This result suggests the query grain drifted from one row per completed order. Check joins, grouping, and filters before changing display details.
M25
Dates, Timestamps, Time Zones, and Half-Open Ranges
SQL Trail treats every query as an evidence trail: identify source grain, transform rows deliberately, then compare output to a shared contract.
PostgreSQL-compatible local syntax; statement policy: select-only
Syntax and examples
Syntax template
SELECT <requested_columns>
FROM <source_table>
WHERE <source_population_filter>
GROUP BY <result_grain_columns>
ORDER BY <deterministic_tie_breakers>;
Minimal example
SELECT DATE_TRUNC('week', signed_up_at)::date AS signup_week, COUNT(*)::int AS users FROM users GROUP BY signup_week ORDER BY signup_week;
Realistic example
SELECT DATE_TRUNC('week', signed_up_at)::date AS signup_week, COUNT(*)::int AS users FROM users GROUP BY signup_week ORDER BY signup_week;
Notes and mistakes
Null, tie, and boundary notes
- Preserve NULL, empty string, zero, and false as distinct values unless the contract says to display a fallback.
- Use every ordering rule in the contract and end tied business metrics with deterministic secondary keys when needed.
- Use the stated inclusive or exclusive boundary, and prefer half-open time periods when requested.
- Preserve duplicate facts unless the prompt explicitly asks for distinct tuples or set semantics.
Common mistakes
- Wrong grain: The row count looks plausible but duplicates or missing zero rows appear. Repair: Name the intended grain, then inspect joins and GROUP BY clauses against that grain.
- Unstable order: The same rows appear in a different order during checks. Repair: Add a deterministic secondary sort key when ties are possible.
- Match the exact output contract: return signup_week; return users; order chronologically.
- This result suggests the query grain drifted from one row per signup week. Check joins, grouping, and filters before changing display details.
M26
Period Grouping, Complete Calendars, Cohorts, and Retention Foundations
SQL Trail treats every query as an evidence trail: identify source grain, transform rows deliberately, then compare output to a shared contract.
PostgreSQL-compatible local syntax; statement policy: select-only
Syntax and examples
Syntax template
SELECT <requested_columns>
FROM <source_table>
WHERE <source_population_filter>
GROUP BY <result_grain_columns>
ORDER BY <deterministic_tie_breakers>;
Minimal example
SELECT user_id, MIN(occurred_at) AS activated_at FROM events WHERE event_name = 'created_project' GROUP BY user_id ORDER BY user_id;
Realistic example
SELECT user_id, MIN(occurred_at) AS activated_at FROM events WHERE event_name = 'created_project' GROUP BY user_id ORDER BY user_id;
Notes and mistakes
Null, tie, and boundary notes
- Preserve NULL, empty string, zero, and false as distinct values unless the contract says to display a fallback.
- Use every ordering rule in the contract and end tied business metrics with deterministic secondary keys when needed.
- Use the stated inclusive or exclusive boundary, and prefer half-open time periods when requested.
- Preserve duplicate facts unless the prompt explicitly asks for distinct tuples or set semantics.
Common mistakes
- Wrong grain: The row count looks plausible but duplicates or missing zero rows appear. Repair: Name the intended grain, then inspect joins and GROUP BY clauses against that grain.
- Unstable order: The same rows appear in a different order during checks. Repair: Add a deterministic secondary sort key when ties are possible.
- Match the exact output contract: return one activation per user; use the first created_project time; order by user_id.
- This result suggests the query grain drifted from one row per activated user. Check joins, grouping, and filters before changing display details.
M27
Window Foundations and Ranking
SQL Trail treats every query as an evidence trail: identify source grain, transform rows deliberately, then compare output to a shared contract.
PostgreSQL-compatible local syntax; statement policy: select-only
Syntax and examples
Syntax template
SELECT <requested_columns>
FROM <source_table>
WHERE <source_population_filter>
GROUP BY <result_grain_columns>
ORDER BY <deterministic_tie_breakers>;
Minimal example
WITH activated AS (SELECT DISTINCT user_id FROM events WHERE event_name = 'created_project') SELECT ea.variant, COUNT(*)::int AS assigned_users, COUNT(a.user_id)::int AS activated_users, ROUND(COUNT(a.user_id)::numeric / NULLIF(COUNT(*), 0), 2) AS activation_rate FROM experiment_assignments ea LEFT JOIN activated a ON a.user_id = ea.user_id GROUP BY ea.variant ORDER BY ea.variant;
Realistic example
WITH activated AS (SELECT DISTINCT user_id FROM events WHERE event_name = 'created_project') SELECT ea.variant, COUNT(*)::int AS assigned_users, COUNT(a.user_id)::int AS activated_users, ROUND(COUNT(a.user_id)::numeric / NULLIF(COUNT(*), 0), 2) AS activation_rate FROM experiment_assignments ea LEFT JOIN activated a ON a.user_id = ea.user_id GROUP BY ea.variant ORDER BY ea.variant;
Notes and mistakes
Null, tie, and boundary notes
- Preserve NULL, empty string, zero, and false as distinct values unless the contract says to display a fallback.
- Use every ordering rule in the contract and end tied business metrics with deterministic secondary keys when needed.
- Use the stated inclusive or exclusive boundary, and prefer half-open time periods when requested.
- Preserve duplicate facts unless the prompt explicitly asks for distinct tuples or set semantics.
Common mistakes
- Wrong grain: The row count looks plausible but duplicates or missing zero rows appear. Repair: Name the intended grain, then inspect joins and GROUP BY clauses against that grain.
- Unstable order: The same rows appear in a different order during checks. Repair: Add a deterministic secondary sort key when ties are possible.
- Match the exact output contract: return variant; return assigned and activated counts; use a safe denominator.
- This result suggests the query grain drifted from one row per experiment variant. Check joins, grouping, and filters before changing display details.
M28
LAG, LEAD, and Period Change
SQL Trail treats every query as an evidence trail: identify source grain, transform rows deliberately, then compare output to a shared contract.
PostgreSQL-compatible local syntax; statement policy: select-only
Syntax and examples
Syntax template
SELECT <requested_columns>
FROM <source_table>
WHERE <source_population_filter>
GROUP BY <result_grain_columns>
ORDER BY <deterministic_tie_breakers>;
Minimal example
SELECT package_id, exception_type, occurred_at, COUNT(*)::int AS duplicate_count FROM exceptions GROUP BY package_id, exception_type, occurred_at HAVING COUNT(*) > 1 ORDER BY package_id, exception_type, occurred_at;
Realistic example
SELECT package_id, exception_type, occurred_at, COUNT(*)::int AS duplicate_count FROM exceptions GROUP BY package_id, exception_type, occurred_at HAVING COUNT(*) > 1 ORDER BY package_id, exception_type, occurred_at;
Notes and mistakes
Null, tie, and boundary notes
- Preserve NULL, empty string, zero, and false as distinct values unless the contract says to display a fallback.
- Use every ordering rule in the contract and end tied business metrics with deterministic secondary keys when needed.
- Use the stated inclusive or exclusive boundary, and prefer half-open time periods when requested.
- Preserve duplicate facts unless the prompt explicitly asks for distinct tuples or set semantics.
Common mistakes
- Wrong grain: The row count looks plausible but duplicates or missing zero rows appear. Repair: Name the intended grain, then inspect joins and GROUP BY clauses against that grain.
- Unstable order: The same rows appear in a different order during checks. Repair: Add a deterministic secondary sort key when ties are possible.
- Match the exact output contract: return grouped exception identity; include duplicate_count; show only duplicate groups.
- This result suggests the query grain drifted from one row per duplicate exception group. Check joins, grouping, and filters before changing display details.
M29
Running Totals, Frames, Rolling Metrics, and Percent of Total
SQL Trail treats every query as an evidence trail: identify source grain, transform rows deliberately, then compare output to a shared contract.
PostgreSQL-compatible local syntax; statement policy: select-only
Syntax and examples
Syntax template
SELECT <requested_columns>
FROM <source_table>
WHERE <source_population_filter>
GROUP BY <result_grain_columns>
ORDER BY <deterministic_tie_breakers>;
Minimal example
SELECT scan_id, package_id, scan_type, ROUND(EXTRACT(EPOCH FROM (ingested_at - scanned_at)) / 60, 1) AS ingest_delay_minutes FROM scans ORDER BY ingest_delay_minutes DESC, scan_id;
Realistic example
SELECT scan_id, package_id, scan_type, ROUND(EXTRACT(EPOCH FROM (ingested_at - scanned_at)) / 60, 1) AS ingest_delay_minutes FROM scans ORDER BY ingest_delay_minutes DESC, scan_id;
Notes and mistakes
Null, tie, and boundary notes
- Preserve NULL, empty string, zero, and false as distinct values unless the contract says to display a fallback.
- Use every ordering rule in the contract and end tied business metrics with deterministic secondary keys when needed.
- Use the stated inclusive or exclusive boundary, and prefer half-open time periods when requested.
- Preserve duplicate facts unless the prompt explicitly asks for distinct tuples or set semantics.
Common mistakes
- Wrong grain: The row count looks plausible but duplicates or missing zero rows appear. Repair: Name the intended grain, then inspect joins and GROUP BY clauses against that grain.
- Unstable order: The same rows appear in a different order during checks. Repair: Add a deterministic secondary sort key when ties are possible.
- Match the exact output contract: return scan identifiers; calculate ingest_delay_minutes; sort largest delay first.
- This result suggests the query grain drifted from one row per scan. Check joins, grouping, and filters before changing display details.
M30
Logical Processing Order, Systematic Debugging, and Data Quality
SQL Trail treats every query as an evidence trail: identify source grain, transform rows deliberately, then compare output to a shared contract.
PostgreSQL-compatible local syntax; statement policy: select-only
Syntax and examples
Syntax template
SELECT <requested_columns>
FROM <source_table>
WHERE <source_population_filter>
GROUP BY <result_grain_columns>
ORDER BY <deterministic_tie_breakers>;
Minimal example
SELECT package_id, scan_id, scan_type, scanned_at FROM scans ORDER BY package_id, scanned_at, scan_id;
Realistic example
SELECT package_id, scan_id, scan_type, scanned_at FROM scans ORDER BY package_id, scanned_at, scan_id;
Notes and mistakes
Null, tie, and boundary notes
- Preserve NULL, empty string, zero, and false as distinct values unless the contract says to display a fallback.
- Use every ordering rule in the contract and end tied business metrics with deterministic secondary keys when needed.
- Use the stated inclusive or exclusive boundary, and prefer half-open time periods when requested.
- Preserve duplicate facts unless the prompt explicitly asks for distinct tuples or set semantics.
Common mistakes
- Wrong grain: The row count looks plausible but duplicates or missing zero rows appear. Repair: Name the intended grain, then inspect joins and GROUP BY clauses against that grain.
- Unstable order: The same rows appear in a different order during checks. Repair: Add a deterministic secondary sort key when ties are possible.
- Match the exact output contract: return package_id, scan_id, scan_type, scanned_at; order by package, scan time, scan_id.
- This result suggests the query grain drifted from one row per scan. Check joins, grouping, and filters before changing display details.
M31
INSERT, UPDATE, DELETE, and RETURNING
SQL Trail treats every query as an evidence trail: identify source grain, transform rows deliberately, then compare output to a shared contract.
PostgreSQL-compatible local syntax; statement policy: select-only
Syntax and examples
Syntax template
SELECT <requested_columns>
FROM <source_table>
WHERE <source_population_filter>
GROUP BY <result_grain_columns>
ORDER BY <deterministic_tie_breakers>;
Minimal example
SELECT e.employee_id, e.employee_name FROM employees e WHERE e.active AND NOT EXISTS (SELECT 1 FROM project_members pm WHERE pm.employee_id = e.employee_id) ORDER BY e.employee_id;
Realistic example
SELECT e.employee_id, e.employee_name FROM employees e WHERE e.active AND NOT EXISTS (SELECT 1 FROM project_members pm WHERE pm.employee_id = e.employee_id) ORDER BY e.employee_id;
Notes and mistakes
Null, tie, and boundary notes
- Preserve NULL, empty string, zero, and false as distinct values unless the contract says to display a fallback.
- Use every ordering rule in the contract and end tied business metrics with deterministic secondary keys when needed.
- Use the stated inclusive or exclusive boundary, and prefer half-open time periods when requested.
- Preserve duplicate facts unless the prompt explicitly asks for distinct tuples or set semantics.
Common mistakes
- Wrong grain: The row count looks plausible but duplicates or missing zero rows appear. Repair: Name the intended grain, then inspect joins and GROUP BY clauses against that grain.
- Unstable order: The same rows appear in a different order during checks. Repair: Add a deterministic secondary sort key when ties are possible.
- Match the exact output contract: return active employees; include employees with zero project_members rows; order by employee_id.
- This result suggests the query grain drifted from one row per active unassigned employee. Check joins, grouping, and filters before changing display details.
M32
Transactions, Constraints, and Upserts
SQL Trail treats every query as an evidence trail: identify source grain, transform rows deliberately, then compare output to a shared contract.
PostgreSQL-compatible local syntax; statement policy: single-dml
Syntax and examples
Syntax template
UPDATE <target_table>
SET <changed_column> = <safe_expression>
WHERE <narrow_key_or_population>
RETURNING <audit_columns>;
Minimal example
UPDATE employees SET active = FALSE WHERE employee_id = 6 RETURNING employee_id, active;
Realistic example
UPDATE employees SET active = FALSE WHERE employee_id = 6 RETURNING employee_id, active;
Notes and mistakes
Null, tie, and boundary notes
- Preserve NULL, empty string, zero, and false as distinct values unless the contract says to display a fallback.
- Use every ordering rule in the contract and end tied business metrics with deterministic secondary keys when needed.
- Use the stated inclusive or exclusive boundary, and prefer half-open time periods when requested.
- Preserve duplicate facts unless the prompt explicitly asks for distinct tuples or set semantics.
Common mistakes
- Wrong grain: The row count looks plausible but duplicates or missing zero rows appear. Repair: Name the intended grain, then inspect joins and GROUP BY clauses against that grain.
- Unstable order: The same rows appear in a different order during checks. Repair: Add a deterministic secondary sort key when ties are possible.
- Match the exact output contract: update only employee_id 6; return employee_id and active; leave all changes rolled back after grading.
- This result suggests the query grain drifted from one returned changed row. Check joins, grouping, and filters before changing display details.
M33
EXPLAIN, Index Fundamentals, and Query Quality
SQL Trail treats every query as an evidence trail: identify source grain, transform rows deliberately, then compare output to a shared contract.
PostgreSQL-compatible local syntax; statement policy: single-dml
Syntax and examples
Syntax template
UPDATE <target_table>
SET <changed_column> = <safe_expression>
WHERE <narrow_key_or_population>
RETURNING <audit_columns>;
Minimal example
INSERT INTO roles (role_id, role_name) VALUES (4, 'Reviewer') ON CONFLICT (role_id) DO UPDATE SET role_name = EXCLUDED.role_name RETURNING role_id, role_name;
Realistic example
INSERT INTO roles (role_id, role_name) VALUES (4, 'Reviewer') ON CONFLICT (role_id) DO UPDATE SET role_name = EXCLUDED.role_name RETURNING role_id, role_name;
Notes and mistakes
Null, tie, and boundary notes
- Preserve NULL, empty string, zero, and false as distinct values unless the contract says to display a fallback.
- Use every ordering rule in the contract and end tied business metrics with deterministic secondary keys when needed.
- Use the stated inclusive or exclusive boundary, and prefer half-open time periods when requested.
- Preserve duplicate facts unless the prompt explicitly asks for distinct tuples or set semantics.
Common mistakes
- Wrong grain: The row count looks plausible but duplicates or missing zero rows appear. Repair: Name the intended grain, then inspect joins and GROUP BY clauses against that grain.
- Unstable order: The same rows appear in a different order during checks. Repair: Add a deterministic secondary sort key when ties are possible.
- Match the exact output contract: insert or update role_id 4; return role_id and role_name; be safe to run more than once.
- This result suggests the query grain drifted from one returned role row. Check joins, grouping, and filters before changing display details.
