Data Sharing SQL Calculations

with placement_play_sessions as (
select 
    eastern_date
    , site_id
    , placement_id
    , placement_embed_id
    -- , strategy_outcome_id
    -- , placement_eval_id
    , sum(case when embeds > 0 then 1 else 0 end) as embeds
    , sum(case when play_attempts > 0 then 1 else 0 end) as play_attempts
    , sum(case when plays > 0 then 1 else 0 end) as plays
    , sum(case when completes > 0 then 1 else 0 end) as completes
    , sum(ad_requests) as ad_requests
    , sum(ad_impressions) as ad_impressions
    , sum(ad_viewable_impressions) as ad_viewable_impressions
    , sum(ad_completes) as ad_completes
    , sum(ad_clicks) as ad_clicks
    , sum(ad_skips) as ad_skips
    -- , count(distinct viewer_id) as player_viewers
from prd_analytics.public.play_sessions
where placement_id is not null
group by
    eastern_date
    , site_id
    , placement_id
    , placement_embed_id
    -- ,strategy_outcome_id
    -- ,placement_eval_id
)
select
    pcms.site_id
    , pcms.eastern_date
    , pcms.placement_id
    , pcms.strategy_outcome_id
    -- placement_sessions metrics
    , sum(pcms.placement_embed_attempts) as placement_embed_attempts
    , sum(pcms.placement_embeds) as placement_embeds
    , sum(pcms.strategy_evaluations) as strategy_evaluations
    , sum(pcms.strategy_evaluation_errors) as strategy_evaluation_errors
    -- , count(distinct pcms.viewer_id) as placement_viewers
    -- play_sessions metrics
    , sum(ps.embeds) as embeds
    , sum(ps.play_attempts) as play_attempts
    , sum(ps.plays) as plays
    , sum(ps.completes) as completes
    , sum(ps.ad_requests) as ad_requests
    , sum(ps.ad_impressions) as ad_impressions
    , sum(ps.ad_viewable_impressions) as ad_viewable_impressions
    , sum(ps.ad_completes) as ad_completes
    , sum(ps.ad_clicks) as ad_clicks
    , sum(ps.ad_skips) as ad_skips
    -- , sum(ps.player_viewers) as player_viewers
from prd_analytics.public.placement_sessions pcms 
left join placement_play_sessions ps
    on pcms.eastern_date = ps.eastern_date
    and pcms.site_id = ps.site_id
    and pcms.placement_id = ps.placement_id
    and pcms.placement_embed_id = ps.placement_embed_id
    -- and pcms.strategy_outcome_id = ps.strategy_outcome_id
    -- and pcms.placement_eval_id = ps.placement_evaluation_id
where pcms.eastern_date = current_date - 1
group by 
    pcms.site_id
    , pcms.eastern_date
    , pcms.placement_id
    , pcms.strategy_outcome_id;

with placement_play_sessions as (
select 
    eastern_date
    , site_id
    , placement_id
    , placement_embed_id
    -- , strategy_outcome_id
    -- , placement_eval_id
    , sum(case when embeds > 0 then 1 else 0 end) as embeds
    , sum(case when play_attempts > 0 then 1 else 0 end) as play_attempts
    , sum(case when plays > 0 then 1 else 0 end) as plays
        -- example of how metrics can be split by a dimension
        , sum(case when plays > 0 and country_code = 'US' then 1 else 0 end) as us_plays
        , sum(case when plays > 0 and country_code <> 'US' then 1 else 0 end) as international_plays
        --
    , sum(case when completes > 0 then 1 else 0 end) as completes
    , sum(ad_impressions) as ad_impressions
    , sum(ad_viewable_impressions) as ad_viewable_impressions
    , sum(ad_completes) as ad_completes
    , sum(ad_clicks) as ad_clicks
    , sum(ad_skips) as ad_skips
    , count(distinct viewer_id) as player_viewers
    from play_sessions_view
where placement_id is not null
group by 
    eastern_date
    , site_id
    , placement_id
    , placement_embed_id
)
select 
    pcmd.id as placement_id
    , pcmd.site_id
    , pcmd.strategy_id
    , pcmd.name as placement_name
    , pcmd.description as placement_description
    , pcms.device_type
    -- placement_sessions metrics
    , sum(pcms.placement_loads) as placement_loads
    , sum(pcms.placement_embed_attempts) as placement_embed_attempts
    , sum(pcms.placement_embeds) as placement_embeds
    , sum(pcms.strategy_evaluations) as strategy_evaluations
    , sum(pcms.strategy_evaluation_errors) as strategy_evaluation_errors
    , count(distinct pcms.viewer_id) as placement_viewers
    -- play_sessions metrics
    , sum(ps.embeds) as embeds
    , sum(ps.play_attempts) as play_attempts
    , sum(ps.plays) as plays
        -- example of how metrics can be split by a dimension
        , sum(us_plays) as us_plays
        , sum(international_plays) as international_plays
        --
    , sum(ps.completes) as completes
    , sum(ps.ad_impressions) as ad_impressions
    , sum(ps.ad_viewable_impressions) as ad_viewable_impressions
    , sum(ps.ad_completes) as ad_completes
    , sum(ps.ad_clicks) as ad_clicks
    , sum(ps.ad_skips) as ad_skips
    , sum(player_viewers) as player_viewers
from placement_definitions_view pcmd
left join placement_sessions_view pcms 
    on pcms.placement_id = pcmd.id
left join placement_play_sessions ps
    on ps.site_id = pcms.site_id
    and ps.eastern_date = pcms.eastern_date
    and ps.placement_id = pcms.placement_id
    and ps.placement_embed_id = pcms.placement_embed_id
    -- and ps.strategy_outcome_id = pcms.strategy_outcome_id
    -- and ps.placement_evaluation_id = pcms.placement_eval_id
where 
    pcmd.enabled
    and pcmd.is_deliverable
    and not pcmd.is_deleted
    and pcms.eastern_date >= current_date - 7 and pcms.eastern_date < current_date
group by 
    pcmd.id
    , pcmd.site_id
    , pcmd.strategy_id
    , pcmd.name
    , pcmd.description
    , pcms.device_type