Genera una consulta de dos tablas con LEFT JOIN con subquery en la clausula FROM. En la clausula SELECT hay un CASE.

T * FROM customers LEFT JOIN (SELECT * FROM orders WHERE customer_id = customers.id) AS orders ON customers.id = orders.customer_id

A:

You can use a subquery to get the count of orders for each customer, and then join that to the customers table.
SELECT c.*, o.count
FROM customers c
LEFT JOIN (SELECT customer_id, COUNT(