Mode disclosure
All modes use one coherent workspace; only disclosure and guidance change. Learn mode keeps theory, concept names, full schema help, progressive hints, and solution review available.
Multi-Table Joins and Fact Grain / write query
M19-A07 - Checkpoint - build a multi-table report and prove final key uniqueness at the requested grain
M19-A07 - Checkpoint - build a multi-table report and prove final key uniqueness at the requested grain. Navigate multiple tables while preserving fact grain and metric source.
- Result grain
- one category key including uncategorized with line-item uniqueness proof
- Exact columns
- category_key; category_label; final_rows; distinct_line_items; historical_revenue
SQL editor shortcuts: Ctrl or Command Enter runs the query, Ctrl or Command Shift Enter checks it, Alt H opens the next hint, Ctrl or Command slash toggles a line comment, Ctrl or Command Shift F formats the SQL, and Escape closes transient UI.
Cursor at line 1, column 1.
Scenario
Expand multi-table joins deliberately: follow the customer-to-category path through facts, keep line-item grain, and choose captured fact columns for historical metrics.
Multi-Table Joins and Fact Grain / write query
One-sentence task
M19-A07 - Checkpoint - build a multi-table report and prove final key uniqueness at the requested grain. Navigate multiple tables while preserving fact grain and metric source.
Learn mode disclosure
Theory, concept names, full schema help, and progressive hints are available.
Structured output contract
- Result grain
- one category key including uncategorized with line-item uniqueness proof
- Exact columns
- category_key; category_label; final_rows; distinct_line_items; historical_revenue
- Source population
- Use the prompt setup plus FROM, JOIN, WHERE, and subquery predicates as the source population. Visible rows are only examples.
- Grouping
- Group only at the requested output grain: one category key including uncategorized with line-item uniqueness proof.
- Ordering
- order by category_key
- Validation
- select-only; hidden deterministic variants.
Relevant tables
Time and difficulty
- Estimated time
- 11 minutes
- Difficulty
- 4/5
Objective and concepts
Debug the requested SQL output contract for multi-table joins and fact grain using source grain, columns, ordering, and edge-case evidence.
Glossary links
Concept material
SQL Trail treats every query as an evidence trail: identify source grain, transform rows deliberately, then compare output to a shared contract.
A passing query must handle hidden nulls, ties, boundaries, and no-match rows when the contract makes them relevant.
Syntax card
SELECT <requested_columns>
FROM <source_table>
WHERE <source_population_filter>
GROUP BY <result_grain_columns>
ORDER BY <deterministic_tie_breakers>;- <requested_columns> means the exact output columns, aliases, and order from the visible contract.
- <source_population_filter> means the row population definition, not a copied visible-row value.
- <deterministic_tie_breakers> means all ordering and tie rules needed for repeatable output.
Why this works
Adding one verified relationship at a time makes accidental row multiplication visible before metrics are trusted.
Edge cases
Hidden variants preserve nulls, ties, duplicates, boundaries, no-match rows, and alternate row order when those risks apply.
PostgreSQL note
The local engine uses PostgreSQL-compatible syntax, including explicit NULL predicates, deterministic ORDER BY clauses, and transactional grading.
Worked example
SELECT step_no, step_name, row_count FROM (VALUES (1, 'order_items', (SELECT COUNT(*)::int FROM order_items)), (2, 'items_to_orders', (SELECT COUNT(*)::int FROM order_items oi JOIN orders o ON o.order_id = oi.order_id)), (3, 'items_orders_customers', (SELECT COUNT(*)::int FROM order_items oi JOIN orders o ON o.order_id = oi.order_id JOIN customers c ON c.customer_id = o.customer_id)), (4, 'items_orders_products', (SELECT COUNT(*)::int FROM order_items oi JOIN orders o ON o.order_id = oi.order_id JOIN products p ON p.product_id = oi.product_id))) AS row_checks(step_no, step_name, row_count) ORDER BY step_no;Assumptions, dialect notes, and common traps
- Duplicate policy
- Preserve duplicate facts unless the prompt explicitly asks for distinct tuples or set semantics.
- Null policy
- Preserve NULL, empty string, zero, and false as distinct values unless the contract says to display a fallback.
- Tie-breakers
- Use every ordering rule in the contract and end tied business metrics with deterministic secondary keys when needed.
- Zero-related entities
- Preserve requested zero-related, no-match, or complete-period entities and make display fallback explicit.
- Numeric tolerance
- Round only at the requested final stage; hidden checks use the contract precision rather than visible formatting luck.
PostgreSQL-compatible local checks
Queries run in a local PGlite worker with PostgreSQL-style syntax and transactional grading.
- 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.
Opened hints
No hints opened yet.