Skip to content

Instantly share code, notes, and snippets.

@sdstrowes

sdstrowes/1_dupes_after_unnest.sql

Last active July 29, 2019 18:18
Show Gist options
  • Save sdstrowes/8355f2d8b1ff69c0bfd1841ff97f6d9c to your computer and use it in GitHub Desktop.
Save sdstrowes/8355f2d8b1ff69c0bfd1841ff97f6d9c to your computer and use it in GitHub Desktop.
Download ZIP
Raw
1_dupes_after_unnest.sql
with filtered_table as (
select p.*
from prod.traceroute_atlas_prod as p, unnest(hops) as h, unnest(resultHops) as rh
where (
startTime > "2019-07-01" and
startTime < "2019-07-02"
)
and (
rh.from = "170.238.232.149" or
rh.from = "170.238.232.150" or
rh.from = "170.238.232.145" or
rh.from = "170.238.232.146"
)
)
select * from filtered_table
Raw
2_de_duped.sql
with filtered_table as (
select p.*
from prod.traceroute_atlas_prod as p, unnest(hops) as h, unnest(resultHops) as rh
where (
startTime > "2019-07-01" and
startTime < "2019-07-02"
)
and (
rh.from = "170.238.232.149" or
rh.from = "170.238.232.150" or
rh.from = "170.238.232.145" or
rh.from = "170.238.232.146"
)
)
select ANY_VALUE(filtered_table).*
from filtered_table
group by to_json_string(filtered_table)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment