Revenue Without Double Counting

#974

The customer revenue report started overstating totals when an order has multiple line items. Count only payments with status = 'success', without multiplying them by product lines. Return id, name, and revenue; users with no successful payments get 0, sorted by id.

Buggy query

SELECT u.id, u.name, COALESCE(SUM(p.amount), 0) AS revenue
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
LEFT JOIN order_items oi ON oi.order_id = o.id
LEFT JOIN payments p ON p.order_id = o.id AND p.status = 'success'
GROUP BY u.id, u.name
ORDER BY u.id;

Vzorec pričakovanega izhoda

Tako izgleda pravilen odgovor — ima svoje število vrstic in se ni treba ujemati s tabelami sheme.

id	name	revenue
1	Alice	73
2	Bob	0
3	Carol	6

Prijavite se za oddajo

Rezultat vaše poizvedbe se bo prikazal tukaj