SELECT
  c.id,
  c.name,
  count(o.id) AS orders
FROM
  customers c
  LEFT JOIN orders o ON o.customer_id = c.id
WHERE
  o.total > 100
GROUP BY
  c.id,
  c.name
ORDER BY
  orders DESC
LIMIT
  10;