MySQL：在一个时间范围内找到前三名客户

Question

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!

Answer 1

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