How to find your top customers by revenue in SQL

Ranking customers by what they have spent is a two-line query, and the two-line version is wrong often enough to be worth ten minutes.

The query

Assume customers and an orders table with customer_id, total and created_at.

PostgreSQL

SELECT
  c.id,
  c.name,
  SUM(o.total)  AS revenue,
  COUNT(o.id)   AS orders,
  MAX(o.created_at) AS last_order
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'completed'
GROUP BY c.id, c.name
ORDER BY revenue DESC
LIMIT 20;

MySQL

The same query works, and on 5.7 you may need c.name in the GROUP BY even when it is functionally dependent on c.id, depending on whether ONLY_FULL_GROUP_BY is enabled. Including it, as above, is portable either way.

What the simple version gets wrong

Ties at the cutoff

LIMIT 20 cuts at exactly twenty rows. If the twentieth and twenty-first customers spent the same amount, which one appears is arbitrary, and it can change between runs. When the list drives a decision, such as who gets a call from an account manager, use a ranking function instead:

-- PostgreSQL, and MySQL 8.0
SELECT * FROM (
  SELECT
    c.id,
    c.name,
    SUM(o.total) AS revenue,
    RANK() OVER (ORDER BY SUM(o.total) DESC) AS rnk
  FROM customers c
  JOIN orders o ON o.customer_id = c.id
  WHERE o.status = 'completed'
  GROUP BY c.id, c.name
) ranked
WHERE rnk <= 20;

RANK() returns every customer tied at the boundary, so you may get twenty-one rows. That is the point.

Lifetime revenue answers a different question

A customer who spent heavily for two years and stopped six months ago will outrank one who started last quarter and is growing fast. Both are “top customers” and they need opposite actions. Decide which question you are asking and filter accordingly:

WHERE o.status = 'completed'
  AND o.created_at >= CURRENT_DATE - INTERVAL '90 days'   -- PostgreSQL
  AND o.created_at >= CURDATE() - INTERVAL 90 DAY         -- MySQL

The last_order column in the first query exists for exactly this reason: it shows at a glance whether a high lifetime total is still live.

The inner join hides everyone with no orders

JOIN orders silently drops customers who have never ordered. For a top-20 list that is harmless. The moment you reuse the query to count customers or compute an average, it is not, because the denominator is now only customers who bought something. Switch to LEFT JOIN and COALESCE(SUM(o.total), 0) when the zero-revenue rows matter.

Refunds

As with any revenue figure, SUM(total) includes orders that were refunded unless the status filter excludes them. A customer who placed one enormous order and sent it all back can otherwise sit at the top of the list.

Making it faster

This query reads every matching order row. On a large table, an index on orders (customer_id, status, created_at) covers the join, the filter and the date range in one pass. Check the plan before adding it: if the table is small, the index costs more to maintain than the query saves.

Or just ask for it

The query above assumes status = 'completed' means paid, that total is the final amount after discounts, and that one row in orders is one purchase. Those assumptions differ in every schema, and they are what decides whether the ranking is right.

ByeSQL reads your schema and writes the query against it, so “top 20 customers by revenue in the last quarter” returns the list and the SQL that produced it.

→ How to get revenue by month in SQL