hourly_ssp_aggregates.sql

CREATE TABLE aggregations (name regclass primary key, last_update timestamp);
INSERT INTO aggregations VALUES ('hourly_ssp_aggregates', now() - interval '15 minute');


CREATE OR REPLACE FUNCTION compute_hourly_ssp_aggregates()
RETURNS void LANGUAGE plpgsql AS $function$
DECLARE
  start_time timestamp;
  end_time timestamp := now() - interval '15 minute'; -- exclude in-flight requests
BEGIN
  SELECT last_update INTO start_time FROM aggregations WHERE name = 'hourly_ssp_aggregates'::regclass;
  UPDATE aggregations SET last_update = end_time WHERE name = 'hourly_ssp_aggregates'::regclass;

  SET LOCAL citus.all_modifications_commutative TO on; -- for on-premises, replication factor >1 only

  EXECUTE $$
    INSERT INTO hourly_ssp_aggregates
      SELECT
        r.loghour,
        r.date_utc,
        r.date_hr,
        r.site_id,
        CASE WHEN s.name IS NULL THEN 'NoName' ELSE s.name END as name,
        r.ad_slot,
        r.ad_size,
        r.impression_type,
        r.adapter_type,
        CASE WHEN s.pub_id IS NULL THEN 0 ELSE s.pub_id END as pub_id,
        r.deal_id,
        r.ad_platform,
        cast(sum(r.requests) as bigint),
        cast(sum(r.impressions) as bigint),
        cast(sum(r.clicks) as bigint),
        cast(sum(r.total_revenue) as decimal),
        cast(sum(r.total_net_revenue) as decimal),
        cast(sum(r.total_gross_revenue) as decimal),
        cast(sum(r.total_auction_revenue) as decimal)
      FROM
        (SELECT
          a.loghour as loghour,
          b.loghour as loghour2,
          CASE WHEN a.date_utc IS NULL THEN b.date_utc ELSE a.date_utc END as date_utc,
          CASE WHEN a.date_hr IS NULL THEN b.date_hr ELSE a.date_hr END as date_hr,
          CASE WHEN a.site_id IS NULL THEN b.site_id ELSE a.site_id END as site_id,
          CASE WHEN a.ad_slot=b.ad_slot OR a.ad_slot IS NULL THEN b.ad_slot ELSE 'AUM' END as ad_slot,
          CASE WHEN a.ad_size IS NULL THEN b.ad_size ELSE a.ad_size END as ad_size,
          CASE WHEN b.impression_type IS NULL THEN 'NA' ELSE b.impression_type END as impression_type,
          CASE WHEN b.adapter_type IS NULL THEN 'NA' ELSE b.adapter_type END as adapter_type,
          b.pub_group_id,
          CASE WHEN b.deal_id IS NULL THEN 'NA' ELSE b.deal_id END as deal_id,
          CASE WHEN b.ad_platform IS NULL THEN 'NA' ELSE b.ad_platform END as ad_platform,
          SUM(count) as requests,
          SUM(b.impressions) as impressions,
          SUM(b.clicks) as clicks,
          SUM(b.total_revenue) as total_revenue,
          SUM(b.total_net_revenue) as total_net_revenue,
          SUM(b.total_gross_revenue) as total_gross_revenue,
          SUM(b.total_auction_revenue) as total_auction_revenue
        FROM
          (
          select 
            loghour,
            cast(to_char(to_timestamp(cast(logtext->>'timeStamp' as bigint)),'YYYY-MM-DD') as text) as date_utc,
            cast(to_char(to_timestamp(cast(logtext->>'timeStamp' as bigint)),'YYYY-MM-DD HH24:00:00') as text) as date_hr,
            logtext->>'SiteId' as site_id,
            logtext->>'AdSlot' as ad_slot,
            logtext->>'AdSize' as ad_size,
            logtext->>'groupPubId' as pub_group_id,
            logtext->>'dealId' as deal_id,
            logtext->>'adPlatformType' as ad_platform,
            sum(cast(logtext->>'count' as int)) as count
          from
            s2s_request
          where
            cast(to_char(to_timestamp(cast(logtext->>'timeStamp' as bigint)),'YYYY-MM-DD HH24:MI:SS') as text)>=cast(to_char($1,'YYYY-MM-DD HH24:MI:00') as text)
            AND
            cast(to_char(to_timestamp(cast(logtext->>'timeStamp' as bigint)),'YYYY-MM-DD HH24:MI:SS') as text)<cast(to_char($2,'YYYY-MM-DD HH24:MI:00') as text)
          group by
            loghour,
            cast(to_char(to_timestamp(cast(logtext->>'timeStamp' as bigint)),'YYYY-MM-DD') as text),
            cast(to_char(to_timestamp(cast(logtext->>'timeStamp' as bigint)),'YYYY-MM-DD HH24:00:00') as text),
            logtext->>'SiteId',
            logtext->>'AdSlot',
            logtext->>'AdSize',
            logtext->>'groupPubId',
            logtext->>'dealId',
            logtext->>'adPlatformType'
          )a
        FULL OUTER JOIN
          (
          select
            loghour,
            cast(to_char(to_timestamp(cast(logtext->>'ts' as bigint)/1000),'YYYY-MM-DD') as text) as date_utc,
            cast(to_char(to_timestamp(cast(logtext->>'ts' as bigint)/1000),'YYYY-MM-DD HH24:00:00') as text) as date_hr,
            logtext->>'sid' as site_id,
            logtext->>'adSlot' as ad_slot,
            logtext->>'adSize' as ad_size,
            logtext->>'it' as impression_type,
            logtext->>'adapterType' as adapter_type,
            logtext->>'pgId' as pub_group_id,
            logtext->>'dealId' as deal_id,
            logtext->>'adPlatform' as ad_platform,
            sum(case when logtext->>'et'='IMPRESSION' then 1 else 0 end) as impressions,
            sum(case when logtext->>'et'='CLICK' then 1 else 0 end) as clicks,
            sum(cast(logtext->>'gp' as decimal)/1000) as total_revenue,
            sum(cast(logtext->>'np' as decimal)/1000) as total_net_revenue,
            sum(cast(logtext->>'gp' as decimal)/1000) as total_gross_revenue,
            sum(cast(logtext->>'ap' as decimal)/1000) as total_auction_revenue
          from
            tracker
          where
            logtext->>'adapterType' = 'OPENRTB' AND
            cast(to_char(to_timestamp(cast(logtext->>'ts' as bigint)/1000),'YYYY-MM-DD HH24:MI:SS') as text)>=cast(to_char($1,'YYYY-MM-DD HH24:MI:00') as text) AND
            cast(to_char(to_timestamp(cast(logtext->>'ts' as bigint)/1000),'YYYY-MM-DD HH24:MI:SS') as text)<cast(to_char($2,'YYYY-MM-DD HH24:MI:00') as text)
          group by
            loghour,
            cast(to_char(to_timestamp(cast(logtext->>'ts' as bigint)/1000),'YYYY-MM-DD') as text),
            cast(to_char(to_timestamp(cast(logtext->>'ts' as bigint)/1000),'YYYY-MM-DD HH24:00:00') as text),
            logtext->>'sid',logtext->>'adSlot',logtext->>'adSize',logtext->>'it',logtext->>'adapterType',logtext->>'pgId',
            logtext->>'dealId',logtext->>'adPlatform'
          ) b
        ON
          a.date_utc=b.date_utc AND
          a.date_hr=b.date_hr AND
          a.pub_group_id=b.pub_group_id AND
          a.ad_size=b.ad_size AND
          a.loghour=b.loghour
        GROUP BY
          a.loghour,
          b.loghour,
          CASE WHEN a.date_utc IS NULL THEN b.date_utc ELSE a.date_utc END,
          CASE WHEN a.date_hr IS NULL THEN b.date_hr ELSE a.date_hr END,
          CASE WHEN a.site_id IS NULL THEN b.site_id ELSE a.site_id END,
          CASE WHEN a.ad_slot=b.ad_slot OR a.ad_slot IS NULL THEN b.ad_slot ELSE 'AUM' END,
          CASE WHEN a.ad_size IS NULL THEN b.ad_size ELSE a.ad_size END,
          b.impression_type,
          b.adapter_type,
          b.pub_group_id,
          b.deal_id,
          b.ad_platform
        )r
      LEFT OUTER JOIN
        (SELECT
          cast(id as text) as site_id,
          account_id as pub_id,
          name as name
        FROM
          site_l
        )s
      ON
        r.site_id = s.site_id
      where
        r.site_id is not NULL
      group by 
        r.loghour,
        r.date_utc,
        r.date_hr,
        r.site_id,
        CASE WHEN s.name IS NULL THEN 'NoName' ELSE s.name END,
        r.ad_slot,
        r.ad_size,
        r.impression_type,
        r.adapter_type,
        CASE WHEN s.pub_id IS NULL THEN 0 ELSE s.pub_id END,
        r.deal_id,
        r.ad_platform
    ON CONFLICT (loghour,date_utc, date_hr,siteid,name,adslot,adsize,impressiontype,adaptertype,grouppubid,dealid,adplatform)
    DO UPDATE
    SET bidssent =+ excluded.bidssent,
      adslotrequestsent =+ excluded.adslotrequestsent,
      bidresponses =+ excluded.bidresponses,
      bidwins =+ excluded.bidwins,
      numimpressions =+ excluded.numimpressions,
      numclicks =+ excluded.numclicks,
      totalrevenue =+ excluded.totalrevenue,
      totalnetrevenue =+ excluded.totalnetrevenue,
      totalgrossrevenue =+ excluded.totalgrossrevenue,
      totalauctionrevenue =+ excluded.totalauctionrevenue$$
  USING start_time, end_time;
  EXECUTE $$
    INSERT INTO hourly_ssp_aggregates
      SELECT
        r.loghour,
        r.date_utc,
        r.date_hr,
        r.site_id,
        s.name,
        r.ad_slot,
        r.ad_size,
        r.impression_type,
        r.adapter_type,
        CASE WHEN s.pub_id IS NULL THEN 0 ELSE s.pub_id END as pub_id,
        r.deal_id,
        r.ad_platform,
        cast(r.requests as bigint),
        cast(r.impressions as bigint),
        cast(r.clicks as bigint),
        cast(r.total_revenue as decimal),
        cast(r.total_net_revenue as decimal),
        cast(r.total_gross_revenue as decimal),
        cast(r.total_auction_revenue as decimal)
      FROM
        (SELECT
          a.loghour as loghour,
          b.loghour as loghour2,
          CASE WHEN a.date_utc IS NULL THEN b.date_utc ELSE a.date_utc END as date_utc,
          CASE WHEN a.date_hr IS NULL THEN b.date_hr ELSE a.date_hr END as date_hr,
          CASE WHEN a.site_id IS NULL THEN b.site_id ELSE a.site_id END as site_id,
          CASE WHEN a.ad_slot=b.ad_slot OR a.ad_slot IS NULL THEN b.ad_slot ELSE 'AUM' END as ad_slot,
          CASE WHEN a.ad_size IS NULL THEN b.ad_size ELSE a.ad_size END as ad_size,
          CASE WHEN b.impression_type IS NULL THEN 'NA' ELSE b.impression_type END as impression_type,
          CASE WHEN b.adapter_type IS NULL THEN 'NA' ELSE b.adapter_type END as adapter_type,
          b.pub_group_id,
          CASE WHEN b.deal_id IS NULL THEN 'NA' ELSE b.deal_id END as deal_id,
          CASE WHEN b.ad_platform IS NULL THEN 'NA' ELSE b.ad_platform END as ad_platform,
          SUM(count) as requests,
          SUM(b.impressions) as impressions,
          SUM(b.clicks) as clicks,
          SUM(b.total_revenue) as total_revenue,
          SUM(b.total_net_revenue) as total_net_revenue,
          SUM(b.total_gross_revenue) as total_gross_revenue,
          SUM(b.total_auction_revenue) as total_auction_revenue
        FROM
          (
          select
            loghour,
            cast(to_char(to_timestamp(cast(logtext->>'timeStamp' as bigint)),'YYYY-MM-DD') as text) as date_utc,
            cast(to_char(to_timestamp(cast(logtext->>'timeStamp' as bigint)),'YYYY-MM-DD HH24:00:00') as text) as date_hr,
            logtext->>'siteId' as site_id,
            logtext->>'adName' as ad_slot,
            logtext->>'size' as ad_size,
            sum(cast(logtext->>'count' as int)) as count
          from
            prebid
          where
            loghour = cast(to_char(NOW(),'YYYY_MM_DD') as text) AND
            cast(to_char(to_timestamp(cast(logtext->>'timeStamp' as bigint)),'YYYY-MM-DD HH24:MI:SS') as text)>=cast(to_char($1,'YYYY-MM-DD HH24:MI:00') as text) AND
            cast(to_char(to_timestamp(cast(logtext->>'timeStamp' as bigint)),'YYYY-MM-DD HH24:MI:SS') as text)<cast(to_char($2,'YYYY-MM-DD HH24:MI:00') as text)
          group by
            loghour,
            cast(to_char(to_timestamp(cast(logtext->>'timeStamp' as bigint)),'YYYY-MM-DD') as text),
            cast(to_char(to_timestamp(cast(logtext->>'timeStamp' as bigint)),'YYYY-MM-DD HH24:00:00') as text),
            logtext->>'siteId',logtext->>'adName',
            logtext->>'size'
          )a
        FULL OUTER JOIN
          (
          select
            loghour,
            cast(to_char(to_timestamp(cast(logtext->>'ts' as bigint)/1000),'YYYY-MM-DD') as text) as date_utc,
            cast(to_char(to_timestamp(cast(logtext->>'ts' as bigint)/1000),'YYYY-MM-DD HH24:00:00') as text) as date_hr,
            logtext->>'sid' as site_id,
            logtext->>'adSlot' as ad_slot,
            logtext->>'adSize' as ad_size,
            logtext->>'it' as impression_type,
            logtext->>'adapterType' as adapter_type,
            logtext->>'pgId' as pub_group_id,
            logtext->>'dealId' as deal_id,
            logtext->>'adPlatform' as ad_platform,
            sum(case when logtext->>'et'='IMPRESSION' then 1 else 0 end) as impressions,
            sum(case when logtext->>'et'='CLICK' then 1 else 0 end) as clicks,
            sum(cast(logtext->>'gp' as decimal)/1000) as total_revenue,
            sum(cast(logtext->>'np' as decimal)/1000) as total_net_revenue,
            sum(cast(logtext->>'gp' as decimal)/1000) as total_gross_revenue,
            sum(cast(logtext->>'ap' as decimal)/1000) as total_auction_revenue
          from
            tracker
          where
            loghour = cast(to_char(NOW(),'YYYY_MM_DD') as text) AND
            logtext->>'adapterType' = 'HTTP' AND
            cast(to_char(to_timestamp(cast(logtext->>'ts' as bigint)/1000),'YYYY-MM-DD HH24:MI:SS') as text)>=cast(to_char($1,'YYYY-MM-DD HH24:MI:00') as text) AND
            cast(to_char(to_timestamp(cast(logtext->>'ts' as bigint)/1000),'YYYY-MM-DD HH24:MI:SS') as text)<cast(to_char($2,'YYYY-MM-DD HH24:MI:00') as text)
          group by
            loghour,
            cast(to_char(to_timestamp(cast(logtext->>'ts' as bigint)/1000),'YYYY-MM-DD') as text),
            cast(to_char(to_timestamp(cast(logtext->>'ts' as bigint)/1000),'YYYY-MM-DD HH24:00:00') as text),
            logtext->>'sid',logtext->>'adSlot',logtext->>'adSize',logtext->>'it',logtext->>'adapterType',logtext->>'pgId',
            logtext->>'dealId',logtext->>'adPlatform'
          ) b
        ON
          a.date_utc=b.date_utc AND
          a.date_hr=b.date_hr AND
          a.site_id=b.site_id AND
          a.ad_size=b.ad_size AND
          a.loghour=b.loghour
        GROUP BY
          a.loghour,
          b.loghour,
          CASE WHEN a.date_utc IS NULL THEN b.date_utc ELSE a.date_utc END,
          CASE WHEN a.date_hr IS NULL THEN b.date_hr ELSE a.date_hr END,
          CASE WHEN a.site_id IS NULL THEN b.site_id ELSE a.site_id END,
          CASE WHEN a.ad_slot=b.ad_slot OR a.ad_slot IS NULL THEN b.ad_slot ELSE 'AUM' END,
          CASE WHEN a.ad_size IS NULL THEN b.ad_size ELSE a.ad_size END,
          b.impression_type,
          b.adapter_type,
          b.pub_group_id,
          b.deal_id,
          b.ad_platform) r
      LEFT OUTER JOIN
        (SELECT
          id as site_id,
          account_id as pub_id,
          name as name
        FROM
          site_l
        ) s
      ON
        CAST(r.site_id as int) = s.site_id
    ON CONFLICT (loghour,date_utc, date_hr,siteid,name,adslot,adsize,impressiontype,adaptertype,grouppubid,dealid,adplatform)
    DO UPDATE
    SET bidssent =+ excluded.bidssent,
      adslotrequestsent =+ excluded.adslotrequestsent,
      bidresponses =+ excluded.bidresponses,
      bidwins =+ excluded.bidwins,
      numimpressions =+ excluded.numimpressions,
      numclicks =+ excluded.numclicks,
      totalrevenue =+ excluded.totalrevenue,
      totalnetrevenue =+ excluded.totalnetrevenue,
      totalgrossrevenue =+ excluded.totalgrossrevenue,
      totalauctionrevenue =+ excluded.totalauctionrevenue$$
  USING start_time, end_time;

END;
$function$;