Context

At Instacart, many operational analyses in Caper Cart and fulfillment reporting require combining order-level data with shopper and retailer attributes. Interviewers ask this to check whether you understand how joins affect row counts, missing data, and downstream metrics.

Core question

Explain how you would join orders, shoppers, and retailers tables to support an analysis. In your answer, describe:

1. Which keys you would join on
2. When to use INNER JOIN versus LEFT JOIN
3. How you would avoid duplicate rows or inflated aggregates
4. What metrics you could compute after the join

Scope guidance

The interviewer is not looking for advanced SQL syntax here. They want a clear explanation of join logic, table relationships, common pitfalls, and how your join choice changes the business result.

Join keys and table relationships

The orders table is usually the fact table because each row represents one order event. It commonly joins to shoppers on orders.shopper_id = shoppers.shopper_id and to retailers on orders.retailer_id = retailers.retailer_id.

SELECT o.order_id, s.shopper_name, r.retailer_name
FROM orders o
LEFT JOIN shoppers s ON o.shopper_id = s.shopper_id
LEFT JOIN retailers r ON o.retailer_id = r.retailer_id;

INNER JOIN vs LEFT JOIN

Use INNER JOIN when you only want orders that have valid matches in both dimension tables. Use LEFT JOIN when you want to preserve all orders, even if shopper or retailer reference data is missing, which is often safer for operational reporting.

SELECT o.order_id, s.shopper_name
FROM orders o
LEFT JOIN shoppers s ON o.shopper_id = s.shopper_id;

Aggregation after joins

After joining, you can group by shopper or retailer attributes to compute metrics like order count, total basket value, or average fulfillment time. You must confirm the joined tables are one-to-one with the fact table at the chosen grain, otherwise aggregates can be overstated.

SELECT r.retailer_name, COUNT(*) AS order_count
FROM orders o
JOIN retailers r ON o.retailer_id = r.retailer_id
GROUP BY r.retailer_name;

Duplicate row risk

A join can multiply rows if the right-side table contains multiple matches for a single key or if you join at the wrong grain. Before aggregating, verify primary keys and understand whether each join is one-to-one, one-to-many, or many-to-many.

SELECT o.order_id, COUNT(*) AS joined_rows
FROM orders o
JOIN shoppers s ON o.shopper_id = s.shopper_id
GROUP BY o.order_id;