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 / predict result

One-sentence task

M16-A04 - Row-count prediction - estimate parent repetition after joining children. Plan a join path and predict row multiplication before writing JOIN syntax.

Structured output contract

Result grain

one matched customer with count of order child rows after join

Exact columns

customer_id; customer_name; joined_order_rows

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 matched customer with count of order child rows after join.

Ordering

return joined_order_rows; estimate how many joined rows each matched customer produces through orders; order by customer_id

Validation

select-only; hidden deterministic variants.

Relevant tables

Time and difficulty

Estimated time

8 minutes

Difficulty

4/5

Objective and concepts

State 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 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;

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.