User Historical Purchase Facts

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

SELECT user.id as user_id
      , min(order.completed_at) as min_date
      , max(order.completed_at) as max_date
FROM order_item_consolidated AS order_item
LEFT JOIN variant_consolidated AS variant ON order_item.variant_id = variant.id
LEFT JOIN category AS variant_category ON variant.category_key = variant_category.category_key
LEFT JOIN product_consolidated AS product ON variant.product_id = product.id
LEFT JOIN (SELECT
            *
            , (
            SELECT COUNT(*)
            FROM order_consolidated o
            WHERE o.id <= `order`.id
              AND o.user_id = `order`.user_id
              and o.completed_at IS NOT NULL
            )
           AS user_order_sequence_number
          FROM order_consolidated AS `order`) AS `order` ON order_item.order_id = `order`.id
LEFT JOIN sylius_user_consolidated AS user ON `order`.user_id = user.id
LEFT JOIN promotion_info_coupon_order as spco ON `order`.id = spco.order_id
LEFT JOIN promotion_info_coupon spc ON spco.promotion_coupon_id = spc.id
WHERE 1=1
AND {% condition product_name %} product_name {% endcondition %}
AND {% condition category_name %} variant_category.name {% endcondition %}
AND {% condition unit_price %} order_item.unit_price/100.0 {% endcondition %}
AND {% condition date_filter %} order.completed_at {% endcondition %}
AND {% condition order_total %} order.total/100.0 {% endcondition %}
AND {% condition order_sequence_number %} order.user_order_sequence_number {% endcondition %}
AND (CAST(CONVERT_TZ(`order`.completed_at,'UTC','America/Los_Angeles') AS CHAR)) is not null
AND order.deleted_at is null
AND (spc.code IS NULL OR spc.code <> <% parameter promo_code2 %>)
AND user_id is not null
Group by 1

# models/schema.yml
version: 2

models:
  - name: user_historical_purchase_facts
    columns:
      - name: user_id
        tests:
          - unique
          - not_null

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 as user_id
      , min(order.completed_at) as min_date
      , max(order.completed_at) as max_date
FROM order_item_consolidated AS order_item
LEFT JOIN variant_consolidated AS variant ON order_item.variant_id = variant.id
LEFT JOIN category AS variant_category ON variant.category_key = variant_category.category_key
LEFT JOIN product_consolidated AS product ON variant.product_id = product.id
LEFT JOIN (SELECT
            *
            , (
            SELECT COUNT(*)
            FROM order_consolidated o
            WHERE o.id <= `order`.id
              AND o.user_id = `order`.user_id
              and o.completed_at IS NOT NULL
            )
           AS user_order_sequence_number
          FROM order_consolidated AS `order`) AS `order` ON order_item.order_id = `order`.id
LEFT JOIN sylius_user_consolidated AS user ON `order`.user_id = user.id
LEFT JOIN promotion_info_coupon_order as spco ON `order`.id = spco.order_id
LEFT JOIN promotion_info_coupon spc ON spco.promotion_coupon_id = spc.id
WHERE 1=1
AND {% condition product_name %} product_name {% endcondition %}
AND {% condition category_name %} variant_category.name {% endcondition %}
AND {% condition unit_price %} order_item.unit_price/100.0 {% endcondition %}
AND {% condition date_filter %} order.completed_at {% endcondition %}
AND {% condition order_total %} order.total/100.0 {% endcondition %}
AND {% condition order_sequence_number %} order.user_order_sequence_number {% endcondition %}
AND (CAST(CONVERT_TZ(`order`.completed_at,'UTC','America/Los_Angeles') AS CHAR)) is not null
AND order.deleted_at is null
AND (spc.code IS NULL OR spc.code <> <% parameter promo_code2 %>)
AND user_id is not null
Group by 1

