Company Transactions

-- models/company_transactions.sql
{{ config(materialized='table') }}

SELECT
  domain_userid,
  domain_sessionidx,
  tr_orderid,
  tr_affiliation,
  tr_total,
  tr_tax,
  tr_city,
  tr_state,
  tr_country,
  MIN(collector_tstamp) AS tr_tstamp
FROM company_analytics.events
WHERE event = 'transaction'
GROUP BY 1,2,3,4,5,5,6,7,8,9,10
ORDER BY tr_orderid

# models/schema.yml
version: 2

models:
  - name: company_transactions
    columns:
      - name: user_id
      - name: session_index
      - name: session_id
      - name: order_id
        tests:
          - unique
          - not_null
      - name: affiliation
      - name: transaction_value
      - name: tax
      - name: city
      - name: state
      - name: country
      - name: order_items

Assumptions

• materialized='table' inferred from a LookML persist hint (persist_for / datagroup / sql_trigger_value).
• schema.yml columns come from view dimensions; a primary_key dimension gets unique + not_null tests.

SELECT
  domain_userid,
  domain_sessionidx,
  tr_orderid,
  tr_affiliation,
  tr_total,
  tr_tax,
  tr_city,
  tr_state,
  tr_country,
  MIN(collector_tstamp) AS tr_tstamp
FROM company_analytics.events
WHERE event = 'transaction'
GROUP BY 1,2,3,4,5,5,6,7,8,9,10
ORDER BY tr_orderid

view: company_transactions {
  derived_table: {
    sql: SELECT
  domain_userid,
  domain_sessionidx,
  tr_orderid,
  tr_affiliation,
  tr_total,
  tr_tax,
  tr_city,
  tr_state,
  tr_country,
  MIN(collector_tstamp) AS tr_tstamp
FROM company_analytics.events
WHERE event = 'transaction'
GROUP BY 1,2,3,4,5,5,6,7,8,9,10
ORDER BY tr_orderid ;;
    persist_for: "24 hours"
  }

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

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

  dimension: session_id {
    sql: ${TABLE}.domain_userid || '-' || ${TABLE}.domain_sessionidx ;;
  }

  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_group: occurred {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.tr_tstamp ;;
  }

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

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

- view: company_transactions
  derived_table:
    sql: SELECT
  domain_userid,
  domain_sessionidx,
  tr_orderid,
  tr_affiliation,
  tr_total,
  tr_tax,
  tr_city,
  tr_state,
  tr_country,
  MIN(collector_tstamp) AS tr_tstamp
FROM company_analytics.events
WHERE event = 'transaction'
GROUP BY 1,2,3,4,5,5,6,7,8,9,10
ORDER BY tr_orderid
    persist_for: 24 hours
  fields:
  - dimension: user_id
    sql: ${TABLE}.domain_userid
  - dimension: session_index
    type: int
    sql: ${TABLE}.domain_sessionidx
  - dimension: session_id
    sql: ${TABLE}.domain_userid || '-' || ${TABLE}.domain_sessionidx
  - 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_group: occurred
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.tr_tstamp
  - measure: transactions_count
    type: count_distinct
    sql: ${order_id}
  - measure: transactions_value
    type: sum
    sql: ${transaction_value}