SQL: count all records with consecutive occurrence of same value for each device set and return the highest count: Percentage

Question

This is in continuation of the question below:

SQL: count all records with consecutive occurrence of same value for each device set and return the highest count

I am able to display the count of all records with consecutive occurence now, but I would like to display them as percentage. So instead of this output:

Device ID        speed             highcount
--------------------------------------------------
07777778999       34               4
07777778123       15               3

I would like it in percentage, that is highestcount/(number of entries of that device)

Device ID        speed             percentcount           
--------------------------------------------------
07777778999       34               0.44(4/9-for explaination reason 4 is higest count and 9 is num of entries of tht device so 4/9 is 0.44)
07777778123       15               0.75(3//4-same as above)

The query which worked for the highest count is

select device_id, speed, num_times
from (select device_id, speed, count(*) as num_times,
             row_number() over (partition by device_id order by count(*) desc) as seqnum
      from (select t.*,
                   row_number() over (partition by device_id order by datetime) as seqnum,
                   row_number() over (partition by device_id, speed order by datetime) as seqnum_s
            from t
           ) t
      group by device_id, speed, (seqnum - seqnum_s)
     ) ds
where seqnum = 1;

how can I modify the same to get percentage as explained above?

Let me also display the schema and table from previous question.

Device ID        speed             DateTime
--------------------------------------------------
07777778999       34               18-12-2016 17:15
07777778123       15               18-12-2016 18:10
07777778999       34               19-12-2016 19:30
07777778999       34               19-12-2016 12:15
07777778999       20               19-12-2016 13:15
07777778999       20               20-12-2016 11:15
07777778123       15               20-12-2016 9:15
07777778128       44               20-12-2016 17:15
07777778123       15               20-12-2016 17:25
07777778123       12               20-12-2016 17:35
07777778999       34               20-12-2016 17:45
07777778999       34               20-12-2016 17:55
07777778999       34               20-12-2016 18:50
07777778999       34               20-12-2016 18:55

Answer 1

This would be one way to do it:

select ds.device_id, ds.speed, ds.num_times, (ds.num_times/ds2.num_dev) percentcount
from (select device_id, speed, count(*) as num_times,
             row_number() over (partition by device_id order by count(*) desc) as seqnum
      from (select t.*,
                   row_number() over (partition by device_id order by datetime) as seqnum,
                   row_number() over (partition by device_id, speed order by datetime) as seqnum_s
            from t
           ) t
      group by device_id, speed, (seqnum - seqnum_s)
     ) ds
left join (select device_id, count(device_id) num_dev
from t
group by device_id) ds2 
on ds.device_id = ds2.device_id
where ds.seqnum = 1

Here is the DEMO so you have it for your next questions :)