Last Page

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

SELECT
 domain_userid,
 domain_sessionidx,
 domain_userid || '-' || domain_sessionidx AS visit_id,
 max(page_title) as page_title,
 MAX(page_urlhost) AS page_urlhost, -- Remove duplicates (v. occasional case where two page views have exactly the same dvce_tstamp)
 MAX(page_urlpath) AS page_urlpath  -- Remove duplicates (v. occasional case where two page views have exactly the same dvce_tstamp)
 FROM (
   SELECT
   domain_userid,
   domain_sessionidx,
   page_urlhost,
   page_urlpath,
   page_title,
   RANK() OVER (PARTITION BY domain_userid, domain_sessionidx ORDER BY dvce_tstamp DESC) AS "rank_desc"
   FROM {{ this }}.events
   WHERE event = 'page_view') AS a
 WHERE rank_desc = 1                -- Filter so only return landing pages
 GROUP BY 1,2,3                     -- Remove duplicates (v. occasional case where two page views have exactly the same dvce_tstamp)

# models/schema.yml
version: 2

models:
  - name: last_page
    columns:
      - name: visit_id
        tests:
          - unique
          - not_null
      - name: domain_user_id
      - name: domain_session_index
      - name: page_url_host
      - name: page_url_path
      - name: page_title

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
 domain_userid,
 domain_sessionidx,
 domain_userid || '-' || domain_sessionidx AS visit_id,
 max(page_title) as page_title,
 MAX(page_urlhost) AS page_urlhost, -- Remove duplicates (v. occasional case where two page views have exactly the same dvce_tstamp)
 MAX(page_urlpath) AS page_urlpath  -- Remove duplicates (v. occasional case where two page views have exactly the same dvce_tstamp)
 FROM (
   SELECT
   domain_userid,
   domain_sessionidx,
   page_urlhost,
   page_urlpath,
   page_title,
   RANK() OVER (PARTITION BY domain_userid, domain_sessionidx ORDER BY dvce_tstamp DESC) AS "rank_desc"
   FROM ${TABLE}.events
   WHERE event = 'page_view') AS a
 WHERE rank_desc = 1                -- Filter so only return landing pages
 GROUP BY 1,2,3                     -- Remove duplicates (v. occasional case where two page views have exactly the same dvce_tstamp)

view: last_page {
  derived_table: {
    sql: SELECT
 domain_userid,
 domain_sessionidx,
 domain_userid || '-' || domain_sessionidx AS visit_id,
 max(page_title) as page_title,
 MAX(page_urlhost) AS page_urlhost, -- Remove duplicates (v. occasional case where two page views have exactly the same dvce_tstamp)
 MAX(page_urlpath) AS page_urlpath  -- Remove duplicates (v. occasional case where two page views have exactly the same dvce_tstamp)
 FROM (
   SELECT
   domain_userid,
   domain_sessionidx,
   page_urlhost,
   page_urlpath,
   page_title,
   RANK() OVER (PARTITION BY domain_userid, domain_sessionidx ORDER BY dvce_tstamp DESC) AS "rank_desc"
   FROM ${TABLE}.events
   WHERE event = 'page_view') AS a
 WHERE rank_desc = 1                -- Filter so only return landing pages
 GROUP BY 1,2,3                     -- Remove duplicates (v. occasional case where two page views have exactly the same dvce_tstamp) ;;
  }

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

  dimension: domain_user_id {
    hidden: yes
    sql: ${TABLE}.domain_userid ;;
  }

  dimension: domain_session_index {
    type: int
    hidden: yes
    sql: ${TABLE}.domain_sessionidx ;;
  }

  dimension: page_url_host {
    sql: ${TABLE}.page_urlhost ;;
  }

  dimension: page_url_path {
    sql: ${TABLE}.page_urlpath ;;
  }

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

  measure: page_views_count {
    type: count
  }
}

- view: last_page
  derived_table:
    sql: SELECT
 domain_userid,
 domain_sessionidx,
 domain_userid || '-' || domain_sessionidx AS visit_id,
 max(page_title) as page_title,
 MAX(page_urlhost) AS page_urlhost, -- Remove duplicates (v. occasional case where two page views have exactly the same dvce_tstamp)
 MAX(page_urlpath) AS page_urlpath  -- Remove duplicates (v. occasional case where two page views have exactly the same dvce_tstamp)
 FROM (
   SELECT
   domain_userid,
   domain_sessionidx,
   page_urlhost,
   page_urlpath,
   page_title,
   RANK() OVER (PARTITION BY domain_userid, domain_sessionidx ORDER BY dvce_tstamp DESC) AS "rank_desc"
   FROM ${TABLE}.events
   WHERE event = 'page_view') AS a
 WHERE rank_desc = 1                -- Filter so only return landing pages
 GROUP BY 1,2,3                     -- Remove duplicates (v. occasional case where two page views have exactly the same dvce_tstamp)
  fields:
  - dimension: visit_id
    primary_key: true
    sql: ${TABLE}.visit_id
  - dimension: domain_user_id
    hidden: true
    sql: ${TABLE}.domain_userid
  - dimension: domain_session_index
    type: int
    hidden: true
    sql: ${TABLE}.domain_sessionidx
  - dimension: page_url_host
    sql: ${TABLE}.page_urlhost
  - dimension: page_url_path
    sql: ${TABLE}.page_urlpath
  - dimension: page_title
    sql: ${TABLE}.page_title
  - measure: page_views_count
    type: count