SQL: How to find top customers that pay 80% of revenue?

Question

Let's say I have a table TRANSACTIONS:

desc customer_transactions; 
+------------------------------+--------------+------+-----+---------+----------------+
| Field                        | Type         | Null | Key | Default | Extra          |
+------------------------------+--------------+------+-----+---------+----------------+
| id                           | int(11)      | NO   | PRI | NULL    | auto_increment |
| transactionID                | varchar(128) | YES  |     | NULL    |                |
| customerID                   | varchar(128) | YES  |     | NULL    |                |
| amountAuthorized             | DECIMAL(5,2) | YES  |     | NULL    |                |
| createdDatetime              | datetime     | YES  |     | NULL    |                |
+------------------------------+--------------+------+-----+---------+----------------+

This table has records of credit card transactions for a SAAS business for the last 5 years. The business has a typical monthly subscription model, where customers automatically charged based on their plan.

I need to find the top customers that are responsible for 80% of all revenue (per time period). The SAAS business is very uneven, because some customers pay 10/month, others may pay in thousands per month.

I will add a "time period" filter later, just need help with aggregation.

I want to generate a report where I only select the customers that generated 80% of revenue in this format:

+------------+-------+
| customerID | Total | 
+------------+-------+

Not sure why this question was "on hold". I just need help writing a query and do not have enough experience with SQL. Basically, the title of the question states what is needed here:

I need to list customers and their corresponding totals, however, only need to select those customers that make up 80% of total revenue. The report needs to aggregate a total per customer.

Using MariaDB version 10.3.9

Answer 1

This is the kind of thing you need to use window functions for.

WITH 
    -- define some sample data, 
    -- where the sum total of amountAuthorized is 10,000
    customer_transactions(  `id`, transactionID, customerID, 
                            amountAuthorized, createdDatetime) AS
    (
                    SELECT 1, 1, 1, 5000, '2018-08-01'
        UNION ALL   SELECT 2, 2, 2, 2000, '2018-08-01'
        UNION ALL   SELECT 3, 3, 3, 1000, '2018-08-01'
        UNION ALL   SELECT 4, 4, 4, 1000, '2018-08-01'
        UNION ALL   SELECT 5, 5, 5, 1000, '2018-08-01'
    )
    -- a query that gives us the running total, sorted to give us the biggest customers first.
    -- note that the additional sorts affect what customers might be returned.
    ,running_totals AS
    (
        SELECT *, SUM(amountAuthorized) OVER (ORDER BY amountAuthorized DESC, createdDatetime DESC, `id`)  AS runningTotal
        FROM customer_transactions
    )
SELECT *
FROM running_totals
WHERE runningTotal <= ( SELECT 0.8 * SUM(amountAuthorized) 
                        FROM customer_transactions)

Note that this takes into account (no pun intended) all data in the table. When you want to only look at a specific time period, you might want to create an intermediate CTE that filters out the dates you want.

Answer 2

You will find that surprisingly close 20% of the customers account for the 80%. See the 80/20 rule .

But, if you don't want to go that direction, you have 2 options:

• Switch to MySQL 8.0 or MariaDB 10.1 in order to use 'windowing' functions; or
• Use @variables to produce a running total, then (in an outer query) grab the desired rows.

Since you are using MariaDB 10.3.9, the windowing seems to be the way to go. But first, you need a separate query (or derived table) that computes the total revenue so you can get 80% of it.

Suggest

 SELECT @revenue80 := 0.8 * SUM(amountAuthorized) 
     FROM customer_transactions

Then use @revenue80 inside the WHERE that Zack suggests.

I see that each amount can be no more than 999.99. Really? Is this a coffee shop?

Answer 3

Use the following:

SELECT 
  ct1.customerID, 
  SUM(ct1.amountAuthorized) as Total, 
  100 * (SUM(ct1.amountAuthorized) / ct3.total_revenue) as percent_revenue 
FROM 
  customer_transactions ct1
CROSS JOIN (SELECT SUM(amountAuthorized) AS total_revenue 
            FROM customer_transactions ct2) AS ct3 
GROUP BY 
  ct1.customerID 
HAVING percent_revenue >= 80