view: user_historical_purchase_facts {
  derived_table: {
    sql: SELECT user.id as user_id
      , min(order.completed_at) as min_date
      , max(order.completed_at) as max_date
FROM order_item_consolidated AS order_item
LEFT JOIN variant_consolidated AS variant ON order_item.variant_id = variant.id
LEFT JOIN category AS variant_category ON variant.category_key = variant_category.category_key
LEFT JOIN product_consolidated AS product ON variant.product_id = product.id
LEFT JOIN (SELECT
            *
            , (
            SELECT COUNT(*)
            FROM order_consolidated o
            WHERE o.id <= `order`.id
              AND o.user_id = `order`.user_id
              and o.completed_at IS NOT NULL
            )
           AS user_order_sequence_number
          FROM order_consolidated AS `order`) AS `order` ON order_item.order_id = `order`.id
LEFT JOIN sylius_user_consolidated AS user ON `order`.user_id = user.id
LEFT JOIN promotion_info_coupon_order as spco ON `order`.id = spco.order_id
LEFT JOIN promotion_info_coupon spc ON spco.promotion_coupon_id = spc.id
WHERE 1=1
AND {% condition product_name %} product_name {% endcondition %}
AND {% condition category_name %} variant_category.name {% endcondition %}
AND {% condition unit_price %} order_item.unit_price/100.0 {% endcondition %}
AND {% condition date_filter %} order.completed_at {% endcondition %}
AND {% condition order_total %} order.total/100.0 {% endcondition %}
AND {% condition order_sequence_number %} order.user_order_sequence_number {% endcondition %}
AND (CAST(CONVERT_TZ(`order`.completed_at,'UTC','America/Los_Angeles') AS CHAR)) is not null
AND order.deleted_at is null
AND (spc.code IS NULL OR spc.code <> <% parameter promo_code2 %>)
AND user_id is not null
Group by 1 ;;
  }

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

  dimension_group: first_filtered_purchase {
    description: "First purchase date matching any user_historical_purchase_facts filters applied. Use User.first_purchase for customer's first purchase"
    type: time
    timeframes: [time, date, week, month, year]
    sql: ${TABLE}.min_date ;;
  }

  dimension_group: last_filtered_purchase {
    description: "Last purchase date matching any user_historical_purchase_facts filters applied. Use User.last_purchase for customer's last purchase"
    type: time
    timeframes: [time, date, week, month, year]
    sql: ${TABLE}.max_date ;;
  }

  measure: count_first_purchase_users {
    type: count_distinct
    sql: ${user_id} ;;
  }
}

- view: user_historical_purchase_facts
  derived_table:
    sql: SELECT user.id as user_id
      , min(order.completed_at) as min_date
      , max(order.completed_at) as max_date
FROM order_item_consolidated AS order_item
LEFT JOIN variant_consolidated AS variant ON order_item.variant_id = variant.id
LEFT JOIN category AS variant_category ON variant.category_key = variant_category.category_key
LEFT JOIN product_consolidated AS product ON variant.product_id = product.id
LEFT JOIN (SELECT
            *
            , (
            SELECT COUNT(*)
            FROM order_consolidated o
            WHERE o.id <= `order`.id
              AND o.user_id = `order`.user_id
              and o.completed_at IS NOT NULL
            )
           AS user_order_sequence_number
          FROM order_consolidated AS `order`) AS `order` ON order_item.order_id = `order`.id
LEFT JOIN sylius_user_consolidated AS user ON `order`.user_id = user.id
LEFT JOIN promotion_info_coupon_order as spco ON `order`.id = spco.order_id
LEFT JOIN promotion_info_coupon spc ON spco.promotion_coupon_id = spc.id
WHERE 1=1
AND {% condition product_name %} product_name {% endcondition %}
AND {% condition category_name %} variant_category.name {% endcondition %}
AND {% condition unit_price %} order_item.unit_price/100.0 {% endcondition %}
AND {% condition date_filter %} order.completed_at {% endcondition %}
AND {% condition order_total %} order.total/100.0 {% endcondition %}
AND {% condition order_sequence_number %} order.user_order_sequence_number {% endcondition %}
AND (CAST(CONVERT_TZ(`order`.completed_at,'UTC','America/Los_Angeles') AS CHAR)) is not null
AND order.deleted_at is null
AND (spc.code IS NULL OR spc.code <> <% parameter promo_code2 %>)
AND user_id is not null
Group by 1
  fields:
  - dimension: user_id
    type: number
    primary_key: true
    hidden: true
    sql: ${TABLE}.user_id
  - dimension_group: first_filtered_purchase
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - year
    sql: ${TABLE}.min_date
  - dimension_group: last_filtered_purchase
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - year
    sql: ${TABLE}.max_date
  - measure: count_first_purchase_users
    type: count_distinct
    sql: ${user_id}