Window Function vs N+1 Query

-- ❌ The N+1 anti-pattern (in your app):
--   users = SELECT * FROM users;
--   for u in users:
--     count = SELECT COUNT(*) FROM orders WHERE user_id = u.id;  -- N more queries!

-- ✓ One query with a JOIN + GROUP BY
SELECT u.id, u.email, COUNT(o.id) AS order_count
FROM   users u
LEFT   JOIN orders o ON o.user_id = u.id
GROUP  BY u.id, u.email;

-- ✓ Same idea with a correlated subquery (simpler when you only need one aggregate)
SELECT u.id, u.email,
       (SELECT COUNT(*) FROM orders WHERE user_id = u.id) AS order_count
FROM   users u;

-- ✓ Show user + recent orders inline using JSON_AGG (PostgreSQL)
SELECT u.id, u.email,
       (SELECT json_agg(o ORDER BY o.created_at DESC)
        FROM (SELECT id, amount, created_at FROM orders
              WHERE user_id = u.id ORDER BY created_at DESC LIMIT 5) o
       ) AS recent_orders
FROM   users u;