LATERAL JOIN — Top-N per Group

Get the top 3 most recent orders per customer using LATERAL. Lineage shows the lateral subquery as a stage between customers and the result.

The lineage

The SQL

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  email TEXT
);

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT REFERENCES customers(customer_id),
  amount NUMERIC(10,2),
  ordered_at TIMESTAMP
);

CREATE VIEW v_recent_orders AS
SELECT c.customer_id, c.email, o.order_id, o.amount, o.ordered_at
FROM customers c
JOIN LATERAL (
  SELECT order_id, amount, ordered_at
  FROM orders
  WHERE customer_id = c.customer_id
  ORDER BY ordered_at DESC
  LIMIT 3
) o ON TRUE;

What the lineage shows

• v_recent_orders.amount traces to orders.amount through the LATERAL stage.
• v_recent_orders.email traces to customers.email directly.
• LATERAL is treated as a per-row stage between the outer and inner sources.