Physician Call Facts

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

SELECT doctor.id AS id
 ,COUNT(*) AS lifetime_chargeable_calls
 ,MIN(call.creation_time) AS first_chargeable_call
 ,MAX(call.creation_time) AS latest_chargeable_call
 ,SUM(call.segment_count) as number_of_segments
 ,AVG(call.duration) as average_call_duration
 ,SUM(call.duration) as total_call_duration
FROM healthcare_call AS call
LEFT JOIN members_genericmember AS member ON call.member_id = member.id
LEFT JOIN doctors_doctor AS doctor ON call.doctor_id = doctor.id
WHERE {% condition call_is_demo %} call.is_demo {% endcondition %}
AND {% condition call_is_test %} call.is_test {% endcondition %}
AND {% condition call_end_status_code %} call.end_status {% endcondition %}
AND {% condition call_duration_seconds %} call.duration {% endcondition %}
GROUP BY doctor.id

# models/schema.yml
version: 2

models:
  - name: physician_call_facts
    columns:
      - name: id
        tests:
          - unique
          - not_null
      - name: lifetime_chargeable_calls
      - name: lifetime_chargeable_calls_tier
      - name: testing_average_call_duration
      - name: number_of_call_segments
      - name: total_call_duration

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 doctor.id AS id
 ,COUNT(*) AS lifetime_chargeable_calls
 ,MIN(call.creation_time) AS first_chargeable_call
 ,MAX(call.creation_time) AS latest_chargeable_call
 ,SUM(call.segment_count) as number_of_segments
 ,AVG(call.duration) as average_call_duration
 ,SUM(call.duration) as total_call_duration
FROM healthcare_call AS call
LEFT JOIN members_genericmember AS member ON call.member_id = member.id
LEFT JOIN doctors_doctor AS doctor ON call.doctor_id = doctor.id
WHERE {% condition call_is_demo %} call.is_demo {% endcondition %}
AND {% condition call_is_test %} call.is_test {% endcondition %}
AND {% condition call_end_status_code %} call.end_status {% endcondition %}
AND {% condition call_duration_seconds %} call.duration {% endcondition %}
GROUP BY doctor.id

view: physician_call_facts {
  derived_table: {
    sql: SELECT doctor.id AS id
 ,COUNT(*) AS lifetime_chargeable_calls
 ,MIN(call.creation_time) AS first_chargeable_call
 ,MAX(call.creation_time) AS latest_chargeable_call
 ,SUM(call.segment_count) as number_of_segments
 ,AVG(call.duration) as average_call_duration
 ,SUM(call.duration) as total_call_duration
FROM healthcare_call AS call
LEFT JOIN members_genericmember AS member ON call.member_id = member.id
LEFT JOIN doctors_doctor AS doctor ON call.doctor_id = doctor.id
WHERE {% condition call_is_demo %} call.is_demo {% endcondition %}
AND {% condition call_is_test %} call.is_test {% endcondition %}
AND {% condition call_end_status_code %} call.end_status {% endcondition %}
AND {% condition call_duration_seconds %} call.duration {% endcondition %}
GROUP BY doctor.id ;;
  }

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

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

  dimension: lifetime_chargeable_calls_tier {
    type: tier
    sql: ${lifetime_chargeable_calls} ;;
  }

  dimension: testing_average_call_duration {
    type: number
    sql: ${TABLE}.average_call_duration ;;
  }

  dimension: number_of_call_segments {
    type: number
    sql: ${TABLE}.number_of_segments ;;
  }

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

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

  dimension_group: latest_chargeable_call {
    type: time
    timeframes: [date]
    sql: ${TABLE}.latest_chargeable_call ;;
  }

  measure: total_calls {
    type: number
    sql: SUM(COALESCE(${TABLE}.lifetime_chargeable_calls, 0)) ;;
  }

  measure: total_call_segments {
    type: sum
    sql: ${number_of_call_segments} ;;
  }

  measure: average_call_duration {
    type: number
    sql: SUM(${total_call_duration}) / NULLIF(SUM(${lifetime_chargeable_calls}), 0) ;;
  }
}

- view: physician_call_facts
  derived_table:
    sql: SELECT doctor.id AS id
 ,COUNT(*) AS lifetime_chargeable_calls
 ,MIN(call.creation_time) AS first_chargeable_call
 ,MAX(call.creation_time) AS latest_chargeable_call
 ,SUM(call.segment_count) as number_of_segments
 ,AVG(call.duration) as average_call_duration
 ,SUM(call.duration) as total_call_duration
FROM healthcare_call AS call
LEFT JOIN members_genericmember AS member ON call.member_id = member.id
LEFT JOIN doctors_doctor AS doctor ON call.doctor_id = doctor.id
WHERE {% condition call_is_demo %} call.is_demo {% endcondition %}
AND {% condition call_is_test %} call.is_test {% endcondition %}
AND {% condition call_end_status_code %} call.end_status {% endcondition %}
AND {% condition call_duration_seconds %} call.duration {% endcondition %}
GROUP BY doctor.id
  fields:
  - dimension: id
    type: number
    primary_key: true
    hidden: true
    sql: ${TABLE}.id
  - dimension: lifetime_chargeable_calls
    type: number
    sql: ${TABLE}.lifetime_chargeable_calls
  - dimension: lifetime_chargeable_calls_tier
    type: tier
    sql: ${lifetime_chargeable_calls}
  - dimension: testing_average_call_duration
    type: number
    sql: ${TABLE}.average_call_duration
  - dimension: number_of_call_segments
    type: number
    sql: ${TABLE}.number_of_segments
  - dimension: total_call_duration
    type: number
    hidden: true
    sql: ${TABLE}.total_call_duration
  - dimension_group: first_chargeable_call
    type: time
    timeframes:
    - date
    - month
    - year
    sql: ${TABLE}.first_chargeable_call
  - dimension_group: latest_chargeable_call
    type: time
    timeframes:
    - date
    sql: ${TABLE}.latest_chargeable_call
  - measure: total_calls
    type: number
    sql: SUM(COALESCE(${TABLE}.lifetime_chargeable_calls, 0))
  - measure: total_call_segments
    type: sum
    sql: ${number_of_call_segments}
  - measure: average_call_duration
    type: number
    sql: SUM(${total_call_duration}) / NULLIF(SUM(${lifetime_chargeable_calls}), 0)