create_session_fact.sql

--SESSION_FACT:
--Grain:        1 row per apikey, session, user
--Metrics:      engaged_time, pageviews, visitors, sessions

-- keys
        session_fact_key                    INTEGER NOT NULL,
--      session_detail_key,
        user_key,
        session_key,
        session_start_time_key              TIMESTAMP,
        previous_session_start_time_key     TIMESTAMP,
-- fields
        engaged_time                        INTEGER,
        pageviews                           INTEGER,
        visitors                            INTEGER,
        sessions                            INTEGER
) distkey(session_fact_key) sortkey(session_fact_key);


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