Calculate decile from frequency in MySQL


Sometimes you may want to calculate decile from frequency or counts in MySQL based on. E.g, to rank customers based on number of purchases or orders instead of based on total sales

Calculate Decile from Frequency

There are no functions for it yet. Here’s a ready made query to do it.

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

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

Here’s a query you can use to calculate decile from frequency or count in MySQL. Just replace the columns – user_id, sales and table – order. It counts the number of orders for each user. Then it ranks them on number of orders. 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,count(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 number of orders for each user in the table and want to directly use the table to calculate decile from frequency or count, 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     |    3     |
|     4     |    2     |
|     3     |    1     |
+-----------+----------+
deciles
+-----------+----------+---------+---------------+
|  user_id  |   total  |  rank   |     decile    |
+-----------+----------+---------+---------------+
|     1     |    3     |    3    |       3       |
|     4     |    2     |    2    |       7       |
|     3     |    1     |    1    |      10       |
+-----------+----------+---------+---------------+

 

mm

About Sreeram Sreenivasan

Sreeram Sreenivasan is the Founder of Ubiq, a business dashboard & reporting platform for small & medium businesses. Ubiq makes it easy to build business dashboards & reports for your business. Try it for free today!