Invoer:
select c.id,c.name,count(o.id) as orders,sum(o.total) as revenue from customers c left join orders o on o.customer_id=c.id and o.status='paid' where c.created_at>='2024-01-01' and (c.country='DE' or c.country='AT') group by c.id,c.name having count(o.id)>0 order by revenue desc limit 10;
Resultaat:
SELECT
c.id,
c.name,
COUNT(o.id) AS orders,
SUM(o.total) AS revenue
FROM
customers c
LEFT JOIN orders o ON o.customer_id = c.id
AND o.status = 'paid'
WHERE
c.created_at >= '2024-01-01'
AND (c.country = 'DE' OR c.country = 'AT')
GROUP BY
c.id,
c.name
HAVING
COUNT(o.id) > 0
ORDER BY
revenue DESC
LIMIT 10;Geminimaliseerd, voor code, configuratiebestanden of logs:
SELECT c.id,c.name,COUNT(o.id) AS orders,SUM(o.total) AS revenue FROM customers c LEFT JOIN orders o ON o.customer_id=c.id AND o.status='paid' WHERE c.created_at>='2024-01-01' AND (c.country='DE' OR c.country='AT') GROUP BY c.id,c.name HAVING COUNT(o.id)>0 ORDER BY revenue DESC LIMIT 10;
Dit bespaart 44 van 332 tekens (13 %); de query blijft hetzelfde.
Voorkeursbron bij Google
