Order Facts

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

SELECT entity_id AS order_id,
        (
          SELECT COUNT(1)
          FROM sales_flat_order a
          WHERE s.customer_id = a.customer_id
          AND a.increment_id <= s.increment_id) AS order_sequence,
          (EXISTS (
                  SELECT 1
                  FROM sales_flat_order a
                  WHERE s.customer_id = a.customer_id
                  AND a.increment_id > s.increment_id
                  )
          ) AS has_next_order
FROM sales_flat_order s
WHERE customer_id IS NOT NULL

# models/schema.yml
version: 2

models:
  - name: order_facts
    columns:
      - name: order_id
        tests:
          - unique
          - not_null
      - name: orders.order_sequence
      - name: orders.has_next_purchase
      - name: orders.is_first_purchase

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 entity_id AS order_id,
        (
          SELECT COUNT(1)
          FROM sales_flat_order a
          WHERE s.customer_id = a.customer_id
          AND a.increment_id <= s.increment_id) AS order_sequence,
          (EXISTS (
                  SELECT 1
                  FROM sales_flat_order a
                  WHERE s.customer_id = a.customer_id
                  AND a.increment_id > s.increment_id
                  )
          ) AS has_next_order
FROM sales_flat_order s
WHERE customer_id IS NOT NULL

view: order_facts {
  derived_table: {
    sql: SELECT entity_id AS order_id,
        (
          SELECT COUNT(1)
          FROM sales_flat_order a
          WHERE s.customer_id = a.customer_id
          AND a.increment_id <= s.increment_id) AS order_sequence,
          (EXISTS (
                  SELECT 1
                  FROM sales_flat_order a
                  WHERE s.customer_id = a.customer_id
                  AND a.increment_id > s.increment_id
                  )
          ) AS has_next_order
FROM sales_flat_order s
WHERE customer_id IS NOT NULL ;;
  }

  dimension: order_id {
    type: int
    primary_key: yes
    hidden: yes
    sql: ${TABLE}.order_id ;;
  }

  dimension: orders.order_sequence {
    type: number
    sql: ${TABLE}.order_sequence ;;
  }

  dimension: orders.has_next_purchase {
    type: yesno
    sql: ${TABLE}.has_next_order = 1 ;;
  }

  dimension: orders.is_first_purchase {
    type: yesno
    sql: ${orders.order_sequence} = 1 ;;
  }

  measure: orders.first_purchase_count {
    type: count
    filters: [orders.is_first_purchase: "True"]
    sql: ${order_id} ;;
  }

  measure: orders.has_next_purchase_count {
    type: count
    filters: [orders.has_next_purchase: "True"]
    sql: ${order_id} ;;
  }

  measure: orders.has_next_purchase_percent {
    type: number
    sql: 100.0 * ${orders.has_next_purchase_count}/ ${orders.count} ;;
  }
}

- view: order_facts
  derived_table:
    sql: SELECT entity_id AS order_id,
        (
          SELECT COUNT(1)
          FROM sales_flat_order a
          WHERE s.customer_id = a.customer_id
          AND a.increment_id <= s.increment_id) AS order_sequence,
          (EXISTS (
                  SELECT 1
                  FROM sales_flat_order a
                  WHERE s.customer_id = a.customer_id
                  AND a.increment_id > s.increment_id
                  )
          ) AS has_next_order
FROM sales_flat_order s
WHERE customer_id IS NOT NULL
  fields:
  - dimension: order_id
    type: int
    primary_key: true
    hidden: true
    sql: ${TABLE}.order_id
  - dimension: orders.order_sequence
    type: number
    sql: ${TABLE}.order_sequence
  - dimension: orders.has_next_purchase
    type: yesno
    sql: ${TABLE}.has_next_order = 1
  - dimension: orders.is_first_purchase
    type: yesno
    sql: ${orders.order_sequence} = 1
  - measure: orders.first_purchase_count
    type: count
    filters:
    - orders.is_first_purchase: True
    sql: ${order_id}
  - measure: orders.has_next_purchase_count
    type: count
    filters:
    - orders.has_next_purchase: True
    sql: ${order_id}
  - measure: orders.has_next_purchase_percent
    type: number
    sql: 100.0 * ${orders.has_next_purchase_count}/ ${orders.count}