find_10_8_tr.sql

with matching_traceroutes as (
  select start_time, prb_id, msm_id, hops
  from ripencc-atlas.yesterday.traceroute, unnest(hops) h
  where net.ip_trunc(h.hop_addr_bytes, 8) = net.ip_from_string("10.0.0.0") and af = 4 and hop > 4
)

-- ANY_VALUE() and TO_JSON_STRING() are magic to get back to the
-- original record, since I unpacked the hops in the statement above
select ANY_VALUE(matching_traceroutes).*
from matching_traceroutes
group by TO_JSON_STRING(matching_traceroutes)