Member Claim Risk Facts

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

SELECT ClaimLine.memberid AS 'claimant'
  , ERGriskOutput.RetroRiskCategory  AS 'retro_risk_category'
  , ERGriskOutput.ActuarialRiskCategory AS 'actuarial_risk_category'
  , SUM(claimline.companypaid) AS 'plan_paid'
  , COUNT(DISTINCT analytica.dbo.ETG_to_Chronic_Condition."Chronic Condition") AS 'comorbidities'
FROM ClaimLine
LEFT JOIN ETGClaimsOutput
ON ETGClaimsOutput.UniqueRecordID = ClaimLine.ID
LEFT JOIN ETGLookup
ON ETGClaimsOutput.ETGNumber = ETGLookup.FullETG
LEFT JOIN Analytica.dbo.ETG_to_Chronic_Condition
ON ETGLookup.BaseETG = Analytica.dbo.ETG_to_Chronic_Condition."ETG Code"
LEFT JOIN ETGMemberTx
ON ClaimLine.MemberId = ETGMemberTx.MemberId
LEFT JOIN ERGriskOutput
ON ETGMemberTx.EtgMemberId = ERGriskOutput.FamilyID
WHERE ClaimLine.IncludeForSymmetry = 1
GROUP BY  ClaimLine.MemberId, ERGriskOutput.RetroRiskCategory, ERGriskOutput.ActuarialRiskCategory

# models/schema.yml
version: 2

models:
  - name: member_claim_risk_facts
    columns:
      - name: claimant
      - name: retro_risk_category
      - name: actuarial_risk_category
      - name: plan_paid
      - name: comorbidities
      - name: comorbidities_group

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 ClaimLine.memberid AS 'claimant'
  , ERGriskOutput.RetroRiskCategory  AS 'retro_risk_category'
  , ERGriskOutput.ActuarialRiskCategory AS 'actuarial_risk_category'
  , SUM(claimline.companypaid) AS 'plan_paid'
  , COUNT(DISTINCT analytica.dbo.ETG_to_Chronic_Condition."Chronic Condition") AS 'comorbidities'
FROM ClaimLine
LEFT JOIN ETGClaimsOutput
ON ETGClaimsOutput.UniqueRecordID = ClaimLine.ID
LEFT JOIN ETGLookup
ON ETGClaimsOutput.ETGNumber = ETGLookup.FullETG
LEFT JOIN Analytica.dbo.ETG_to_Chronic_Condition
ON ETGLookup.BaseETG = Analytica.dbo.ETG_to_Chronic_Condition."ETG Code"
LEFT JOIN ETGMemberTx
ON ClaimLine.MemberId = ETGMemberTx.MemberId
LEFT JOIN ERGriskOutput
ON ETGMemberTx.EtgMemberId = ERGriskOutput.FamilyID
WHERE ClaimLine.IncludeForSymmetry = 1
GROUP BY  ClaimLine.MemberId, ERGriskOutput.RetroRiskCategory, ERGriskOutput.ActuarialRiskCategory

