Mode disclosure
All modes use one coherent workspace; only disclosure and guidance change. Practice mode uses a concise prompt, normal schema, optional hints, and the same local Run and Check pipeline.
LEFT JOIN, Missing Relationships, and Zero Counts / predict result
M18-A01 - Prediction - compare inner and left join outputs on unmatched rows
M18-A01 - Prediction - compare inner and left join outputs on unmatched rows. Preserve unmatched left rows and calculate zero-related counts correctly.
- Result grain
- one comparison row per join type for customer to order matching
- Exact columns
- join_type; joined_rows; unmatched_customer_rows
SQL editor shortcuts: Ctrl or Command Enter runs the query, Ctrl or Command Shift Enter checks it, Alt H opens the next hint, Ctrl or Command slash toggles a line comment, Ctrl or Command Shift F formats the SQL, and Escape closes transient UI.
Cursor at line 1, column 1.
Scenario
Use LEFT JOIN to preserve unmatched parent rows: place right-side filters intentionally, count right-side keys for zeroes, and test right-side keys for anti-joins.
LEFT JOIN, Missing Relationships, and Zero Counts / predict result
One-sentence task
M18-A01 - Prediction - compare inner and left join outputs on unmatched rows. Preserve unmatched left rows and calculate zero-related counts correctly.
Practice mode disclosure
Concise prompt, normal schema help, optional hints, and the same checker workspace are available.
Structured output contract
- Result grain
- one comparison row per join type for customer to order matching
- Exact columns
- join_type; joined_rows; unmatched_customer_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 comparison row per join type for customer to order matching.
- Ordering
- order by join_type
- Validation
- select-only; hidden deterministic variants.
Relevant tables
Time and difficulty
- Estimated time
- 5 minutes
- Difficulty
- 3/5
Objective and concepts
Practice mode keeps the prompt concise; open hints only if you choose guided help.
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.
Opened hints
No hints opened yet.