Event Session Map

-- models/event_session_map.sql
{{ config(materialized='view') }}

select em.event_id
  , em.event_type
  , s.domain_userid
  , s.session_id
  , row_number() over(partition by s.session_id order by em.e_tstamp) as sess_em_seq_num
  , FIRST_VALUE(em.event_type)
      over (partition by em.domain_userid order by em.e_tstamp ROWS BETWEEN unbounded preceding AND unbounded following) as user_entry_event
  , LAST_VALUE(em.event_type)
      over (partition by s.session_id order by em.e_tstamp ROWS BETWEEN unbounded preceding AND unbounded following) as session_exit_event
from {{ ref('events_mapped_to_user') }} as em
inner join {{ ref('sessions') }} as  s
  on em.domain_userid = s.domain_userid
  and em.e_tstamp >= s.session_start
  and em.e_tstamp < s.next_session_start
  -- limit 10

# models/schema.yml
version: 2

models:
  - name: event_session_map
    columns:
      - name: event_id
        tests:
          - unique
          - not_null
      - name: event_type
      - name: domain_userid
      - name: session_id
      - name: sess_em_seq_num
      - name: user_entry_event
      - name: session_exit_event

Assumptions

• Each ${view.SQL_TABLE_NAME} was mapped to {{ ref('view') }}; confirm a dbt model exists for each referenced view.
• materialized='view' is the default; no persist hint found.
• schema.yml columns come from view dimensions; a primary_key dimension gets unique + not_null tests.

select em.event_id
  , em.event_type
  , s.domain_userid
  , s.session_id
  , row_number() over(partition by s.session_id order by em.e_tstamp) as sess_em_seq_num
  , FIRST_VALUE(em.event_type)
      over (partition by em.domain_userid order by em.e_tstamp ROWS BETWEEN unbounded preceding AND unbounded following) as user_entry_event
  , LAST_VALUE(em.event_type)
      over (partition by s.session_id order by em.e_tstamp ROWS BETWEEN unbounded preceding AND unbounded following) as session_exit_event
from ${events_mapped_to_user.SQL_TABLE_NAME} as em
inner join ${sessions.SQL_TABLE_NAME} as  s
  on em.domain_userid = s.domain_userid
  and em.e_tstamp >= s.session_start
  and em.e_tstamp < s.next_session_start
  -- limit 10

view: event_session_map {
  derived_table: {
    sql: select em.event_id
  , em.event_type
  , s.domain_userid
  , s.session_id
  , row_number() over(partition by s.session_id order by em.e_tstamp) as sess_em_seq_num
  , FIRST_VALUE(em.event_type)
      over (partition by em.domain_userid order by em.e_tstamp ROWS BETWEEN unbounded preceding AND unbounded following) as user_entry_event
  , LAST_VALUE(em.event_type)
      over (partition by s.session_id order by em.e_tstamp ROWS BETWEEN unbounded preceding AND unbounded following) as session_exit_event
from ${events_mapped_to_user.SQL_TABLE_NAME} as em
inner join ${sessions.SQL_TABLE_NAME} as  s
  on em.domain_userid = s.domain_userid
  and em.e_tstamp >= s.session_start
  and em.e_tstamp < s.next_session_start
  -- limit 10 ;;
  }

  dimension: event_id {
    primary_key: yes
    sql: ${TABLE}.event_id ;;
  }

  dimension: event_type {
    sql: ${TABLE}.event_type ;;
  }

  dimension: domain_userid {
    sql: ${TABLE}.domain_userid ;;
  }

  dimension: session_id {
    sql: ${TABLE}.session_id ;;
  }

  dimension: sess_em_seq_num {
    type: number
    sql: ${TABLE}.sess_em_seq_num ;;
  }

  dimension: user_entry_event {
    sql: ${TABLE}.user_entry_event ;;
  }

  dimension: session_exit_event {
    sql: ${TABLE}.session_exit_event ;;
  }

  measure: count {
    type: count
    drill_fields: [drill_fields*]
  }
}

- view: event_session_map
  derived_table:
    sql: select em.event_id
  , em.event_type
  , s.domain_userid
  , s.session_id
  , row_number() over(partition by s.session_id order by em.e_tstamp) as sess_em_seq_num
  , FIRST_VALUE(em.event_type)
      over (partition by em.domain_userid order by em.e_tstamp ROWS BETWEEN unbounded preceding AND unbounded following) as user_entry_event
  , LAST_VALUE(em.event_type)
      over (partition by s.session_id order by em.e_tstamp ROWS BETWEEN unbounded preceding AND unbounded following) as session_exit_event
from ${events_mapped_to_user.SQL_TABLE_NAME} as em
inner join ${sessions.SQL_TABLE_NAME} as  s
  on em.domain_userid = s.domain_userid
  and em.e_tstamp >= s.session_start
  and em.e_tstamp < s.next_session_start
  -- limit 10
  fields:
  - dimension: event_id
    primary_key: true
    sql: ${TABLE}.event_id
  - dimension: event_type
    sql: ${TABLE}.event_type
  - dimension: domain_userid
    sql: ${TABLE}.domain_userid
  - dimension: session_id
    sql: ${TABLE}.session_id
  - dimension: sess_em_seq_num
    type: number
    sql: ${TABLE}.sess_em_seq_num
  - dimension: user_entry_event
    sql: ${TABLE}.user_entry_event
  - dimension: session_exit_event
    sql: ${TABLE}.session_exit_event
  - measure: count
    type: count
    drill_fields:
    - drill_fields*