Scenario

Plan joins before writing them: identify keys, classify cardinality, name bridge grain, predict parent repetition, and diagnose Cartesian row-count evidence.

Keys, Relationships, Cardinality, and Join Planning / compare queries

One-sentence task

M16-A05 - Bridge reasoning - state the grain of order_items and where quantity belongs. Plan a join path and predict row multiplication before writing JOIN syntax.

Structured output contract

Result grain

one bridge-planning note per concept

Exact columns

concept; correct_table; reason

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

identify order_items as the bridge grain; order by concept

Validation

select-only; hidden deterministic variants.

Relevant tables

Time and difficulty

Estimated time

9 minutes

Difficulty

4/5

Objective and concepts

Debug the requested SQL output contract for keys, relationships, cardinality, and join planning 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 c.customer_id, c.customer_name, COUNT(o.order_id)::int AS joined_order_rows FROM customers c JOIN orders o ON o.customer_id = c.customer_id GROUP BY c.customer_id, c.customer_name ORDER BY c.customer_id;

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.

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

Opened hints

No hints opened yet.