User Orders Facts

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

SELECT
        coalesce(d.user_id, o.owner_id) as user_id
        , count(distinct o.id) AS total_paid_orders
        , min(o.created_at) as first_paid_order
        , max(o.created_at) as latest_paid_order
        , sum(o.amount) as lifetime_value
        , min(d.created_at) as first_digital_order
        , max(d.created_at) as latest_digital_order
        , count(distinct d.id) as total_digital_orders
        , count(*) as total_orders
        , DATE_PART('day', min(o.created_at) - min(d.created_at) ) as days_from_first_dig_to_first_paid
FROM orders as o
FULL OUTER JOIN digital_orders as d
ON o.owner_id = d.user_id
GROUP BY 1

# models/schema.yml
version: 2

models:
  - name: user_orders_facts
    columns:
      - name: user_id
        tests:
          - unique
          - not_null
      - name: total_paid_orders
      - name: latest_paid_order
      - name: lifetime_value
      - name: total_digital_orders
      - name: total_orders
      - name: placed_digital_order_first
      - name: days_from_first_dig_to_first_paid
      - name: days_from_first_dig_to_first_paid_tiers

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
        coalesce(d.user_id, o.owner_id) as user_id
        , count(distinct o.id) AS total_paid_orders
        , min(o.created_at) as first_paid_order
        , max(o.created_at) as latest_paid_order
        , sum(o.amount) as lifetime_value
        , min(d.created_at) as first_digital_order
        , max(d.created_at) as latest_digital_order
        , count(distinct d.id) as total_digital_orders
        , count(*) as total_orders
        , DATE_PART('day', min(o.created_at) - min(d.created_at) ) as days_from_first_dig_to_first_paid
FROM orders as o
FULL OUTER JOIN digital_orders as d
ON o.owner_id = d.user_id
GROUP BY 1

view: user_orders_facts {
  derived_table: {
    sql: SELECT
        coalesce(d.user_id, o.owner_id) as user_id
        , count(distinct o.id) AS total_paid_orders
        , min(o.created_at) as first_paid_order
        , max(o.created_at) as latest_paid_order
        , sum(o.amount) as lifetime_value
        , min(d.created_at) as first_digital_order
        , max(d.created_at) as latest_digital_order
        , count(distinct d.id) as total_digital_orders
        , count(*) as total_orders
        , DATE_PART('day', min(o.created_at) - min(d.created_at) ) as days_from_first_dig_to_first_paid
FROM orders as o
FULL OUTER JOIN digital_orders as d
ON o.owner_id = d.user_id
GROUP BY 1 ;;
  }

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

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

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

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

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

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

  dimension: placed_digital_order_first {
    type: yesno
    sql: ${first_digital_order_date} < ${first_paid_order_date} ;;
  }

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

  dimension: days_from_first_dig_to_first_paid_tiers {
    type: tier
    sql: ${days_from_first_dig_to_first_paid} ;;
  }

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

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

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

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

- view: user_orders_facts
  derived_table:
    sql: SELECT
        coalesce(d.user_id, o.owner_id) as user_id
        , count(distinct o.id) AS total_paid_orders
        , min(o.created_at) as first_paid_order
        , max(o.created_at) as latest_paid_order
        , sum(o.amount) as lifetime_value
        , min(d.created_at) as first_digital_order
        , max(d.created_at) as latest_digital_order
        , count(distinct d.id) as total_digital_orders
        , count(*) as total_orders
        , DATE_PART('day', min(o.created_at) - min(d.created_at) ) as days_from_first_dig_to_first_paid
FROM orders as o
FULL OUTER JOIN digital_orders as d
ON o.owner_id = d.user_id
GROUP BY 1
  fields:
  - dimension: user_id
    type: number
    primary_key: true
    sql: ${TABLE}.user_id
  - dimension: total_paid_orders
    type: number
    sql: ${TABLE}.total_paid_orders
  - dimension: latest_paid_order
    sql: ${TABLE}.latest_paid_order
  - dimension: lifetime_value
    type: number
    sql: ${TABLE}.lifetime_value
  - dimension: total_digital_orders
    type: number
    sql: ${TABLE}.total_digital_orders
  - dimension: total_orders
    type: number
    sql: ${TABLE}.total_orders
  - dimension: placed_digital_order_first
    type: yesno
    sql: ${first_digital_order_date} < ${first_paid_order_date}
  - dimension: days_from_first_dig_to_first_paid
    sql: ${TABLE}.days_from_first_dig_to_first_paid
  - dimension: days_from_first_dig_to_first_paid_tiers
    type: tier
    sql: ${days_from_first_dig_to_first_paid}
  - dimension_group: first_paid_order
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.first_paid_order
  - dimension_group: first_digital_order
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.first_digital_order
  - dimension_group: latest_digital_order
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.latest_digital_order
  - measure: count
    type: count
    drill_fields:
    - detail*