use andyrussg;

CREATE TABLE
  t152122bannerloader20161201
AS SELECT
  *,
  SUBSTR(ts,1,16) as time,
  parse_url(concat('http://bla.org/woo/', uri_query), 'QUERY', 'campaign')  as campaign,
  parse_url(concat('http://bla.org/woo/', uri_query), 'QUERY', 'banner')  as banner,
  parse_url(concat('http://bla.org/woo/', uri_query), 'QUERY', 'uselang')  as uselang,
  parse_url(concat('http://bla.org/woo/', uri_query), 'QUERY', 'debug')  as debug
FROM
  wmf.webrequest
WHERE
  year=2016
  AND month=12
  AND day=1
  AND agent_type = 'user'
  AND uri_query LIKE "%BannerLoader%";
  
CREATE TABLE
  t152122beaconimpression20161202
AS SELECT
  *,
  SUBSTR(ts,1,16) as time,
  parse_url(concat('http://bla.org/woo/', uri_query), 'QUERY', 'campaign')  as campaign,
  parse_url(concat('http://bla.org/woo/', uri_query), 'QUERY', 'banner')  as banner,
  parse_url(concat('http://bla.org/woo/', uri_query), 'QUERY', 'uselang')  as uselang,
  parse_url(concat('http://bla.org/woo/', uri_query), 'QUERY', 'debug')  as debug,
  parse_url(concat('http://bla.org/woo/', uri_query), 'QUERY', 'db')  as db,
  parse_url(concat('http://bla.org/woo/', uri_query), 'QUERY', 'device')  as device,
  parse_url(concat('http://bla.org/woo/', uri_query), 'QUERY', 'statusCode')  as statusCode,
  parse_url(concat('http://bla.org/woo/', uri_query), 'QUERY', 'country')  as country,
  parse_url(concat('http://bla.org/woo/', uri_query), 'QUERY', 'anonymous')  as anonymous
FROM
  wmf.webrequest
WHERE
  year=2016
  AND month=12
  AND day=2
  AND agent_type = 'user'
  AND uri_path LIKE '%beacon%impression%';