Calculate decile in MySQL based on totals


You can calculate decile in MySQL to identify best customers. Basically, you rank and group customers into 10 groups based on total sales. Based on the result, you can come up with more focused, relevant retention initiatives. Here’s a ready made query to do it.

Calculate Decile in MySQL

E.g, You have a table orders which contains all product orders for each user. You want to calculate decile in MySQL.

order
+-----------+------------+----------+
|  user_id  |   product  |   sales  |
+-----------+------------+----------+
|     1     |     Soap   |    10    |
|     4     |   Perfume  |   100    |
|     1     |   Noodles  |   20     |
|     3     |     Deo    |   200    |
+-----------+------------+----------+
deciles
+-----------+----------+---------+---------------+
|  user_id  |   total  |  rank   |     decile    |
+-----------+----------+---------+---------------+
|     1     |    30    |    3    |       3       |
|     4     |   100    |    2    |       7       |
|     3     |   200    |    1    |      10       |
+-----------+----------+---------+---------------+

Here’s a query you can use to calculate decile in MySQL based on totals. Just replace the columns – user_id, sales and table – order. It aggregates the total sales for each user. Then it ranks them on total sales. Finally, it calculates decile using rank.

select user_id,total,rank,round(10*(cnt-rank+1)/cnt,0) as decile from   
(SELECT  user_id,total,@curRank := @curRank + 1 AS rank
FROM      (select user_id,sum(sales) as total from `order` group by user_id)
p, (SELECT @curRank := 0) r
ORDER BY  total desc ) as dt,(select count(distinct user_id) as cnt from
`order`) as ct

If you already have total sales for each user in the table and want to directly use the table to calculate decile, here’s a query

select user_id,total,rank,round(10*(cnt-rank+1)/cnt,0) as decile from   
(SELECT  user_id,total,@curRank := @curRank + 1 AS rank
FROM   `order`
p, (SELECT @curRank := 0) r
ORDER BY  total desc ) as dt,(select count(distinct user_id) as cnt from
`order`) as ct
order
+-----------+----------+
|  user_id  |   total  |
+-----------+----------+
|     1     |    30    |
|     4     |   100    |
|     3     |   200    |
+-----------+----------+
deciles
+-----------+----------+---------+---------------+
|  user_id  |   total  |  rank   |     decile    |
+-----------+----------+---------+---------------+
|     1     |    30    |    3    |        3      |
|     4     |   100    |    2    |        7      |
|     3     |   200    |    1    |       10      |
+-----------+----------+---------+---------------+

 

mm

About Ubiq

Ubiq is a business dashboard & reporting platform for SMBs & enterprises. Ubiq makes it easy to build business dashboards & reports for your business. Try it for free today!