Finding the Top Customers by Revenue in a CSV File

The top customers list is the most requested table in business analysis and one of the easiest to get subtly wrong. The query is short, a group-by and a sort, and the answer is only as good as three decisions made before it: what counts as revenue, what a customer is and what to do about ties. This article builds the table in SQL on a CSV of 2 million orders, adds the cumulative share that turns a list into a picture of concentration and goes through the places where the numbers mislead. It is one of the moves in more analysis moves for CSV data, and it builds on grouping and aggregating CSV data in DuckDB.

The ranked list

The core is a group-by and a window function for the rank. Revenue per customer, ranked, with the share of the total that each customer and all those above it account for:

WITH c AS (
  SELECT customer_id, sum(amount) AS revenue, count(*) AS orders
  FROM read_csv('orders-2m.csv') GROUP BY customer_id),
r AS (
  SELECT *, row_number() OVER (ORDER BY revenue DESC) AS rank,
         sum(revenue) OVER (ORDER BY revenue DESC) / sum(revenue) OVER () AS cum_share
  FROM c)
SELECT rank, customer_id, orders, round(revenue, 2) AS revenue, round(100 * cum_share, 3) AS cum_pct
FROM r WHERE rank <= 5 ORDER BY rank;

rank  customer_id  orders  revenue  cum_pct
1     11346        17      2328.48  0.001
2     55526        20      2232.05  0.003
3     12848        17      2129.47  0.004
4     102809       19      2112.01  0.005
5     178331       17      2109.45  0.007

Read it from the left. Customer 11346 placed 17 orders worth 2,328.48 and alone accounts for 0.001 percent of revenue; the top five together are 0.007 percent. In a business with real concentration, the first row of this table would show a few percent. The shape here, a slowly declining list with no dominant head, tells you the revenue is spread across a quarter of a million buyers.

row_number() gives every customer a unique rank, breaking ties arbitrarily. If two customers tie on revenue, one is placed before the other for no reason, and re-running the query could swap them. For a list people will read, add a tiebreaker (ORDER BY revenue DESC, customer_id) to make it stable, or use rank(), which gives tied customers the same rank and leaves a gap after them, or dense_rank(), which leaves no gap. For a top-ten list with a tie at tenth place, rank() <= 10 returns eleven rows, and that is the honest answer.

The concentration curve

A list of the top five says little. The question behind it is usually "how dependent are we on a few accounts", and the answer is the cumulative share at 1 percent, 10 percent and 20 percent of customers. Compute those points directly:

WITH c AS (SELECT customer_id, sum(amount) AS revenue FROM read_csv('orders-2m.csv') GROUP BY 1),
r AS (SELECT revenue, row_number() OVER (ORDER BY revenue DESC) AS rank,
             count(*) OVER () AS n, sum(revenue) OVER () AS total FROM c)
SELECT n AS customers,
       round(100 * sum(revenue) FILTER (WHERE rank <= n * 0.1) / any_value(total), 1) AS top10pct_share,
       round(100 * sum(revenue) FILTER (WHERE rank <= n * 0.01) / any_value(total), 1) AS top1pct_share
FROM r GROUP BY n;

customers  top10pct_share  top1pct_share
249916     18.5            2.4

The top 10 percent of customers, 24,992 of them, produced 18.5 percent of revenue, and the top 1 percent produced 2.4 percent. If every customer contributed equally those figures would be 10 and 1, so this file is slightly concentrated and mostly even. Real businesses, particularly B2B ones, show curves where the top 10 percent bring half of revenue or more, and the interesting number is how far from the diagonal the curve sits. A common summary is the Gini coefficient, but the two or three points above are easier to explain and good enough to act on.

The same table by orders and by customers acquired in a period, rather than overall, answers other questions: whether the concentration is rising, and whether the top customers this year are the top customers from last year. Compute the list for two periods and join on customer_id to see how many of the top 100 appear in both.

