Scenario

Inspect schema metadata and small sample rows to name table grain, true identifiers, relationship keys, and result grain before writing relational SQL.

Tables, Rows, Columns, Keys, and Grain / debug query

One-sentence task

M01-A04 - Relationship preview - identify the child foreign key and parent key. State row grain, distinguish identifiers from labels, and preview relationships.

Structured output contract

Result grain

one relationship preview for orders to customers

Exact columns

child_table; child_column; parent_table; parent_key; optional_fk_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 relationship preview for orders to customers.

Ordering

identify orders as the child table; identify orders.customer_id as the child foreign-key column

Validation

select-only; hidden deterministic variants.

Relevant tables

Time and difficulty

Estimated time

7 minutes

Difficulty

2/5

Objective and concepts

Debug the requested SQL output contract for tables, rows, columns, keys, and 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 'orders' AS child_table, 'customer_id' AS child_column, 'customers' AS parent_table, 'customer_id' AS parent_key, COUNT(*) FILTER (WHERE customer_id IS NULL)::int AS optional_fk_rows FROM orders;

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.

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

Opened hints

No hints opened yet.