Health Condition Affinity

-- models/health_condition_affinity.sql
{{ config(materialized='table') }}

SELECT
health_condition_a ,
health_condition_b ,
joint_frequency ,
top1.health_condition_frequency AS health_condition_a_frequency ,
top2.health_condition_frequency AS health_condition_b_frequency
FROM
(
  SELECT
      op1.condition_id AS condition_a_id,
      op1.description AS health_condition_a ,
      op2.condition_id AS condition_b_id,
      op2.description AS health_condition_b ,
      COUNT(*) AS joint_frequency
  FROM
      {{ ref('consumer_health_conditions') }} AS op1
  INNER JOIN {{ ref('consumer_health_conditions') }} AS op2
  ON  op1.consumer_id = op2.consumer_id
  GROUP BY
      1,2,3,4
) AS prop
INNER JOIN {{ ref('consumer_health_condition_frequency') }} AS top1 ON prop.condition_a_id = top1.condition_id
INNER JOIN {{ ref('consumer_health_condition_frequency') }} AS top2 ON prop.condition_b_id = top2.condition_id
WHERE health_condition_a <> health_condition_b
ORDER BY
3 DESC

# models/schema.yml
version: 2

models:
  - name: health_condition_affinity
    columns:
      - name: health_condition_a
      - name: health_condition_b

Assumptions

• Each ${view.SQL_TABLE_NAME} was mapped to {{ ref('view') }}; confirm a dbt model exists for each referenced view.
• materialized='table' inferred from a LookML persist hint (persist_for / datagroup / sql_trigger_value).
• schema.yml columns come from view dimensions; a primary_key dimension gets unique + not_null tests.

SELECT
health_condition_a ,
health_condition_b ,
joint_frequency ,
top1.health_condition_frequency AS health_condition_a_frequency ,
top2.health_condition_frequency AS health_condition_b_frequency
FROM
(
  SELECT
      op1.condition_id AS condition_a_id,
      op1.description AS health_condition_a ,
      op2.condition_id AS condition_b_id,
      op2.description AS health_condition_b ,
      COUNT(*) AS joint_frequency
  FROM
      ${consumer_health_conditions.SQL_TABLE_NAME} AS op1
  INNER JOIN ${consumer_health_conditions.SQL_TABLE_NAME} AS op2
  ON  op1.consumer_id = op2.consumer_id
  GROUP BY
      1,2,3,4
) AS prop
INNER JOIN ${consumer_health_condition_frequency.SQL_TABLE_NAME} AS top1 ON prop.condition_a_id = top1.condition_id
INNER JOIN ${consumer_health_condition_frequency.SQL_TABLE_NAME} AS top2 ON prop.condition_b_id = top2.condition_id
WHERE health_condition_a <> health_condition_b
ORDER BY
3 DESC

view: health_condition_affinity {
  derived_table: {
    sql: SELECT
health_condition_a ,
health_condition_b ,
joint_frequency ,
top1.health_condition_frequency AS health_condition_a_frequency ,
top2.health_condition_frequency AS health_condition_b_frequency
FROM
(
  SELECT
      op1.condition_id AS condition_a_id,
      op1.description AS health_condition_a ,
      op2.condition_id AS condition_b_id,
      op2.description AS health_condition_b ,
      COUNT(*) AS joint_frequency
  FROM
      ${consumer_health_conditions.SQL_TABLE_NAME} AS op1
  INNER JOIN ${consumer_health_conditions.SQL_TABLE_NAME} AS op2
  ON  op1.consumer_id = op2.consumer_id
  GROUP BY
      1,2,3,4
) AS prop
INNER JOIN ${consumer_health_condition_frequency.SQL_TABLE_NAME} AS top1 ON prop.condition_a_id = top1.condition_id
INNER JOIN ${consumer_health_condition_frequency.SQL_TABLE_NAME} AS top2 ON prop.condition_b_id = top2.condition_id
WHERE health_condition_a <> health_condition_b
ORDER BY
3 DESC ;;
    persist_for: "24 hours"
  }

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

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

  measure: joint_frequency {
    type: sum
    sql: ${TABLE}.joint_frequency ;;
  }

  measure: health_condition_a_frequency {
    type: sum
    sql: ${TABLE}.health_condition_a_frequency ;;
  }

  measure: health_condition_b_frequency {
    type: sum
    sql: ${TABLE}.health_condition_b_frequency ;;
  }

  measure: affinity {
    type: number
    sql: 100 * (${joint_frequency}/(${health_condition_a_frequency} + ${health_condition_b_frequency} - ${joint_frequency})) ;;
  }
}

- view: health_condition_affinity
  derived_table:
    sql: SELECT
health_condition_a ,
health_condition_b ,
joint_frequency ,
top1.health_condition_frequency AS health_condition_a_frequency ,
top2.health_condition_frequency AS health_condition_b_frequency
FROM
(
  SELECT
      op1.condition_id AS condition_a_id,
      op1.description AS health_condition_a ,
      op2.condition_id AS condition_b_id,
      op2.description AS health_condition_b ,
      COUNT(*) AS joint_frequency
  FROM
      ${consumer_health_conditions.SQL_TABLE_NAME} AS op1
  INNER JOIN ${consumer_health_conditions.SQL_TABLE_NAME} AS op2
  ON  op1.consumer_id = op2.consumer_id
  GROUP BY
      1,2,3,4
) AS prop
INNER JOIN ${consumer_health_condition_frequency.SQL_TABLE_NAME} AS top1 ON prop.condition_a_id = top1.condition_id
INNER JOIN ${consumer_health_condition_frequency.SQL_TABLE_NAME} AS top2 ON prop.condition_b_id = top2.condition_id
WHERE health_condition_a <> health_condition_b
ORDER BY
3 DESC
    persist_for: 24 hours
  fields:
  - dimension: health_condition_a
    sql: ${TABLE}.health_condition_a
  - dimension: health_condition_b
    sql: ${TABLE}.health_condition_b
  - measure: joint_frequency
    type: sum
    sql: ${TABLE}.joint_frequency
  - measure: health_condition_a_frequency
    type: sum
    sql: ${TABLE}.health_condition_a_frequency
  - measure: health_condition_b_frequency
    type: sum
    sql: ${TABLE}.health_condition_b_frequency
  - measure: affinity
    type: number
    sql: 100 * (${joint_frequency}/(${health_condition_a_frequency} + ${health_condition_b_frequency} - ${joint_frequency}))