Decide what revenue is

The query above summed amount for every order. In this file, orders have five statuses, each holding about 20 percent of the money: failed 32.0 million, paid 32.0 million, pending 32.1 million, refunded 32.1 million and shipped 32.0 million. A top-customer list on all of it ranks buyers whose orders failed alongside buyers who paid. Almost certainly the list you want counts only paid and shipped orders:

SELECT customer_id, sum(amount) AS revenue
FROM read_csv('orders-2m.csv') WHERE status IN ('paid', 'shipped')
GROUP BY customer_id ORDER BY revenue DESC LIMIT 10;

The restriction moves the total from 160.2 million to 64.0 million and changes who is on the list, since a customer with several refunded high-value orders drops sharply. Refunds deserve explicit thought: if the file records a refund as a status on the original order, as here, filtering it out removes the sale; if refunds are separate rows with negative amounts, summing everything gives net revenue, and filtering only on amount > 0 would give gross. State which one the table is, in the column heading: net_revenue or paid_revenue. More analysis moves for CSV data discusses how a definition changes the answer more than any method does.

Is a customer a customer?

The ranking treats each customer_id as one buyer, and the data may not agree. One company can appear under several IDs (a guest checkout, an old account and a new one), which splits its revenue and lowers its rank. Several people can share one ID, which merges them, as with a family account or a purchasing portal. Emails with different capitalization, a changed company name after an acquisition and a customer who moved country can all produce duplicates. Before presenting the list, check the top 50 for near-duplicates: sort the names or emails and look at neighbors, and group by a normalized key (lowercased email, a domain for business accounts) to see whether the order changes (removing duplicate rows in a CSV covers the exact-duplicate case, and fuzzier matching is a separate job).

Also check the key itself. IDs that were read as numbers lose leading zeros, so 00123 and 123 merge (why a CSV file has no types). And check for placeholder values: a customer_id of 0, -1 or unknown that stands for "anonymous" will be the top customer by a wide margin, and it is not a customer at all. In the test file, the top customer had 17 orders, so there was no such row, and on a real file the first line of the output is where to look.

Per-group rankings

"Top three customers per country" is the same query with a partition. row_number() OVER (PARTITION BY country ORDER BY revenue DESC) ranks within each country, and DuckDB's QUALIFY filters on the window result without a subquery:

SELECT country, customer_id, round(sum(amount), 2) AS revenue
FROM read_csv('orders-2m.csv') WHERE status IN ('paid', 'shipped')
GROUP BY country, customer_id
QUALIFY row_number() OVER (PARTITION BY country ORDER BY sum(amount) DESC) <= 3
ORDER BY country, revenue DESC;

That returns up to three rows for each of the seven countries; the first rows were:

country  customer_id  revenue
BR       242881       765.76
BR       142490       759.37
BR       202656       750.18
DE       82230        855.39
DE       40682        819.12
DE       35258        753.23

The top paid-and-shipped customer in Germany spent 855.39, and the Brazilian leader 765.76. A customer that orders from two countries appears in both, which may or may not be what you intend; decide whether the unit is a customer or a customer in a country. Ranking rows within groups in CSV data covers the pattern in other databases, where QUALIFY is replaced by a subquery.

When customer IDs are not customers

Stop trusting the list when the unit of the ID is not the unit of the decision. A file of orders keyed by a session or a device ID is a list of devices. A file where the customer column is the invoicing entity, a distributor, ranks distributors, and the people who actually buy are behind it. A file with a short history, two weeks of orders, ranks whoever happened to order in that window, and the long-term top customers may be absent. And a file with very uneven order sizes needs a second look at the median order value and the order count, since a customer with one huge order is different from one who orders weekly, and the revenue column alone does not separate them.

Pair the list with the number of orders and the date of the first and last order, so a reader can see tenure and recency. The window functions are documented in DuckDB's window function reference, and the QUALIFY clause in its QUALIFY page.