the query IS : WITH h1 AS
(
SELECT
    datetime,
    account,
    campaign,
    adgroup,
    tag_name,
    domain_id,
    query,
    ip_isp,
    POSITION,
    hq1_search_id,
    type_tq,
    yahoo_revenue AS revenue,
    yahoo_discard_percent AS outclicks,
    yahoo_outclicks,
    session_outclicks
FROM adknown_reporting.hourly_advertiser_rpcs
WHERE pdate >= '2018-03-01'
AND hq1_search_id > 0
AND distinct_queries = 1
AND NOT REGEXP_LIKE(account, 'trafficvance|awd1-|test1')
AND NOT REGEXP_LIKE(campaign, 'R_T_Big')
)
, p1 AS (
SELECT
    datetime,
    account,
    campaign,
    adgroup,
    tag_name,
    domain_id,
    query,
    ip_isp,
    POSITION,
    hq_poo_search_id,
    type_tq,
    yahoo_revenue AS revenue,
    yahoo_discard_percent AS outclicks,
    yahoo_outclicks,
    session_outclicks
FROM adknown_reporting.hourly_advertiser_rpcs
WHERE pdate >= '2018-03-01'
AND hq_poo_search_id > 0
AND distinct_queries = 1
AND NOT REGEXP_LIKE(account, 'trafficvance|awd1-|test1')
)
, h2 AS (
SELECT
    a.datetime,
    a.account,
    a.campaign,
    a.adgroup,
    a.tag_name,
    a.domain_id,
    a.query,
    a.ip_isp,
    c.ip_address,
    c.country_code,
    c.state,
    c.user_agent,
    c.id,
    c.inclicks,
    a.outclicks,
    a.revenue,
    a.POSITION,
    d.placement AS source_placement,
    d.creative AS creative,
    d.device_type AS source_device_type,
    d.device_model AS source_device_model,
    d.POSITION AS source_position,
    d.network AS source_network,
    d.match_type AS source_match_type,
    d.entry_url
FROM h1 a
LEFT JOIN adknown_platform_archive.v_s_hq1_searches_luke b ON a.hq1_search_id = b.id
LEFT JOIN adknown_platform_archive.v_s_hq1_luke c ON b.hq1_id = c.id
LEFT JOIN adknown_platform_archive.v_s_hq1_adwords_luke d ON c.id = d.hq1_id
WHERE b.pdate >= '2018-03-01'
AND c.pdate >= '2018-03-01'
AND d.pdate >= '2018-03-01'
AND a.revenue > 0.00
)
, p2 AS (
SELECT
    a.datetime,
    a.account,
    a.campaign,
    a.adgroup,
    a.tag_name,
    a.domain_id,
    a.query,
    a.ip_isp,
    c.ip_address,
    c.country_code,
    c.state,
    c.user_agent,
    c.id,
    c.inclicks,
    a.outclicks,
    a.revenue,
    a.POSITION,
    c.source_placement,
    c.ad AS creative,
    c.device_type AS source_device_type,
    NULL AS source_device_model,
    NULL AS source_position,
    c.source_network,
    c.source_match_type,
    c.entry_url
FROM p1 a
LEFT JOIN adknown_platform.s_hq_poo_searches b ON a.hq_poo_search_id = b.id
LEFT JOIN adknown_platform.s_hq_poo c ON b.tracker = c.tracker
WHERE b.pdate >= '2018-03-01'
AND c.pdate >= '2018-03-01'
AND a.revenue > 0.00
)
, h3 AS (
SELECT
    a.datetime,
    a.account,
    a.campaign,
    a.adgroup,
    a.tag_name,
    a.domain_id,
    a.query,
    a.ip_address,
    a.country_code,
    a.ip_isp,
    a.state,
    a.user_agent,
    a.id,
    a.inclicks,
    a.outclicks,
    0 AS revenue,
    0 AS POSITION,
    b.placement AS source_placement,
    b.creative AS creative,
    b.device_type AS source_device_type,
    b.device_model AS source_device_model,
    b.POSITION AS source_position,
    b.network AS source_network,
    b.match_type AS source_match_type,
    b.entry_url
 FROM adknown_platform_archive.v_s_hq1_luke a
  LEFT JOIN adknown_platform_archive.v_s_hq1_adwords_luke b ON a.id = b.hq1_id
WHERE CAST(datetime AS VARCHAR) BETWEEN '2018-03-01' AND CAST((SELECT DATE_ADD('hour', 1, MAX(datetime)) FROM h1 GROUP BY datetime ORDER BY datetime DESC LIMIT 1) AS VARCHAR)
  AND b.pdate >= '2018-03-01'
AND NOT REGEXP_LIKE(a.account, 'trafficvance|awd1-|test1')
AND NOT REGEXP_LIKE(a.campaign, 'R_T_Big')
AND a.id NOT IN (SELECT id FROM h2 WHERE id IS NOT NULL GROUP BY id)
)
, p3 AS (
SELECT
    a.datetime,
    a.account,
    a.campaign,
    a.adgroup,
    a.tag_name,
    a.domain_id,
    a.query,
    a.ip_address,
    a.country_code,
    a.ip_isp,
    a.state,
    a.user_agent,
    a.id,
    a.inclicks,
    a.outclicks,
    0 AS revenue,
    0 AS POSITION,
    source_placement,
    ad AS creative,
    device_type AS source_device_type,
    NULL AS source_device_model,
    NULL AS source_position,
    source_network,
    source_match_type,
    entry_url
 FROM adknown_platform.s_hq_poo a
WHERE CAST(datetime AS VARCHAR) BETWEEN '2018-03-01' AND  CAST((SELECT DATE_ADD('hour', 1, MAX(datetime)) FROM p1 GROUP BY datetime ORDER BY datetime DESC LIMIT 1) AS VARCHAR)
AND NOT REGEXP_LIKE(a.account, 'trafficvance|awd1-|test1')
AND a.id NOT IN (SELECT id FROM p2 WHERE id IS NOT NULL GROUP BY id)
)
, u1 AS (
SELECT * FROM h2
  UNION
SELECT * FROM h3
  UNION
SELECT * FROM p2
  UNION
SELECT * FROM p3
)
, u2 AS (
SELECT
 a.*,
 CASE
    WHEN user_agent LIKE '%(Linux; Android%'
        THEN REPLACE(SPLIT_PART(user_agent,';',2),'Mozilla/5.0 (Linux; ','')
    WHEN user_agent LIKE '%Mozilla/5.0 (Android%'
        THEN REPLACE(SPLIT_PART(user_agent,';',1),'Mozilla/5.0( ','')
    WHEN user_agent LIKE '%(Linux; U; Android%'
        THEN REPLACE(LTRIM(SPLIT_PART(user_agent,';',3)),'Mozilla/5.0 (Linux; U; ','')
    WHEN user_agent LIKE '%Windows NT 10.0%'
        THEN 'Windows 10'
    WHEN user_agent LIKE '%Windows NT 6.3%'
        THEN 'Windows 8.1'
    WHEN user_agent LIKE '%Windows NT 6.2%'
        THEN 'Windows 8'
    WHEN user_agent LIKE '%Windows NT 6.1%'
        THEN 'Windows 7'
    WHEN user_agent LIKE '%Windows NT 6.0%'
        THEN 'Windows Vista'
    WHEN user_agent LIKE '%Windows NT 5.2%'
        THEN 'Windows Server 2003; Windows XP x64 Edition'
    WHEN user_agent LIKE '%Windows NT 5.1%'
        THEN 'Windows XP'
    WHEN user_agent LIKE '%Windows NT 5.0%'
        THEN 'Windows 2000'
    WHEN user_agent LIKE '%Windows NT 4.0%'
        THEN 'Microsoft Windows NT 4.0'
    WHEN user_agent LIKE '%Windows 9'
        THEN 'Windows 95/98/Millenium'
    WHEN user_agent LIKE '%Windows CE'
        THEN 'Windows CE'
    WHEN user_agent LIKE '%Mozilla/5.0 (Macintosh%'
        AND user_agent NOT LIKE '% rv:%'
        THEN REPLACE(SPLIT_PART(user_agent,')', 1),'Mozilla/5.0 (Macintosh; Intel ','')
    WHEN user_agent LIKE '%Mozilla/5.0 (Macintosh%'
        AND user_agent LIKE '% rv:%'
        THEN REPLACE(SPLIT_PART(user_agent,';', 1),'Mozilla/5.0 (Macintosh; Intel ','')
    WHEN user_agent LIKE '%Blackberry%'
        THEN 'Blackberry'
    /* WHEN user_agent LIKE '%Windows Phone%'
        THEN LTRIM(SPLIT_PART(SPLIT_PART(user_agent,';', 2),';',  -1))*/
    WHEN user_agent LIKE '%Opera Mini%'
        THEN 'Opera Mini'
    WHEN user_agent LIKE '%UCWEB%'
        THEN LTRIM(CONCAT(' UCWEB_Browser ',REPLACE(LTRIM(SPLIT_PART(user_agent,';', 3)),'UCWEB/2.0 (MIDP-2.0; U; ','')))
    WHEN user_agent LIKE '%Mozilla/5.0 (iPhone%'
        THEN REPLACE(SPLIT_PART(user_agent,'like Mac OS X',1),'Mozilla/5.0 (iPhone; CPU ','')
    WHEN user_agent LIKE '%iPod%'
        THEN CONCAT('iPod ',REPLACE(SPLIT_PART(user_agent,'like Mac OS X', 1),'Mozilla/5.0 (iPod touch; CPU ',''))
    WHEN user_agent LIKE '%iPad%'
        THEN CONCAT('iPad ',REPLACE(SPLIT_PART(user_agent,'like Mac OS X', 1),'Mozilla/5.0 (iPad; CPU ',''))
    WHEN user_agent LIKE '%(X11;%'
        THEN REPLACE(SPLIT_PART(user_agent,')', 1),'Mozilla/5.0 (X11; ','')
    ELSE user_agent
END AS os_version,
CASE
    WHEN user_agent LIKE '%Firefox%'
        THEN 'firefox'
    WHEN user_agent LIKE '%Chrome%'
        THEN 'chrome'
    WHEN user_agent LIKE '%Safari%'
        THEN 'safari'
    WHEN user_agent LIKE '%Dalvik%'
        THEN 'dalvik'
    WHEN user_agent LIKE '%MSIE 7%'
        THEN 'IE7'
    WHEN user_agent LIKE '%MSIE 8%'
        THEN 'IE8'
    WHEN user_agent LIKE '%MSIE 9%'
        THEN 'IE9'
    WHEN user_agent LIKE '%MSIE 10%'
        THEN 'IE10'
    WHEN user_agent LIKE '%rv:11%'
        THEN 'IE11'
    WHEN user_agent LIKE '%like Mac OS X%'
        THEN 'like Mac OS X'
    ELSE user_agent
END AS browser,
b.carrier_type
FROM u1 a
LEFT JOIN luke.carrier_groups b ON a.ip_isp = b.carrier
)
,u3 AS (
SELECT
  HOUR(datetime) AS HOUR,
  CONCAT(SPLIT_PART(ip_address,'.',1),'.',SPLIT_PART(ip_address,'.',2),'.',SPLIT_PART(ip_address,'.',3)) AS c_class_ip,
  *,
  CASE
        WHEN os_version LIKE '%Android%'
            THEN 'Android'
        WHEN os_version LIKE '%Windows%'
            THEN 'Windows'
        WHEN os_version LIKE '%iPhone%'
            THEN 'iPhone'
        WHEN os_version LIKE '%Mac OS X%'
            THEN 'OS X'
        WHEN os_version LIKE '%Linux%'
            THEN 'Linux'
        ELSE 'unknown'
    END AS operating_system
FROM u2
  )
  SELECT
  HOUR,
  SUM(inclicks) AS inclicks,
  SUM(outclicks) AS outclicks,
  ROUND(SUM(outclicks) / SUM(CAST(inclicks AS DOUBLE)), 3) AS ctr,
  SUM(revenue) / SUM(outclicks) AS rpc,
  ROUND((SUM(revenue) / SUM(CAST(inclicks AS DOUBLE))) * 1000, 2) AS rpm
  FROM u3
  GROUP BY HOUR
  ORDER BY HOUR ASC