LATERAL Join for Top-N Per Group

-- Get latest 3 posts per author (efficient with LATERAL)
SELECT a.id AS author_id, a.name, p.*
FROM authors a
CROSS JOIN LATERAL (
  SELECT id, title, created_at
  FROM posts
  WHERE posts.author_id = a.id
  ORDER BY created_at DESC
  LIMIT 3
) p;

-- Top product per category with details
SELECT c.name AS category, t.*
FROM categories c
CROSS JOIN LATERAL (
  SELECT p.name, p.price, p.rating
  FROM products p
  WHERE p.category_id = c.id
  ORDER BY p.rating DESC
  LIMIT 1
) t;

-- LATERAL with aggregation: stats per user
SELECT u.id, u.name, s.total_orders, s.total_spent
FROM users u
CROSS JOIN LATERAL (
  SELECT
    count(*) AS total_orders,
    coalesce(sum(total), 0) AS total_spent
  FROM orders
  WHERE orders.user_id = u.id
    AND orders.created_at >= CURRENT_DATE - INTERVAL '30 days'
) s
WHERE s.total_orders > 0
ORDER BY s.total_spent DESC;

-- Ensure index exists for the correlated subquery
CREATE INDEX idx_posts_author_date
  ON posts (author_id, created_at DESC);