Event Order Date Fperformances

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

select
  min(c.date) as earliest_txn_date,
  max(c.date) as latest_txn_date,
  t.EventId as event_id,
  count(distinct b.orderid) as event_order_id_count
from
  customer_purchases as b
inner join
  transactions as c
    on b.orderid = c.orderid
inner join
  event_entries as t
    on b.productid = t.id
where
  b.producttype = 'T'
and
  b.status = 'Payment Received'
group by t.eventid

# models/schema.yml
version: 2

models:
  - name: event_order_date_fperformances
    columns:
      - name: event_order_id_count
        description: "This is the order count for the entire event.  This value will be repeated if the event is split into multiple rows, so do not total this."
      - name: event_id
        tests:
          - unique
          - not_null
      - name: transaction_date_filter_flag

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
  min(c.date) as earliest_txn_date,
  max(c.date) as latest_txn_date,
  t.EventId as event_id,
  count(distinct b.orderid) as event_order_id_count
from
  customer_purchases as b
inner join
  transactions as c
    on b.orderid = c.orderid
inner join
  event_entries as t
    on b.productid = t.id
where
  b.producttype = 'T'
and
  b.status = 'Payment Received'
group by t.eventid

view: event_order_date_fperformances {
  derived_table: {
    sql: select
  min(c.date) as earliest_txn_date,
  max(c.date) as latest_txn_date,
  t.EventId as event_id,
  count(distinct b.orderid) as event_order_id_count
from
  customer_purchases as b
inner join
  transactions as c
    on b.orderid = c.orderid
inner join
  event_entries as t
    on b.productid = t.id
where
  b.producttype = 'T'
and
  b.status = 'Payment Received'
group by t.eventid ;;
  }

  dimension: event_order_id_count {
    label: "Unique Orders Count (for entire event)"
    description: "This is the order count for the entire event.  This value will be repeated if the event is split into multiple rows, so do not total this."
    type: number
    sql: IF(${TABLE}.event_order_id_count > 0, ${TABLE}.event_order_id_count, 0) ;;
  }

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

  dimension: transaction_date_filter_flag {
    label: "Earliest Transaction Date Filter Flag"
    type: yesno
    sql: (  {% condition transaction_date_filter %} ${earliest_txn_date} {% endcondition %}
 ) ;;
  }

  dimension_group: earliest_txn {
    label: "Earliest Transaction"
    description: "This is the earliest order date at the event level"
    type: time
    timeframes: [date, week, month, month_num, year, hour_of_day]
    sql: ${TABLE}.earliest_txn_date ;;
  }

  dimension_group: latest_txn {
    label: "Latest Transaction"
    description: "This is the latest order date at the event level"
    type: time
    timeframes: [time, date, week, month, month_num, year, hour_of_day]
    sql: ${TABLE}.latest_txn_date ;;
  }

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

- view: event_order_date_fperformances
  derived_table:
    sql: select
  min(c.date) as earliest_txn_date,
  max(c.date) as latest_txn_date,
  t.EventId as event_id,
  count(distinct b.orderid) as event_order_id_count
from
  customer_purchases as b
inner join
  transactions as c
    on b.orderid = c.orderid
inner join
  event_entries as t
    on b.productid = t.id
where
  b.producttype = 'T'
and
  b.status = 'Payment Received'
group by t.eventid
  fields:
  - dimension: event_order_id_count
    type: number
    label: Unique Orders Count (for entire event)
    description: This is the order count for the entire event.  This value will be repeated if the event is split into multiple rows, so do not total this.
    sql: IF(${TABLE}.event_order_id_count > 0, ${TABLE}.event_order_id_count, 0)
  - dimension: event_id
    type: number
    primary_key: true
    hidden: true
    sql: ${TABLE}.event_id
  - dimension: transaction_date_filter_flag
    type: yesno
    label: Earliest Transaction Date Filter Flag
    sql: (  {% condition transaction_date_filter %} ${earliest_txn_date} {% endcondition %}
 )
  - dimension_group: earliest_txn
    type: time
    timeframes:
    - date
    - week
    - month
    - month_num
    - year
    - hour_of_day
    sql: ${TABLE}.earliest_txn_date
  - dimension_group: latest_txn
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - month_num
    - year
    - hour_of_day
    sql: ${TABLE}.latest_txn_date
  - measure: count
    type: count
    drill_fields:
    - detail*