data.ml
dbt Telemedicine service

Company Eligibility With First Dependent

Company Eligibility With First Dependent model for telemedicine service.

inferred

Blocks

company_eligibility_with_first_dependent.sql · 118 lines
inferred 
-- models/company_eligibility_with_first_dependent.sql
{{ config(materialized='view') }}

SELECT *,

    -- the following columns, which are results of CASE expressions, are only here to make NULL the inner_dependent_* fields in the case when the present row is the employee, *and* he/she has no dependents.
    -- in that case we want the 'first' dependent to be null.
    -- had we not set these to NULL, then employees with no dependents would show up here as having themselves as the 'first dependents' (i.e. in the dependent_one_first_name, dependent_one_last_name, etc. fields)

    CASE
      WHEN relationship_to_employee = 1    -- is an employee
           AND inner_dependent_one_id = id  -- and he/she has no dependents
        THEN NULL
        ELSE inner_dependent_one_id
    END
        AS dependent_one_id,  -- this is the present "household"'s first dependent
    CASE
      WHEN relationship_to_employee = 1    -- is an employee
           AND inner_dependent_one_id = id  -- and he/she has no dependents
        THEN NULL
        ELSE inner_dependent_one_first_name
    END
        AS dependent_one_first_name,
    CASE
      WHEN relationship_to_employee = 1    -- is an employee
           AND inner_dependent_one_id = id  -- and he/she has no dependents
        THEN NULL
        ELSE inner_dependent_one_last_name
    END
        AS dependent_one_last_name

    FROM
    (SELECT
      ce.id,
      ce.company_id,
      ce.division,
      ce.dob,
      ce.insurance_plan_id,
      ce.email,


      ce.gender,
      ce.insurance_group_id,
      ce.insurance_member_id,
      ce.insurance_plan,
      ce.is_children_eligible,
      ce.is_dependent,
      ce.is_international,
      ce.is_spouse_eligible,
      ce.job_title,

      ce.policy_holder_id,
      ce.prefix,
      ce.relationship_to_employee,
      ce.identifier,
      ce.subscriber_identifier,
      ce.suffix,
      ce.valid_from,
      ce.valid_until,

      ce.employee_id,
      ce.first_name,
      ce.last_name,
      ce.middle_name,


      first_value(ce.id) OVER w AS inner_dependent_one_id,
      first_value(ce.first_name) OVER w AS inner_dependent_one_first_name,
      first_value(ce.last_name) OVER w AS inner_dependent_one_last_name
    FROM eligibility_membereligibility AS ce
    JOIN members_companygroup AS mcg ON ce.company_id = mcg.id  -- this join to get mcg.company_id to partition on
    -- WHERE mcg.company_id = 52

    WINDOW w AS (PARTITION BY mcg.company_id, employee_id   -- note that mcg.company_id is FK to members_company, as expected, but ce.company_id is really an FK to members_companygroup, which is not expected.
       ORDER BY (CASE
                  WHEN relationship_to_employee = 4 THEN 0 -- domestic partner
                  WHEN relationship_to_employee = 5 THEN 1 -- spouse
                  WHEN relationship_to_employee = 2 THEN 2 -- child
                  WHEN relationship_to_employee = 6 THEN 3 -- legal ward
                  WHEN relationship_to_employee = 3 THEN 4 -- grandchild
                  WHEN relationship_to_employee = 7 THEN 5 -- "other"
                  WHEN relationship_to_employee = 1 THEN 7 -- self is the lowest priority. if the employee has no deps, he/she will have him/herself as the dependent. we'll get rid of that in the outer SELECT, when making the dependent_one_* fields.
                  ELSE 6 -- unknown is higher priority than self.
                END) ASC
       RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
    ) AS comp_elig
    --WHERE comp_elig.id != comp_elig.dependent_one_id
    ORDER BY employee_id ASC

# models/schema.yml
version: 2

models:
  - name: company_eligibility_with_first_dependent
    columns:
      - name: id
      - name: company_id
      - name: dob
      - name: email
      - name: gender
      - name: insurance_group_id
      - name: insurance_plan
      - name: is_children_eligible
      - name: is_dependent
      - name: is_international
      - name: is_spouse_eligible
      - name: policy_holder_id
      - name: relationship_to_employee_code
      - name: relationship_to_employee
      - name: identifier
      - name: subscriber_identifier
      - name: employee_id
      - name: first_name
      - name: last_name
      - name: middle_name
      - name: dependent_one_id
      - name: dependent_one_first_name
      - name: dependent_one_last_name

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.
company_eligibility_with_first_dependent.sql · 85 lines
source-preserved validation: parses 
SELECT *,

    -- the following columns, which are results of CASE expressions, are only here to make NULL the inner_dependent_* fields in the case when the present row is the employee, *and* he/she has no dependents.
    -- in that case we want the 'first' dependent to be null.
    -- had we not set these to NULL, then employees with no dependents would show up here as having themselves as the 'first dependents' (i.e. in the dependent_one_first_name, dependent_one_last_name, etc. fields)

    CASE
      WHEN relationship_to_employee = 1    -- is an employee
           AND inner_dependent_one_id = id  -- and he/she has no dependents
        THEN NULL
        ELSE inner_dependent_one_id
    END
        AS dependent_one_id,  -- this is the present "household"'s first dependent
    CASE
      WHEN relationship_to_employee = 1    -- is an employee
           AND inner_dependent_one_id = id  -- and he/she has no dependents
        THEN NULL
        ELSE inner_dependent_one_first_name
    END
        AS dependent_one_first_name,
    CASE
      WHEN relationship_to_employee = 1    -- is an employee
           AND inner_dependent_one_id = id  -- and he/she has no dependents
        THEN NULL
        ELSE inner_dependent_one_last_name
    END
        AS dependent_one_last_name

    FROM
    (SELECT
      ce.id,
      ce.company_id,
      ce.division,
      ce.dob,
      ce.insurance_plan_id,
      ce.email,


      ce.gender,
      ce.insurance_group_id,
      ce.insurance_member_id,
      ce.insurance_plan,
      ce.is_children_eligible,
      ce.is_dependent,
      ce.is_international,
      ce.is_spouse_eligible,
      ce.job_title,

      ce.policy_holder_id,
      ce.prefix,
      ce.relationship_to_employee,
      ce.identifier,
      ce.subscriber_identifier,
      ce.suffix,
      ce.valid_from,
      ce.valid_until,

      ce.employee_id,
      ce.first_name,
      ce.last_name,
      ce.middle_name,


      first_value(ce.id) OVER w AS inner_dependent_one_id,
      first_value(ce.first_name) OVER w AS inner_dependent_one_first_name,
      first_value(ce.last_name) OVER w AS inner_dependent_one_last_name
    FROM eligibility_membereligibility AS ce
    JOIN members_companygroup AS mcg ON ce.company_id = mcg.id  -- this join to get mcg.company_id to partition on
    -- WHERE mcg.company_id = 52

    WINDOW w AS (PARTITION BY mcg.company_id, employee_id   -- note that mcg.company_id is FK to members_company, as expected, but ce.company_id is really an FK to members_companygroup, which is not expected.
       ORDER BY (CASE
                  WHEN relationship_to_employee = 4 THEN 0 -- domestic partner
                  WHEN relationship_to_employee = 5 THEN 1 -- spouse
                  WHEN relationship_to_employee = 2 THEN 2 -- child
                  WHEN relationship_to_employee = 6 THEN 3 -- legal ward
                  WHEN relationship_to_employee = 3 THEN 4 -- grandchild
                  WHEN relationship_to_employee = 7 THEN 5 -- "other"
                  WHEN relationship_to_employee = 1 THEN 7 -- self is the lowest priority. if the employee has no deps, he/she will have him/herself as the dependent. we'll get rid of that in the outer SELECT, when making the dependent_one_* fields.
                  ELSE 6 -- unknown is higher priority than self.
                END) ASC
       RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
    ) AS comp_elig
    --WHERE comp_elig.id != comp_elig.dependent_one_id
    ORDER BY employee_id ASC
company_eligibility_with_first_dependent.view.lkml · 255 lines
inferred 
view: company_eligibility_with_first_dependent {
  derived_table: {
    sql: SELECT *,

    -- the following columns, which are results of CASE expressions, are only here to make NULL the inner_dependent_* fields in the case when the present row is the employee, *and* he/she has no dependents.
    -- in that case we want the 'first' dependent to be null.
    -- had we not set these to NULL, then employees with no dependents would show up here as having themselves as the 'first dependents' (i.e. in the dependent_one_first_name, dependent_one_last_name, etc. fields)

    CASE
      WHEN relationship_to_employee = 1    -- is an employee
           AND inner_dependent_one_id = id  -- and he/she has no dependents
        THEN NULL
        ELSE inner_dependent_one_id
    END
        AS dependent_one_id,  -- this is the present "household"'s first dependent
    CASE
      WHEN relationship_to_employee = 1    -- is an employee
           AND inner_dependent_one_id = id  -- and he/she has no dependents
        THEN NULL
        ELSE inner_dependent_one_first_name
    END
        AS dependent_one_first_name,
    CASE
      WHEN relationship_to_employee = 1    -- is an employee
           AND inner_dependent_one_id = id  -- and he/she has no dependents
        THEN NULL
        ELSE inner_dependent_one_last_name
    END
        AS dependent_one_last_name

    FROM
    (SELECT
      ce.id,
      ce.company_id,
      ce.division,
      ce.dob,
      ce.insurance_plan_id,
      ce.email,


      ce.gender,
      ce.insurance_group_id,
      ce.insurance_member_id,
      ce.insurance_plan,
      ce.is_children_eligible,
      ce.is_dependent,
      ce.is_international,
      ce.is_spouse_eligible,
      ce.job_title,

      ce.policy_holder_id,
      ce.prefix,
      ce.relationship_to_employee,
      ce.identifier,
      ce.subscriber_identifier,
      ce.suffix,
      ce.valid_from,
      ce.valid_until,

      ce.employee_id,
      ce.first_name,
      ce.last_name,
      ce.middle_name,


      first_value(ce.id) OVER w AS inner_dependent_one_id,
      first_value(ce.first_name) OVER w AS inner_dependent_one_first_name,
      first_value(ce.last_name) OVER w AS inner_dependent_one_last_name
    FROM eligibility_membereligibility AS ce
    JOIN members_companygroup AS mcg ON ce.company_id = mcg.id  -- this join to get mcg.company_id to partition on
    -- WHERE mcg.company_id = 52

    WINDOW w AS (PARTITION BY mcg.company_id, employee_id   -- note that mcg.company_id is FK to members_company, as expected, but ce.company_id is really an FK to members_companygroup, which is not expected.
       ORDER BY (CASE
                  WHEN relationship_to_employee = 4 THEN 0 -- domestic partner
                  WHEN relationship_to_employee = 5 THEN 1 -- spouse
                  WHEN relationship_to_employee = 2 THEN 2 -- child
                  WHEN relationship_to_employee = 6 THEN 3 -- legal ward
                  WHEN relationship_to_employee = 3 THEN 4 -- grandchild
                  WHEN relationship_to_employee = 7 THEN 5 -- "other"
                  WHEN relationship_to_employee = 1 THEN 7 -- self is the lowest priority. if the employee has no deps, he/she will have him/herself as the dependent. we'll get rid of that in the outer SELECT, when making the dependent_one_* fields.
                  ELSE 6 -- unknown is higher priority than self.
                END) ASC
       RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
    ) AS comp_elig
    --WHERE comp_elig.id != comp_elig.dependent_one_id
    ORDER BY employee_id ASC ;;
  }

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

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

  dimension: dob {
    type: string
    sql: ${TABLE}.dob ;;
  }

  dimension: email {
    type: string
    sql: ${TABLE}.email ;;
  }

  dimension: gender {
    type: string
    sql: ${TABLE}.gender ;;
  }

  dimension: insurance_group_id {
    type: string
    sql: ${TABLE}.insurance_group_id ;;
  }

  dimension: insurance_plan {
    type: string
    sql: ${TABLE}.insurance_plan ;;
  }

  dimension: is_children_eligible {
    type: yesno
    sql: ${TABLE}.is_children_eligible ;;
  }

  dimension: is_dependent {
    type: yesno
    sql: ${TABLE}.is_dependent ;;
  }

  dimension: is_international {
    type: yesno
    sql: ${TABLE}.is_international ;;
  }

  dimension: is_spouse_eligible {
    type: yesno
    sql: ${TABLE}.is_spouse_eligible ;;
  }

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

  dimension: relationship_to_employee_code {
    type: int
    sql: ${TABLE}.relationship_to_employee ;;
  }

  dimension: relationship_to_employee {
  }

  dimension: identifier {
    type: string
    sql: ${TABLE}.identifier ;;
  }

  dimension: subscriber_identifier {
    type: string
    sql: ${TABLE}.subscriber_identifier ;;
  }

  dimension: employee_id {
    type: string
    sql: ${TABLE}.employee_id ;;
  }

  dimension: first_name {
    type: string
    sql: ${TABLE}.first_name ;;
  }

  dimension: last_name {
    type: string
    sql: ${TABLE}.last_name ;;
  }

  dimension: middle_name {
    type: string
    sql: ${TABLE}.middle_name ;;
  }

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

  dimension: dependent_one_first_name {
    type: string
    sql: ${TABLE}.dependent_one_first_name ;;
  }

  dimension: dependent_one_last_name {
    type: string
    sql: ${TABLE}.dependent_one_last_name ;;
  }

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

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

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

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

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

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

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

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

  measure: count {
    type: count
    drill_fields: [detail*]
  }
}
company_eligibility_with_first_dependent.view.lookml · 223 lines
source-preserved 
- view: company_eligibility_with_first_dependent
  derived_table:
    sql: SELECT *,

    -- the following columns, which are results of CASE expressions, are only here to make NULL the inner_dependent_* fields in the case when the present row is the employee, *and* he/she has no dependents.
    -- in that case we want the 'first' dependent to be null.
    -- had we not set these to NULL, then employees with no dependents would show up here as having themselves as the 'first dependents' (i.e. in the dependent_one_first_name, dependent_one_last_name, etc. fields)

    CASE
      WHEN relationship_to_employee = 1    -- is an employee
           AND inner_dependent_one_id = id  -- and he/she has no dependents
        THEN NULL
        ELSE inner_dependent_one_id
    END
        AS dependent_one_id,  -- this is the present "household"'s first dependent
    CASE
      WHEN relationship_to_employee = 1    -- is an employee
           AND inner_dependent_one_id = id  -- and he/she has no dependents
        THEN NULL
        ELSE inner_dependent_one_first_name
    END
        AS dependent_one_first_name,
    CASE
      WHEN relationship_to_employee = 1    -- is an employee
           AND inner_dependent_one_id = id  -- and he/she has no dependents
        THEN NULL
        ELSE inner_dependent_one_last_name
    END
        AS dependent_one_last_name

    FROM
    (SELECT
      ce.id,
      ce.company_id,
      ce.division,
      ce.dob,
      ce.insurance_plan_id,
      ce.email,


      ce.gender,
      ce.insurance_group_id,
      ce.insurance_member_id,
      ce.insurance_plan,
      ce.is_children_eligible,
      ce.is_dependent,
      ce.is_international,
      ce.is_spouse_eligible,
      ce.job_title,

      ce.policy_holder_id,
      ce.prefix,
      ce.relationship_to_employee,
      ce.identifier,
      ce.subscriber_identifier,
      ce.suffix,
      ce.valid_from,
      ce.valid_until,

      ce.employee_id,
      ce.first_name,
      ce.last_name,
      ce.middle_name,


      first_value(ce.id) OVER w AS inner_dependent_one_id,
      first_value(ce.first_name) OVER w AS inner_dependent_one_first_name,
      first_value(ce.last_name) OVER w AS inner_dependent_one_last_name
    FROM eligibility_membereligibility AS ce
    JOIN members_companygroup AS mcg ON ce.company_id = mcg.id  -- this join to get mcg.company_id to partition on
    -- WHERE mcg.company_id = 52

    WINDOW w AS (PARTITION BY mcg.company_id, employee_id   -- note that mcg.company_id is FK to members_company, as expected, but ce.company_id is really an FK to members_companygroup, which is not expected.
       ORDER BY (CASE
                  WHEN relationship_to_employee = 4 THEN 0 -- domestic partner
                  WHEN relationship_to_employee = 5 THEN 1 -- spouse
                  WHEN relationship_to_employee = 2 THEN 2 -- child
                  WHEN relationship_to_employee = 6 THEN 3 -- legal ward
                  WHEN relationship_to_employee = 3 THEN 4 -- grandchild
                  WHEN relationship_to_employee = 7 THEN 5 -- "other"
                  WHEN relationship_to_employee = 1 THEN 7 -- self is the lowest priority. if the employee has no deps, he/she will have him/herself as the dependent. we'll get rid of that in the outer SELECT, when making the dependent_one_* fields.
                  ELSE 6 -- unknown is higher priority than self.
                END) ASC
       RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
    ) AS comp_elig
    --WHERE comp_elig.id != comp_elig.dependent_one_id
    ORDER BY employee_id ASC
  fields:
  - dimension: id
    type: int
    sql: ${TABLE}.id
  - dimension: company_id
    type: int
    sql: ${TABLE}.company_id
  - dimension: dob
    type: string
    sql: ${TABLE}.dob
  - dimension: email
    type: string
    sql: ${TABLE}.email
  - dimension: gender
    type: string
    sql: ${TABLE}.gender
  - dimension: insurance_group_id
    type: string
    sql: ${TABLE}.insurance_group_id
  - dimension: insurance_plan
    type: string
    sql: ${TABLE}.insurance_plan
  - dimension: is_children_eligible
    type: yesno
    sql: ${TABLE}.is_children_eligible
  - dimension: is_dependent
    type: yesno
    sql: ${TABLE}.is_dependent
  - dimension: is_international
    type: yesno
    sql: ${TABLE}.is_international
  - dimension: is_spouse_eligible
    type: yesno
    sql: ${TABLE}.is_spouse_eligible
  - dimension: policy_holder_id
    type: int
    sql: ${TABLE}.policy_holder_id
  - dimension: relationship_to_employee_code
    type: int
    sql: ${TABLE}.relationship_to_employee
  - dimension: relationship_to_employee
  - dimension: identifier
    type: string
    sql: ${TABLE}.identifier
  - dimension: subscriber_identifier
    type: string
    sql: ${TABLE}.subscriber_identifier
  - dimension: employee_id
    type: string
    sql: ${TABLE}.employee_id
  - dimension: first_name
    type: string
    sql: ${TABLE}.first_name
  - dimension: last_name
    type: string
    sql: ${TABLE}.last_name
  - dimension: middle_name
    type: string
    sql: ${TABLE}.middle_name
  - dimension: dependent_one_id
    type: int
    sql: ${TABLE}.dependent_one_id
  - dimension: dependent_one_first_name
    type: string
    sql: ${TABLE}.dependent_one_first_name
  - dimension: dependent_one_last_name
    type: string
    sql: ${TABLE}.dependent_one_last_name
  - dimension_group: division
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.division
  - dimension_group: insurance_plan_id
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.insurance_plan_id
  - dimension_group: insurance_member_id
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.insurance_member_id
  - dimension_group: job_title
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.job_title
  - dimension_group: prefix
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.prefix
  - dimension_group: suffix
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.suffix
  - dimension_group: valid_from
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.valid_from
  - dimension_group: valid_until
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.valid_until
  - measure: count
    type: count
    drill_fields:
    - detail*

Contents

  • company_eligibility_with_first_dependent.sql
  • company_eligibility_with_first_dependent.sql
  • company_eligibility_with_first_dependent.view.lkml
  • company_eligibility_with_first_dependent.view.lookml