create_user_dim.sql

--USER_DIM:
--Grain:        1 row per user
--Surrogate Key: visitor_ip + visitor_network_id + visitor_site_id
--Dimensions: ip address fields 
--Calculated Dimensions:   user_type (loyalty, returning, new); engaged_user (currently defined as >X sessions in full data set)

CREATE TABLE public.user_dim (
-- keys
        user_key                            INTEGER NOT NULL,
-- identifying fields
        visitor_ip                          VARCHAR(256),
        visitor_network_id                  VARCHAR(128),
        visitor_site_id                     VARCHAR(128) NOT NULL
-- derived dimensions
        user_type                           VARCHAR(256),
        engaged_user                        VARCHAR(256),
-- dimensions
        ip_city                             VARCHAR(256),
        ip_continent                        VARCHAR(256),
        ip_country                          VARCHAR(256),
        ip_lat                              VARCHAR(256),
        ip_lon                              VARCHAR(256),
        ip_postal                           VARCHAR(256),
        ip_subdivision                      VARCHAR(256),
        ip_timezone                         VARCHAR(256),
        ip_market_name                      VARCHAR(256),
        ip_market_nielsen                   VARCHAR(256),
        ip_market_doubleclick               VARCHAR(256)
) distkey(user_key) sortkey(user_key;

  CREATE TABLE public.user_dim
  distkey(user_key)
  sortkey(user_key)
  as
  select distinct
  --- ids
    B.user_key,
    visitor_ip,
    visitor_site_id,
    ip_city,
    ip_continent,
    ip_country,
    ip_lat,
    ip_lon,
    ip_postal,
    ip_subdivision,
    ip_timezone,
    ip_market_name,
    ip_market_nielsen,
    ip_market_doubleclick,
    case when
      num_of_sessions > 2
      then 1 else 0 end as engaged_user,
      case
          when num_of_sessions > 2 then 'Loyalty'
          when num_of_sessions > 1 then 'Returning'
          else 'One Time' end as user_type
  --- dimensions
  from
    (select
      user_key,
      count(distinct session_id) as num_of_sessions
    from public.tmp_dpl_staging
    where action = 'pageview'
    group by user_key) A
  join
    (select distinct
      user_key,
      visitor_ip,
      visitor_site_id,
      ip_city,
      ip_continent,
      ip_country,
      ip_lat,
      ip_lon,
      ip_postal,
      ip_subdivision,
      ip_timezone,
      ip_market_name,
      ip_market_nielsen,
      ip_market_doubleclick
    from public.tmp_dpl_staging) B
  on A.user_key = B.user_key;