SQL LATERAL Join Examples

-- Top 3 orders per customer using LATERAL
SELECT c.id, c.name, o.id AS order_id, o.amount, o.created_at
FROM customers c
CROSS JOIN LATERAL (
    SELECT id, amount, created_at
    FROM orders
    WHERE customer_id = c.id
    ORDER BY amount DESC
    LIMIT 3
) o;

-- Latest order per customer
SELECT c.id, c.name, latest.amount, latest.created_at
FROM customers c
LEFT JOIN LATERAL (
    SELECT amount, created_at
    FROM orders
    WHERE customer_id = c.id
    ORDER BY created_at DESC
    LIMIT 1
) latest ON TRUE;

-- Running total with LATERAL
SELECT
    o.id,
    o.amount,
    o.created_at,
    running.total AS running_total
FROM orders o
CROSS JOIN LATERAL (
    SELECT SUM(amount) AS total
    FROM orders
    WHERE created_at <= o.created_at
) running
ORDER BY o.created_at;

-- Expand array/JSON elements
SELECT
    p.id,
    p.name,
    t.tag
FROM products p
CROSS JOIN LATERAL UNNEST(p.tags) AS t(tag);

-- Computed columns referencing previous columns
SELECT *
FROM orders,
LATERAL (
    SELECT
        amount * 0.1 AS tax,
        amount * 0.9 AS net
) calc,
LATERAL (
    SELECT calc.net - shipping_cost AS profit
) profit_calc;