Transactions

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

select * from (
SELECT
  domain_userid,
  domain_sessionidx,
  domain_userid || '-' || domain_sessionidx AS visit_id,
  tr_orderid,
  tr_affiliation,
  tr_total,
  tr_tax,
  tr_city,
  tr_state,
  tr_country,
  collector_tstamp AS tr_tstamp,
  event_id,
  row_number() over (partition by domain_userid, domain_sessionidx, tr_orderid order by collector_tstamp asc) rn
FROM {{ this }}.events
WHERE event = 'transaction' ) x
where x.rn = 1

# models/schema.yml
version: 2

models:
  - name: transactions
    columns:
      - name: event_id
      - name: domain_user_id
      - name: domain_session_index
      - name: visit_id
      - name: order_id
        tests:
          - unique
          - not_null
      - name: affiliation
      - name: transaction_value
      - name: tax
      - name: city
      - name: state
      - name: country
      - name: order_items
      - name: transaction_apr
      - name: booking_window

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 * from (
SELECT
  domain_userid,
  domain_sessionidx,
  domain_userid || '-' || domain_sessionidx AS visit_id,
  tr_orderid,
  tr_affiliation,
  tr_total,
  tr_tax,
  tr_city,
  tr_state,
  tr_country,
  collector_tstamp AS tr_tstamp,
  event_id,
  row_number() over (partition by domain_userid, domain_sessionidx, tr_orderid order by collector_tstamp asc) rn
FROM ${TABLE}.events
WHERE event = 'transaction' ) x
where x.rn = 1

view: transactions {
  derived_table: {
    sql: select * from (
SELECT
  domain_userid,
  domain_sessionidx,
  domain_userid || '-' || domain_sessionidx AS visit_id,
  tr_orderid,
  tr_affiliation,
  tr_total,
  tr_tax,
  tr_city,
  tr_state,
  tr_country,
  collector_tstamp AS tr_tstamp,
  event_id,
  row_number() over (partition by domain_userid, domain_sessionidx, tr_orderid order by collector_tstamp asc) rn
FROM ${TABLE}.events
WHERE event = 'transaction' ) x
where x.rn = 1 ;;
  }

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

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

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

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

  dimension: order_id {
    primary_key: yes
    sql: ${TABLE}.tr_orderid ;;
  }

  dimension: affiliation {
    sql: ${TABLE}.tr_affiliation ;;
  }

  dimension: transaction_value {
    type: number
    sql: ${TABLE}.tr_total ;;
  }

  dimension: tax {
    type: number
    sql: ${TABLE}.tr_tax ;;
  }

  dimension: city {
    sql: ${TABLE}.tr_city ;;
  }

  dimension: state {
    sql: ${TABLE}.tr_state ;;
  }

  dimension: country {
    sql: ${TABLE}.tr_country ;;
  }

  dimension: order_items {
    sql: ${order_id} ;;
  }

  dimension: transaction_apr {
    type: number
    sql: case when ${transaction_info.checkin_date} is not null then ${TABLE}.tr_total/(case
when ${transaction_info.checkout_date}-${transaction_info.checkin_date} > 0
then ${transaction_info.checkout_date}-${transaction_info.checkin_date} else
1 end) end ;;
  }

  dimension: booking_window {
    type: int
    sql: ((${transaction_info.checkin_date})::date - (${TABLE}.tr_tstamp)::date)::integer ;;
  }

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

  measure: transactions_count {
    type: count_distinct
    sql: ${order_id} ;;
  }

  measure: total_transaction_value {
    type: sum
    sql: ${transaction_value} ;;
  }

  measure: apr {
    type: number
    sql: sum(case when ${transaction_info.checkin_date} is not null then ${TABLE}.tr_total
else null end) / sum(case when ${transaction_info.checkin_date} is not null
then case when ${transaction_info.checkout_date}-${transaction_info.checkin_date}
> 0 then ${transaction_info.checkout_date}-${transaction_info.checkin_date}
else 1 end end) ;;
  }

  measure: avg_booking_window {
    type: average
    sql: (${transaction_info.checkin_date}::date - ${TABLE}.tr_tstamp::date)::integer ;;
  }
}

- view: transactions
  derived_table:
    sql: select * from (
SELECT
  domain_userid,
  domain_sessionidx,
  domain_userid || '-' || domain_sessionidx AS visit_id,
  tr_orderid,
  tr_affiliation,
  tr_total,
  tr_tax,
  tr_city,
  tr_state,
  tr_country,
  collector_tstamp AS tr_tstamp,
  event_id,
  row_number() over (partition by domain_userid, domain_sessionidx, tr_orderid order by collector_tstamp asc) rn
FROM ${TABLE}.events
WHERE event = 'transaction' ) x
where x.rn = 1
  fields:
  - dimension: event_id
    sql: ${TABLE}.event_id
  - dimension: domain_user_id
    sql: ${TABLE}.domain_userid
  - dimension: domain_session_index
    type: int
    sql: ${TABLE}.domain_sessionidx
  - dimension: visit_id
    sql: ${TABLE}.visit_id
  - dimension: order_id
    primary_key: true
    sql: ${TABLE}.tr_orderid
  - dimension: affiliation
    sql: ${TABLE}.tr_affiliation
  - dimension: transaction_value
    type: number
    sql: ${TABLE}.tr_total
  - dimension: tax
    type: number
    sql: ${TABLE}.tr_tax
  - dimension: city
    sql: ${TABLE}.tr_city
  - dimension: state
    sql: ${TABLE}.tr_state
  - dimension: country
    sql: ${TABLE}.tr_country
  - dimension: order_items
    sql: ${order_id}
  - dimension: transaction_apr
    type: number
    sql: case when ${transaction_info.checkin_date} is not null then ${TABLE}.tr_total/(case
when ${transaction_info.checkout_date}-${transaction_info.checkin_date} > 0
then ${transaction_info.checkout_date}-${transaction_info.checkin_date} else
1 end) end
  - dimension: booking_window
    type: int
    sql: ((${transaction_info.checkin_date})::date - (${TABLE}.tr_tstamp)::date)::integer
  - dimension_group: occurred
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.tr_tstamp
  - measure: transactions_count
    type: count_distinct
    sql: ${order_id}
  - measure: total_transaction_value
    type: sum
    sql: ${transaction_value}
  - measure: apr
    type: number
    sql: sum(case when ${transaction_info.checkin_date} is not null then ${TABLE}.tr_total
else null end) / sum(case when ${transaction_info.checkin_date} is not null
then case when ${transaction_info.checkout_date}-${transaction_info.checkin_date}
> 0 then ${transaction_info.checkout_date}-${transaction_info.checkin_date}
else 1 end end)
  - measure: avg_booking_window
    type: average
    sql: (${transaction_info.checkin_date}::date - ${TABLE}.tr_tstamp::date)::integer