query.sql

with rtts as
(
    select prb_id, af, hop, rtt
    from ripencc-atlas.yesterday.traceroute, unnest(hops) h
    where hop <= 2 and err is null and hop_addr is not null 
),

agg_rtts as
(
    with quants as (
        select prb_id, af, hop, approx_quantiles(rtt, 20) q_array, count(*) n
        from rtts
        group by prb_id, af, hop
    )
    select prb_id, af, hop, q_array[offset(2)] q10, q_array[offset(10)] med, q_array[offset(18)] q90, n
    from quants
)

select distinct *
from  agg_rtts
where med >= 495
and   n > 100
order by prb_id, af, hop

results.md

Row	prb_id	af	hop	q10	med	q90	n
1	11268	4	2	33.853	745.818	993.572	4278
2	24110	4	2	3.692	2906.042	3702.204	7221
3	27843	4	1	0.757	757.876	2687.683	3445
4	27843	4	2	757.658	885.665	2869.679	2306
5	33487	4	2	1133.993	1593.411	2189.28	2838
6	55562	4	2	581.38	618.896	789.054	5522
7	1000244	4	2	584.496	617.264	787.287	5276
8	1000795	4	2	34.16	532.325	1829.909	3215
9	1002186	6	2	942.54	1431.632	2086.3	7624