Trying to spot the top 70 percent of purchases using mysql

Question

I have a scenario where I am trying to calculate the number of products purchased, where the purchase amount of the product is 70% of the total purchases in MySQL.

In other words, how many unique products represent 70% of product purchases.

Consider the following example:

create table purchases
(id int not null auto_increment primary key,
`product`varchar(10),
`purchased` int);

insert into purchases values 
(1,'pen',1),(2,'pencil',5),(3,'pencil',5),
(4,'eraser',3),(5,'case',1),(6,'case',1),
(7,'pen',1),(8,'pen',1),(9,'pen',1),(10,'pen',1);

As you can see, I have 10 separate purchases of 4 unique products.

A total of 20 products are available: 10 pencils, 5 pens, 3 erasers and 2 cases.

I am trying to determine how many products are required to make up 70% of the total purchases. Thus, in this case, the desired result is 2.

2 comes from 10 pencils plus 5 pens = 15 = ~ 70% of 20.

I created sqlfiddle if that helps.

. ,

Answer 1

.

-, ; , "" , . - :

SQL Fiddle

MySQL 5.6 :

create table purchases (
  id int not null auto_increment primary key,
  `product`varchar(10),`purchased` int
);

insert into purchases 
values (1,'pen',1),(2,'pencil',5),(3,'pencil',5)
     , (4,'eraser',3),(5,'case',1),(6,'case',1)
     , (7,'pen',1),(8,'pen',1),(9,'pen',1)
     ,(10,'pen',1);

1:

-- First, store "sum(purchased)" and the desired percentile into user variables
select sum(purchased), 0.7
into @total_purchases
   , @percentile
from purchases

: (none)

2:

-- Test the query: This will "flag" the desired records
select p.*
     , @cummulative := @cummulative + sum_purchased as cummulative
     , @cummulative / @total_purchases as cummulative_p
     , @cummulative / @total_purchases <= @percentile as flag
from 
    (
        select @cummulative := 0
    ) as init,
    (
        select product, sum(purchased) as sum_purchased 
        from purchases 
        group by product 
        order by sum_purchased desc
    ) as p

:

| product | sum_purchased | cummulative | cummulative_p | flag |
|---------|---------------|-------------|---------------|------|
|  pencil |            10 |          10 |           0.5 |    1 |
|     pen |             5 |          15 |          0.75 |    0 |
|  eraser |             3 |          18 |           0.9 |    0 |
|    case |             2 |          20 |             1 |    0 |

3:

-- Get the final result: Use the above query as a data source for your final result
select product, sum_purchased
from
  (
    select p.*
         , @cummulative := @cummulative + sum_purchased as cummulative
         , @cummulative / @total_purchases as cummulative_p
         , @cummulative / @total_purchases <= @percentile as flag
    from 
        (
            select @cummulative := 0
        ) as init,
        (
            select product, sum(purchased) as sum_purchased 
            from purchases 
            group by product 
            order by sum_purchased desc
        ) as p
  ) as a
where flag = 1

:

| product | sum_purchased |
|---------|---------------|
|  pencil |            10 |

---

---

:

, "" , () :

SQL Fiddle

MySQL 5.6 :

create table purchases (
  id int not null auto_increment primary key,
  `product`varchar(10),`purchased` int
);

insert into purchases 
values (1,'pen',1),(2,'pencil',5),(3,'pencil',5)
     , (4,'eraser',3),(5,'case',1),(6,'case',1)
     , (7,'pen',1),(8,'pen',1),(9,'pen',1)
     ,(10,'pen',1);

1:

-- First, store "sum(purchased)" and the desired percentile into user variables
select sum(purchased), 0.7
into @total_purchases
   , @percentile
from purchases

: (none)

2:

-- Test the query: This will "flag" the desired records
select p.*
     , @cummulative := @cummulative + sum_purchased as cummulative
     , @cummulative / @total_purchases as cummulative_p
     , @flag as flag
     , @flag := case when @cummulative / @total_purchases > @percentile then 0 else 1 end as new_flag
from 
    (
        select @cummulative := 0
             , @flag := 1
    ) as init,
    (
        select product, sum(purchased) as sum_purchased 
        from purchases 
        group by product 
        order by sum_purchased desc
    ) as p

:

| product | sum_purchased | cummulative | cummulative_p | flag | new_flag |
|---------|---------------|-------------|---------------|------|----------|
|  pencil |            10 |          10 |           0.5 |    1 |        1 |
|     pen |             5 |          15 |          0.75 |    1 |        0 |
|  eraser |             3 |          18 |           0.9 |    0 |        0 |
|    case |             2 |          20 |             1 |    0 |        0 |

3:

-- Get the final result: Use the above query as a data source for your final result
select product, sum_purchased
from
  (
    select p.*
         , @cummulative := @cummulative + sum_purchased as cummulative
         , @cummulative / @total_purchases as cummulative_p
         , @flag as flag
         , @flag := case when @cummulative / @total_purchases > @percentile then 0 else 1 end as new_flag
    from 
        (
            select @cummulative := 0
                 , @flag := 1
        ) as init,
        (
            select product, sum(purchased) as sum_purchased 
            from purchases 
            group by product 
            order by sum_purchased desc
        ) as p
  ) as a
where flag = 1

:

| product | sum_purchased |
|---------|---------------|
|  pencil |            10 |
|     pen |             5 |