-- Bad use of aggregation
SELECT
  users.id,
  users.email,
  users.name,
  MAX(users.created_at) AS created_at, -- not necessary
  COUNT(orders.id) AS orders_count
FROM orders
LEFT JOIN users
       ON users.id = orders.user_id
GROUP BY users.id, users.email, users.name -- only users.id should be in here

-- Good but using sub-query
SELECT
  users.id,
  users.email,
  users.name,
  users.created_at,
  user_orders.orders_count
FROM ( -- subqueries are hard to read
  SELECT
    orders.user_id,
    COUNT(orders.id) AS orders_count
  FROM orders
  GROUP BY orders.user_id
) user_orders
LEFT JOIN users
       ON users.id = user_orders.user_id

-- Better
WITH user_orders AS (
  SELECT
    orders.user_id,
    COUNT(orders.id) AS orders_count
  FROM orders
  GROUP BY orders.user_id
)
SELECT
  users.id,
  users.email,
  users.name,
  users.created_at,
  user_orders.orders_count
FROM user_orders
LEFT JOIN users
       ON users.id = user_orders.user_id