Sessions

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

SELECT
  lag.e_tstamp AS session_start
  , lag.idle_time AS idle_time
  , lag.user_id AS user_id
  , ROW_NUMBER () OVER (ORDER BY lag.e_tstamp) AS unique_session_id
  , RANK() OVER(PARTITION BY lag.user_id ORDER BY lag.e_tstamp) AS session_sequence
  , COALESCE(
        LEAD(lag.e_tstamp) OVER (PARTITION BY lag.user_id ORDER BY lag.e_tstamp)
      , '3000-01-01') AS next_session_start
FROM
    ( SELECT
          timestamp 'epoch' + logs.ts * interval '1 second' AS e_tstamp
        , logs.uid AS user_id
        , DATEDIFF(
            minute,
            LAG(timestamp 'epoch' + logs.ts * interval '1 second') OVER ( PARTITION BY logs.uid ORDER BY logs.ts)
          , timestamp 'epoch' + logs.ts * interval '1 second') AS idle_time
      FROM ts_user_base AS logs
      ) AS lag
WHERE (lag.idle_time > 30 OR lag.idle_time IS NULL)  -- session threshold

# models/schema.yml
version: 2

models:
  - name: sessions
    columns:
      - name: unique_session_id
        tests:
          - unique
          - not_null
      - name: idle_time
      - name: user_id
      - name: session_sequence_by_user
        description: "For a given user, what order did the sessions take place in? 1=First, 2=Second, etc"
      - name: is_first_session

Assumptions

• 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
  lag.e_tstamp AS session_start
  , lag.idle_time AS idle_time
  , lag.user_id AS user_id
  , ROW_NUMBER () OVER (ORDER BY lag.e_tstamp) AS unique_session_id
  , RANK() OVER(PARTITION BY lag.user_id ORDER BY lag.e_tstamp) AS session_sequence
  , COALESCE(
        LEAD(lag.e_tstamp) OVER (PARTITION BY lag.user_id ORDER BY lag.e_tstamp)
      , '3000-01-01') AS next_session_start
FROM
    ( SELECT
          timestamp 'epoch' + logs.ts * interval '1 second' AS e_tstamp
        , logs.uid AS user_id
        , DATEDIFF(
            minute,
            LAG(timestamp 'epoch' + logs.ts * interval '1 second') OVER ( PARTITION BY logs.uid ORDER BY logs.ts)
          , timestamp 'epoch' + logs.ts * interval '1 second') AS idle_time
      FROM ts_user_base AS logs
      ) AS lag
WHERE (lag.idle_time > 30 OR lag.idle_time IS NULL)  -- session threshold

view: sessions {
  derived_table: {
    sql: SELECT
  lag.e_tstamp AS session_start
  , lag.idle_time AS idle_time
  , lag.user_id AS user_id
  , ROW_NUMBER () OVER (ORDER BY lag.e_tstamp) AS unique_session_id
  , RANK() OVER(PARTITION BY lag.user_id ORDER BY lag.e_tstamp) AS session_sequence
  , COALESCE(
        LEAD(lag.e_tstamp) OVER (PARTITION BY lag.user_id ORDER BY lag.e_tstamp)
      , '3000-01-01') AS next_session_start
FROM
    ( SELECT
          timestamp 'epoch' + logs.ts * interval '1 second' AS e_tstamp
        , logs.uid AS user_id
        , DATEDIFF(
            minute,
            LAG(timestamp 'epoch' + logs.ts * interval '1 second') OVER ( PARTITION BY logs.uid ORDER BY logs.ts)
          , timestamp 'epoch' + logs.ts * interval '1 second') AS idle_time
      FROM ts_user_base AS logs
      ) AS lag
WHERE (lag.idle_time > 30 OR lag.idle_time IS NULL)  -- session threshold ;;
  }

  dimension: unique_session_id {
    type: int
    primary_key: yes
    sql: ${TABLE}.unique_session_id ;;
  }

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

  dimension: user_id {
    hidden: yes
    sql: ${TABLE}.user_id ;;
  }

  dimension: session_sequence_by_user {
    description: "For a given user, what order did the sessions take place in? 1=First, 2=Second, etc"
    type: number
    sql: ${TABLE}.session_sequence ;;
  }

  dimension: is_first_session {
    type: yesno
    sql: ${session_sequence_by_user} = 1 ;;
  }

  dimension_group: start {
    type: time
    timeframes: [time, date, week, month, hour_of_day, day_of_week, hour, raw]
    sql: ${TABLE}.session_start ;;
  }

  dimension_group: next_session_start {
    type: time
    timeframes: [time, date, week, month, raw]
    sql: ${TABLE}.next_session_start ;;
  }

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

  measure: count_distinct_users {
    type: count_distinct
    drill_fields: [detail*]
    sql: ${user_id} ;;
  }
}

- view: sessions
  derived_table:
    sql: SELECT
  lag.e_tstamp AS session_start
  , lag.idle_time AS idle_time
  , lag.user_id AS user_id
  , ROW_NUMBER () OVER (ORDER BY lag.e_tstamp) AS unique_session_id
  , RANK() OVER(PARTITION BY lag.user_id ORDER BY lag.e_tstamp) AS session_sequence
  , COALESCE(
        LEAD(lag.e_tstamp) OVER (PARTITION BY lag.user_id ORDER BY lag.e_tstamp)
      , '3000-01-01') AS next_session_start
FROM
    ( SELECT
          timestamp 'epoch' + logs.ts * interval '1 second' AS e_tstamp
        , logs.uid AS user_id
        , DATEDIFF(
            minute,
            LAG(timestamp 'epoch' + logs.ts * interval '1 second') OVER ( PARTITION BY logs.uid ORDER BY logs.ts)
          , timestamp 'epoch' + logs.ts * interval '1 second') AS idle_time
      FROM ts_user_base AS logs
      ) AS lag
WHERE (lag.idle_time > 30 OR lag.idle_time IS NULL)  -- session threshold
  fields:
  - dimension: unique_session_id
    type: int
    primary_key: true
    sql: ${TABLE}.unique_session_id
  - dimension: idle_time
    type: number
    sql: ${TABLE}.idle_time
  - dimension: user_id
    hidden: true
    sql: ${TABLE}.user_id
  - dimension: session_sequence_by_user
    type: number
    description: For a given user, what order did the sessions take place in? 1=First, 2=Second, etc
    sql: ${TABLE}.session_sequence
  - dimension: is_first_session
    type: yesno
    sql: ${session_sequence_by_user} = 1
  - dimension_group: start
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - hour_of_day
    - day_of_week
    - hour
    - raw
    sql: ${TABLE}.session_start
  - dimension_group: next_session_start
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - raw
    sql: ${TABLE}.next_session_start
  - measure: count
    type: count
    drill_fields:
    - detail*
  - measure: count_distinct_users
    type: count_distinct
    drill_fields:
    - detail*
    sql: ${user_id}