view: member_claim_risk_facts {
  derived_table: {
    sql: SELECT ClaimLine.memberid AS 'claimant'
  , ERGriskOutput.RetroRiskCategory  AS 'retro_risk_category'
  , ERGriskOutput.ActuarialRiskCategory AS 'actuarial_risk_category'
  , SUM(claimline.companypaid) AS 'plan_paid'
  , COUNT(DISTINCT analytica.dbo.ETG_to_Chronic_Condition."Chronic Condition") AS 'comorbidities'
FROM ClaimLine
LEFT JOIN ETGClaimsOutput
ON ETGClaimsOutput.UniqueRecordID = ClaimLine.ID
LEFT JOIN ETGLookup
ON ETGClaimsOutput.ETGNumber = ETGLookup.FullETG
LEFT JOIN Analytica.dbo.ETG_to_Chronic_Condition
ON ETGLookup.BaseETG = Analytica.dbo.ETG_to_Chronic_Condition."ETG Code"
LEFT JOIN ETGMemberTx
ON ClaimLine.MemberId = ETGMemberTx.MemberId
LEFT JOIN ERGriskOutput
ON ETGMemberTx.EtgMemberId = ERGriskOutput.FamilyID
WHERE ClaimLine.IncludeForSymmetry = 1
GROUP BY  ClaimLine.MemberId, ERGriskOutput.RetroRiskCategory, ERGriskOutput.ActuarialRiskCategory ;;
  }

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

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

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

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

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

  dimension: comorbidities_group {
    sql: CASE
  WHEN ${comorbidities} BETWEEN 0 AND 3
  THEN CONVERT(VARCHAR(2), ${comorbidities})
  ELSE '4+'
END ;;
  }

  measure: count_of_claimants {
    type: count
    sql: ${claimant} ;;
  }

  measure: total_claimants {
    type: count_distinct
    sql: ${claimant} ;;
  }

  measure: total_plan_paid {
    type: sum
    sql: ${plan_paid} ;;
  }

  measure: average_risk_index {
    type: average
    sql: ${retro_risk_category} ;;
  }

  measure: plan_paid_per_claimant {
    type: number
    sql: ${total_plan_paid} / ${total_claimants} ;;
  }
}

- view: member_claim_risk_facts
  derived_table:
    sql: SELECT ClaimLine.memberid AS 'claimant'
  , ERGriskOutput.RetroRiskCategory  AS 'retro_risk_category'
  , ERGriskOutput.ActuarialRiskCategory AS 'actuarial_risk_category'
  , SUM(claimline.companypaid) AS 'plan_paid'
  , COUNT(DISTINCT analytica.dbo.ETG_to_Chronic_Condition."Chronic Condition") AS 'comorbidities'
FROM ClaimLine
LEFT JOIN ETGClaimsOutput
ON ETGClaimsOutput.UniqueRecordID = ClaimLine.ID
LEFT JOIN ETGLookup
ON ETGClaimsOutput.ETGNumber = ETGLookup.FullETG
LEFT JOIN Analytica.dbo.ETG_to_Chronic_Condition
ON ETGLookup.BaseETG = Analytica.dbo.ETG_to_Chronic_Condition."ETG Code"
LEFT JOIN ETGMemberTx
ON ClaimLine.MemberId = ETGMemberTx.MemberId
LEFT JOIN ERGriskOutput
ON ETGMemberTx.EtgMemberId = ERGriskOutput.FamilyID
WHERE ClaimLine.IncludeForSymmetry = 1
GROUP BY  ClaimLine.MemberId, ERGriskOutput.RetroRiskCategory, ERGriskOutput.ActuarialRiskCategory
  fields:
  - dimension: claimant
    sql: ${TABLE}.claimant
  - dimension: retro_risk_category
    type: number
    sql: ${TABLE}.retro_risk_category
  - dimension: actuarial_risk_category
    type: number
    sql: ${TABLE}.actuarial_risk_category
  - dimension: plan_paid
    type: number
    sql: ${TABLE}.plan_paid
  - dimension: comorbidities
    type: number
    sql: ${TABLE}.comorbidities
  - dimension: comorbidities_group
    sql: CASE
  WHEN ${comorbidities} BETWEEN 0 AND 3
  THEN CONVERT(VARCHAR(2), ${comorbidities})
  ELSE '4+'
END
  - measure: count_of_claimants
    type: count
    sql: ${claimant}
  - measure: total_claimants
    type: count_distinct
    sql: ${claimant}
  - measure: total_plan_paid
    type: sum
    sql: ${plan_paid}
  - measure: average_risk_index
    type: average
    sql: ${retro_risk_category}
  - measure: plan_paid_per_claimant
    type: number
    sql: ${total_plan_paid} / ${total_claimants}