Lesson and practical work
Professional outcomes
- Choose INNER or LEFT JOIN based on the analytical population.
- State relationship cardinality before combining tables.
- Diagnose duplicated measures after one-to-many or many-to-many joins.
- Use pre-aggregation, bridge logic, and reconciliation to protect metrics.
Need a refresher?JOIN type is a population decisionView concise foundation
INNER JOIN keeps matched rows. LEFT JOIN preserves all rows from the left table. Neither is universally correct. The choice depends on whether unmatched entities belong in the analysis.
Revenue rose 18% after adding product category
The original report used one row per order. Product category requires order_items, creating one row per order line. Summing order_total now repeats the full order value once for each line.
Keep measures at their natural grain
WITH order_metrics AS (
SELECT order_id, customer_id, net_revenue
FROM orders
WHERE status = 'completed'
), item_metrics AS (
SELECT order_id, product_id, SUM(quantity * net_unit_price) AS line_revenue
FROM order_items
GROUP BY order_id, product_id
)
SELECT p.category, SUM(i.line_revenue) AS category_revenue
FROM item_metrics i
JOIN products p ON p.product_id = i.product_id
GROUP BY p.category;Category revenue should come from line-level values. Order-level net revenue is appropriate for order metrics. Keep separate aggregates and reconcile line revenue to order revenue, allowing for documented differences such as order-level discounts.
What the result means—and does not mean
The inflated result was a grain error, not evidence of growth. Correcting it requires understanding measure ownership. DISTINCT on order_total is not a safe repair because different legitimate orders can have the same value.
Analyst's checklist
- Record expected cardinality for every join.
- Compare rows and distinct keys before and after.
- Reconcile measures at their source grain.
- Inspect unmatched keys in both directions.
- Test known multi-line and duplicate-key cases.
Common mistakes and failure modes
- Fixing duplication with SELECT DISTINCT.
- Summing order totals at line grain.
- Using INNER JOIN and silently losing entities without matches.
- Assuming a declared key is actually unique.
Challenge yourself
A customer-revenue report drops customers with no orders after the analyst adds a WHERE condition on the orders table to a LEFT JOIN query.
- Why did LEFT JOIN behave like INNER JOIN?
- Where should the order condition go?
- What should revenue show for customers without orders?
Show hint
A WHERE predicate on a nullable right-table field removes unmatched rows.
Show analytical guidance
Place eligible-order conditions in the JOIN predicate when preserving all customers, then use COALESCE for zero revenue where that interpretation is appropriate.
How this may appear in an interview
How do you prevent duplicate rows from inflating a metric after JOINs?
What a strong candidate should communicate
State grain and cardinality, test key uniqueness, compare row counts and distinct keys, aggregate at natural grain, and reconcile totals. Explain why DISTINCT can conceal rather than solve the model problem.
When would a many-to-many relationship be legitimate?
Where this skill becomes evidence
Add a relationship audit showing expected cardinality, unmatched counts, pre/post rows, and measure reconciliation. This is powerful evidence of professional SQL practice.
Starter SQL
SELECT
o.order_id,
o.net_revenue,
i.product_id
FROM orders o
LEFT JOIN order_items i ON i.order_id = o.order_id;Test your analytical judgment
Answer before opening the guidance. More than one defensible approach may exist.
Why is DISTINCT not a reliable fix for repeated order totals?
Show guidance
How does a WHERE condition change a LEFT JOIN population?
Show guidance
Design a reconciliation between order and line revenue.
Show guidance
Move from reviewing expert reasoning to performing the analysis.
Guided practice can add the synthetic dataset, multi-step assignment, validation checks, solution comparison, project workflow, and progress tracking.
- Work with the data
- Make and defend assumptions
- Validate the result
- Build portfolio evidence