Skip to content

Instantly share code, notes, and snippets.

@qi-qi

qi-qi/empty-impression-urls.sql

Created February 19, 2019 11:00
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save qi-qi/e8998075f3c65d2533881daddd2f5b1c to your computer and use it in GitHub Desktop.
Save qi-qi/e8998075f3c65d2533881daddd2f5b1c to your computer and use it in GitHub Desktop.
Download ZIP
empty impression urls
Raw
empty-impression-urls.sql
with dataset as (
select ad.i, is_rt, ad.impurls, dt
from data_raw.batch, unnest(data_raw.batch.ads) as t(ad)
where dt >= '2019-02-10')
select dt, i as impression, is_rt, count(*) as counting
from dataset where cardinality(impurls) = 0 group by dt, i, is_rt order by dt, count(*) desc, i
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment