create_session_content_fact.sql

--SESSION_CONTENT_FACT:
--Grain:    1 row per apikey, session, user, post
--Metrics:  engaged_time, pageviews

CREATE TABLE public.session_content_fact (
-- keys
        session_content_fact_key            PRIMARY KEY,
        content_key                         VARCHAR(256),
        session_fact_key                    VARCHAR(256),
        session_start_time_key              TIMESTAMP,
        previous_session_start_time_key     TIMESTAMP,
--      session_detail_key                  VARCHAR(256),
        user_key                            VARCHAR(256),
        session_key                         VARCHAR(256),
-- metrics
        engaged_time                        INTEGER,
        pageviews                           INTEGER,
        sessions                            INTEGER,
        visitors                            INTEGER
) distkey(session_content_fact_key) sortkey(session_content_fact_key);


CREATE TABLE public.session_content_fact
distkey(session_content_fact_key)
sortkey(session_content_fact_key)
as
select
--- ids
  DPL.session_content_fact_key,
  DPL.content_key,
  DPL.ts_session_current as session_start_time_key,
  DPL.ts_session_last as previous_session_start_time_key,
--DPL.session_detail_key,
  DPL.user_key,
  DPL.session_key,
--- metrics
  sum(DPL.engaged_time_inc) as engaged_time,
  count(distinct DPL.session_id) as sessions,
  1 as visitors,
  PV.pageviews
from public.tmp_dpl_staging DPL
join
(select
  session_content_fact_key,
  count(session_id) as pageviews
from public.tmp_dpl_staging
where action='pageview'
group by session_content_fact_key) PV
on PV.session_content_fact_key = DPL.session_content_fact_key
group by  DPL.session_content_fact_key, PV.pageviews,   DPL.content_key, /*DPL.session_detail_key,*/ DPL.user_key, DPL.session_key;