User Order Facts

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

select
user_id
,count(*) as lifetime_orders
,min(completed_at) as first_order
,max(completed_at) as last_order
,sum(total) as lifetime_order_value
,avg(total) as average_order_value


from spree_orders
where locked_at > 0
and user_id > 0
group by user_id

# models/schema.yml
version: 2

models:
  - name: user_order_facts
    columns:
      - name: user_id
        tests:
          - unique
          - not_null
      - name: lifetime_orders
      - name: completed_qtr
      - name: user_lifetime_order_value
      - name: user_average_order_value

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
user_id
,count(*) as lifetime_orders
,min(completed_at) as first_order
,max(completed_at) as last_order
,sum(total) as lifetime_order_value
,avg(total) as average_order_value


from spree_orders
where locked_at > 0
and user_id > 0
group by user_id

view: user_order_facts {
  derived_table: {
    sql: select
user_id
,count(*) as lifetime_orders
,min(completed_at) as first_order
,max(completed_at) as last_order
,sum(total) as lifetime_order_value
,avg(total) as average_order_value


from spree_orders
where locked_at > 0
and user_id > 0
group by user_id ;;
  }

  dimension: user_id {
    type: number
    primary_key: yes
    sql: ${TABLE}.user_id ;;
  }

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

  dimension: completed_qtr {
    sql: CONCAT(${first_order_year},' ','Q',FLOOR(MONTH(${first_order_date})/4)+1) ;;
  }

  dimension: user_lifetime_order_value {
    type: number
    sql: ${TABLE}.lifetime_order_value ;;
  }

  dimension: user_average_order_value {
    type: number
    sql: ${TABLE}.average_order_value ;;
  }

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

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

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

- view: user_order_facts
  derived_table:
    sql: select
user_id
,count(*) as lifetime_orders
,min(completed_at) as first_order
,max(completed_at) as last_order
,sum(total) as lifetime_order_value
,avg(total) as average_order_value


from spree_orders
where locked_at > 0
and user_id > 0
group by user_id
  fields:
  - dimension: user_id
    type: number
    primary_key: true
    sql: ${TABLE}.user_id
  - dimension: lifetime_orders
    type: number
    sql: ${TABLE}.lifetime_orders
  - dimension: completed_qtr
    sql: CONCAT(${first_order_year},' ','Q',FLOOR(MONTH(${first_order_date})/4)+1)
  - dimension: user_lifetime_order_value
    type: number
    sql: ${TABLE}.lifetime_order_value
  - dimension: user_average_order_value
    type: number
    sql: ${TABLE}.average_order_value
  - dimension_group: first_order
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - year
    sql: ${TABLE}.first_order
  - dimension_group: last_order
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - year
    sql: ${TABLE}.last_order
  - measure: count
    type: count
    drill_fields:
    - detail*