Top 3 most recent orders per customer (ROW_NUMBER and LATERAL)

-- ======================================================================
-- Q: Write a query that returns the 3 most recent orders of each customer.
--
-- The classic top-N per group is ROW_NUMBER with PARTITION BY. I add a second
-- key to the ORDER BY (id) so the result is deterministic on ties. If there are
-- few customers and many orders, LATERAL with an index does an index scan with
-- LIMIT per customer instead of sorting the whole table. In Django the
-- equivalent is Window(RowNumber(), partition_by=...), and since Django 4.2 you
-- can filter directly on a window annotation.
-- ======================================================================

SELECT customer_id, id, created_at, total_cents
FROM (
    SELECT o.*,
           ROW_NUMBER() OVER (PARTITION BY customer_id
                              ORDER BY created_at DESC, id DESC) AS rn
    FROM orders o
    WHERE o.status <> 'cancelled'
) t
WHERE rn <= 3
ORDER BY customer_id, rn;

-- Alternative with LATERAL: very fast when an index (customer_id, created_at DESC) exists
SELECT c.id, o.id, o.created_at
FROM customers c
CROSS JOIN LATERAL (
    SELECT id, created_at FROM orders
    WHERE customer_id = c.id
    ORDER BY created_at DESC LIMIT 3
) o;