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 / write query

One-sentence task

M18-A05 - Write - report every customer with qualifying-order count including zero. Preserve unmatched left rows and calculate zero-related counts correctly.

Structured output contract

Result grain

one customer row with a zero-capable completed order count

Exact columns

customer_id; customer_name; completed_order_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 customer row with a zero-capable completed order count.

Ordering

return completed_order_count; preserve every customer including zero completed orders; count only completed orders; order by customer_id

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 left join, missing relationships, and zero counts 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(*)::int AS left_join_rows, COUNT(o.order_id)::int AS matched_order_count FROM customers c LEFT 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

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.

• LEFT preserves the written left side: The physically smaller table is assumed to be preserved regardless of query order. Repair: Put the parent population that must survive before LEFT JOIN.
• Right filters in WHERE erase no-matches: Rows with no qualifying child disappear even though the prompt asks for zeroes. Repair: Move right-side qualification into ON or into aggregate FILTER depending on the requested metric.
• COUNT star is not a zero-child count: A parent with no child reports 1 because the NULL-extended preserved row is counted. Repair: Count the right-side key when the metric is number of matched children.
• Nullable attributes do not prove absence: A matched child with a NULL delivered_at or other attribute is treated as if no row matched. Repair: Use the right-side key NULL test for anti-joins and absence checks.

Opened hints

No hints opened yet.