Scenario

Retrieve the skill without relying on the lesson label.

Keys, Relationships, Cardinality, and Join Planning / write query

One-sentence task

RD-A06 - Mixed checkpoint: pre-aggregate independent child facts and return one row per parent. Plan a join path and predict row multiplication before writing JOIN syntax.

Structured output contract

Result grain

one row per calendar day

Exact columns

day; event_count

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 row per calendar day.

Ordering

order by day

Validation

select-only; hidden deterministic variants.

Relevant tables

Time and difficulty

Estimated time

11 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

Preserve requested zero-related, no-match, or complete-period entities and make display fallback explicit.

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.