pdns.sql

with pdns_google as (
  select af, prb_id, p.data google, response_time response_time_google
  from `ripencc-atlas.measurements.dns_decoded`, unnest(wire_message.payload) p
  where date(start_time) = "2022-08-02"
  and (
    # ipv4
    msm_id = 43148525 or
    msm_id = 43148540 or
    msm_id = 43148581 or
    msm_id = 43148589 or
    msm_id = 43148591 or
    msm_id = 43148594 or
    msm_id = 43148595 or
    msm_id = 43148602 or
    msm_id = 43148614 or
    msm_id = 43148677 or
    msm_id = 43148685 or
    msm_id = 43148686

   # ipv6
    or
    msm_id = 43148704 or
    msm_id = 43148709 or
    msm_id = 43148710 or
    msm_id = 43148711	or
    msm_id = 43148714	or
    msm_id = 43148715
  )
  and p.name = "NSID" and p.class = "512" and p.error is null and strpos(p.data, "gpdns-") = 1
),

pdns_quad9 as (
  select af, prb_id, p.data quad9, response_time response_time_quad9
  from `ripencc-atlas.measurements.dns_decoded`, unnest(wire_message.payload) p
  where date(start_time) = "2022-07-30"
  and (
    # ipv4
    msm_id = 12016241
  )
  and p.section = "answer" and p.name = "id.server." and p.type = "TXT" and p.class = "CH" and p.error is null and strpos(p.data, "rrdns.pch.net") > 0
),

pdns_cloudflare as (
  select af, prb_id, p.data cloudflare, response_time response_time_cloudflare
  from `ripencc-atlas.measurements.dns_decoded`, unnest(wire_message.payload) p
  where date(start_time) = "2022-07-30"
  and (
    # ipv4
    msm_id = 12016253
  )
  and p.section = "answer" and p.name = "id.server." and p.type = "TXT" and p.class = "CH" and p.error is null and p.data is not null
)


select * from pdns_google
full outer join pdns_quad9 using (af, prb_id)
full outer join pdns_cloudflare using (af, prb_id)
where af = 4
order by prb_id