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 / guided completion

One-sentence task

M19-A01 - Path planning - map customer to category through order and line facts. Navigate multiple tables while preserving fact grain and metric source.

Structured output contract

Result grain

one join-path planning step from customer to product category

Exact columns

step_no; from_table; to_table; join_condition; output_grain

Source population

Use the prompt setup plus FROM, JOIN, WHERE, and subquery predicates as the source population. Visible rows are only examples.

Grouping

Do not collapse rows unless the contract explicitly asks for aggregation, distinct tuples, or set semantics.

Ordering

map customer to category through orders and line facts; order by step_no

Validation

select-only; hidden deterministic variants.

Relevant tables

Time and difficulty

Estimated time

5 minutes

Difficulty

3/5

Objective and concepts

State the requested SQL output contract for multi-table joins and fact grain using source grain, columns, ordering, and edge-case evidence.

SELECT <requested_columns>
FROM <source_table>
WHERE <source_population_filter>
GROUP BY <result_grain_columns>
ORDER BY <deterministic_tie_breakers>;

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;

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

Do not invent zero rows unless the contract asks for preserved parents, missing entities, or complete periods.

Numeric tolerance

Use exact semantic comparison unless the activity explicitly declares a numeric tolerance.

• 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.