select
    click_id,
    ip,
    device,
    os,
    count(*)
from (
    select
        click_id,
        ip,
        device,
        os,
        click_time
    from raw
    order by ip, device, os, click_time
)
all inner join (
    select
        ip,
        device,
        os,
        click_time as click_time2
    from raw
    order by ip, device, os, click_time2
) using ip, device, os
where click_time >= click_time2
group by click_id, ip, device, os
limit 25