Reason About Composite Join Keys and Predicate Placement

Scenario

You are analyzing merchant order fulfillment across international marketplaces. You are working with two tables:

• orders: order_id, marketplace_id, seller_id, order_date
• shipments: shipment_id, marketplace_id, order_id, carrier, is_expedited (BOOLEAN), shipped_at

Tasks

1. Conceptual Analysis

Compare the behavior and row counts of the following two queries:

-- Query A
SELECT o.order_id, o.marketplace_id, s.carrier
FROM orders o
LEFT JOIN shipments s
  ON o.order_id = s.order_id
 AND o.marketplace_id = s.marketplace_id
 AND s.is_expedited = TRUE;

-- Query B
SELECT o.order_id, o.marketplace_id, s.carrier
FROM orders o
LEFT JOIN shipments s
  ON o.order_id = s.order_id
 AND o.marketplace_id = s.marketplace_id
WHERE s.is_expedited = TRUE;

Explain how Query B fundamentally alters the LEFT JOIN semantics, and describe what happens to orders with no matching expedited shipments.

2. Write a Safe Query

Write a SQL query that fulfills the following requirements:

• Return all rows from orders with order_date >= '2023-01-01'.
• Join with shipments on the composite key (order_id, marketplace_id).
• Include expedited shipment details (carrier, shipped_at). If an order has no expedited shipment, still return the order with NULL for shipment fields.
• Prevent row multiplication: An order can have multiple expedited split shipments. For each order, return at most one matching shipment: the one with the latest shipped_at timestamp.

Input Example

orders:

shipments:

Output Example

Edge Cases to Consider

• Orders that have only non-expedited shipments.
• Orders that have zero shipments.
• Multiple shipments with the exact same shipped_at timestamp (handle deterministically via tie-breaker such as shipment_id).