I'm searching for a solution to this:
A customer can place a order with a price x at a time y. A customer can have unlimited number of orders.
I want to get the z top-customers with their ordering-amount within a time-frame (e.g.a month).
I'm stuck at the summing and the ordering / filtering to the top z.
Can you help me out? Thanks!
Given an orders table with customer_id, amount and time columns, you should be able to do something like this:
SELECT customer_id, sum(amount) AS total
FROM orders
GROUP BY customer_id
WHERE time BETWEEN start AND end
ORDER BY total DESCENDING
LIMIT 3
This is psuedo code, but I'd use something like:
select sum(order_total), client_id
from orders
where order_date between X and Y
group by client_id
order by sum(order_total)
limit 0, 10