Metric

Average basket

Revenue divided by the number of orders, over a period.

Sum each order first, then average the orders. Say which orders count: cancelled ones usually do not.

The trap

SELECT ROUND(AVG(l.quantity * l.unit_price), 2) AS average_basket
FROM orders o
JOIN order_lines l ON l.order_id = o.order_id
WHERE o.status <> 'cancelled';

average_basket
31.06

average_basket

31.06

Try it in Randori

The fix

SELECT ROUND(SUM(l.quantity * l.unit_price) / COUNT(DISTINCT o.order_id), 2) AS average_basket
FROM orders o
JOIN order_lines l ON l.order_id = o.order_id
WHERE o.status <> 'cancelled';

average_basket
68.25

average_basket

68.25

Try it in Randori

Practise:Average basket per month