# Ways to compare distribution by bin?

• ### Question

• Hi

I have a table, which has 4 columns with

|Device_Model | Device_Id | Bin | DurationInBin|

The Bin number is from 1 to 20. DurationBin is the time spent in each Bin number by each device.

We want to compare the distribution of DurationInBin by device_model. My initial attempt is to sum up the DurationInBin by Bin then compare the sum across models. The downside is that the value could be blown up if some devices stay in one bin for a long time and skew the result.

What're the other ways to see the distribution?

Thanks for the help.

Wednesday, June 17, 2020 5:50 PM

### All replies

• hi

Please provide some query and more clarity that you attempt

Thanks

Wednesday, June 17, 2020 7:21 PM
• For example, here's the data we see

Device_Model,Device_Id,Bin,DurationInBin
m2, 13027,1,0
m2, 13027,2,20
m2, 13027,3,30
m2, 13027,4,34
m2, 13027,5,25
m2, 13027,6,0
m2, 13027,7,0
m2, 13027,8,0
m2, 13027,9,0
m2, 13027,10,0
m2, 13027,11,0
m2, 13027,12,0
m2, 13027,13,0
m2, 13027,14,0
m2, 13027,15,0
m2, 13027,16,0
m2, 13027,17,0
m2, 13027,18,0
m2, 13027,20,0
m3, 14924,1,0
m3, 14924,2,0
m3, 14924,3,0
m3, 14924,4,24
m3, 14924,5,28
m3, 14924,6,37
m3, 14924,7,30
m3, 14924,8,10
m3, 14924,9,5
m3, 14924,10,0
m3, 14924,11,0
m3, 14924,12,0
m3, 14924,13,0
m3, 14924,14,0
m3, 14924,15,0
m3, 14924,16,0
m3, 14924,17,0
m3, 14924,18,0
m3, 14924,20,55

Wednesday, June 17, 2020 8:49 PM
• hi

```with machin(model,Id,bin,duration)as
(
select d.Device_Model,d.Device_Id,d.bin,sum(d.DurationInBin) from distribution_by1_bin  d
group by d.Device_Model,d.Device_Id,d.bin
),result(model,Id,bin,duration,ranks)as
(
select model,Id,bin,duration,rank() over(partition by model order by duration desc) ranks
from machin
)
select model,Id,bin,duration from result where ranks = 1```
here the table name i provide
`distribution_by1_bin`

Thanks and Regards

Laxmidhar sahoo

Thursday, June 18, 2020 11:27 AM