Lead Facts

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

SELECT
  CallStat.C3SLeadId,
  COUNT(*) as num_calls,
  GROUP_CONCAT((CallStat.callId) SEPARATOR ', ') as call_id_list,
  CASE WHEN COUNT(*) >= 5 THEN 1 ELSE 0 END as max_reached,
  SUM(callDispoGrossFlag) as gross_contacts,
  SUM(CallStat.callDispoNetFlag) as net_contacts,
  MIN(CallStat.callCreatedOn) as first_call_time,
  MIN(C3SOrder.orderId) as orderId
FROM CallStat
LEFT JOIN C3SOrder
ON CallStat.callId = C3SOrder.orderCallRootId
GROUP BY C3SLeadId

# models/schema.yml
version: 2

models:
  - name: lead_facts
    columns:
      - name: id
        tests:
          - unique
          - not_null
      - name: num_calls
      - name: call_id_list
      - name: max_reached
      - name: gross_contacts
      - name: net_contacts
      - name: first_call_time
      - name: first_call_time_raw
      - name: order_id

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
  CallStat.C3SLeadId,
  COUNT(*) as num_calls,
  GROUP_CONCAT((CallStat.callId) SEPARATOR ', ') as call_id_list,
  CASE WHEN COUNT(*) >= 5 THEN 1 ELSE 0 END as max_reached,
  SUM(callDispoGrossFlag) as gross_contacts,
  SUM(CallStat.callDispoNetFlag) as net_contacts,
  MIN(CallStat.callCreatedOn) as first_call_time,
  MIN(C3SOrder.orderId) as orderId
FROM CallStat
LEFT JOIN C3SOrder
ON CallStat.callId = C3SOrder.orderCallRootId
GROUP BY C3SLeadId

view: lead_facts {
  derived_table: {
    sql: SELECT
  CallStat.C3SLeadId,
  COUNT(*) as num_calls,
  GROUP_CONCAT((CallStat.callId) SEPARATOR ', ') as call_id_list,
  CASE WHEN COUNT(*) >= 5 THEN 1 ELSE 0 END as max_reached,
  SUM(callDispoGrossFlag) as gross_contacts,
  SUM(CallStat.callDispoNetFlag) as net_contacts,
  MIN(CallStat.callCreatedOn) as first_call_time,
  MIN(C3SOrder.orderId) as orderId
FROM CallStat
LEFT JOIN C3SOrder
ON CallStat.callId = C3SOrder.orderCallRootId
GROUP BY C3SLeadId ;;
  }

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

  dimension: num_calls {
    type: int
    sql: ${TABLE}.num_calls ;;
  }

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

  dimension: max_reached {
    type: yesno
    sql: ${TABLE}.max_reached = 1 ;;
  }

  dimension: gross_contacts {
    type: int
    sql: ${TABLE}.gross_contacts ;;
  }

  dimension: net_contacts {
    type: int
    sql: ${TABLE}.net_contacts ;;
  }

  dimension: first_call_time {
    type: date_time
    sql: ${TABLE}.first_call_time ;;
  }

  dimension: first_call_time_raw {
    hidden: yes
    sql: ${TABLE}.first_call_time ;;
  }

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

  measure: max_reached_count {
    type: count
    filters: [max_reached: "True"]
  }

  measure: total_num_calls {
    type: sum
    sql: ${num_calls} ;;
  }

  measure: total_gross_contacts {
    type: sum
    sql: ${gross_contacts} ;;
  }

  measure: total_net_contacts {
    type: sum
    sql: ${net_contacts} ;;
  }
}

- view: lead_facts
  derived_table:
    sql: SELECT
  CallStat.C3SLeadId,
  COUNT(*) as num_calls,
  GROUP_CONCAT((CallStat.callId) SEPARATOR ', ') as call_id_list,
  CASE WHEN COUNT(*) >= 5 THEN 1 ELSE 0 END as max_reached,
  SUM(callDispoGrossFlag) as gross_contacts,
  SUM(CallStat.callDispoNetFlag) as net_contacts,
  MIN(CallStat.callCreatedOn) as first_call_time,
  MIN(C3SOrder.orderId) as orderId
FROM CallStat
LEFT JOIN C3SOrder
ON CallStat.callId = C3SOrder.orderCallRootId
GROUP BY C3SLeadId
  fields:
  - dimension: id
    type: int
    primary_key: true
    hidden: true
    sql: ${TABLE}.C3SLeadId
  - dimension: num_calls
    type: int
    sql: ${TABLE}.num_calls
  - dimension: call_id_list
    sql: ${TABLE}.call_id_list
  - dimension: max_reached
    type: yesno
    sql: ${TABLE}.max_reached = 1
  - dimension: gross_contacts
    type: int
    sql: ${TABLE}.gross_contacts
  - dimension: net_contacts
    type: int
    sql: ${TABLE}.net_contacts
  - dimension: first_call_time
    type: date_time
    sql: ${TABLE}.first_call_time
  - dimension: first_call_time_raw
    hidden: true
    sql: ${TABLE}.first_call_time
  - dimension: order_id
    type: int
    hidden: true
    sql: ${TABLE}.orderId
  - measure: max_reached_count
    type: count
    filters:
    - max_reached: True
  - measure: total_num_calls
    type: sum
    sql: ${num_calls}
  - measure: total_gross_contacts
    type: sum
    sql: ${gross_contacts}
  - measure: total_net_contacts
    type: sum
    sql: ${net_contacts}