Page Comparison

with adv as (
      select distinct advertiserId
    from i19s-insights.insights.trail_click_cnt
    where requestDate between '2023-03-05' and '2023-03-05'= current_date()
      and networkId = '253'<networkId>
    and ( billingCat != 'trailClickPartnerBounce' or billingCat is null)
    and cnt > 0
  ),
  x as (
  select
  DISTINCT
  a.requestId,
  a.advertiserId,
  a.partnerId,
  a.adspaceId,
  b.redirectingId as followClick,
  FROM i19s-insights.insights.trail_click a
  inner join adv c
  on a.advertiserId = c.advertiserId
  left join i19s-insights.insights.trail_click b
  on b.redirectingId = a.requestId and date(b.requestTime) between '2023-03-05' and '2023-03-05'= current_date() and b.networkId='253'<networkId>
  WHERE date(a.requestTime) between '2023-03-05' and '2023-03-05'= current_date()
  and a.networkId ='253' <networkId>
  and a.mediaTarget is not null
)
select advertiserId, partnerId, adspaceId, count(*) total, count(followClick) as redirected, round(count(followClick)*100/count(*),2) as share
from x
-- where partnerId = '501844'<partnerId>
group by 1,2,3
order by total desc