SQL Genomics health services

Account Activity

Account Activity model for genomics health services.

source-preserved

Blocks

dbt SQL LKML LookML

account_activity.sql · 48 lines

inferred

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

SELECT
  o5.order_week
  ,o5.account_identifier
  ,o5.active_count_week
  ,week_join.active_count_week AS lag_active_count_week
  ,o5.order_month
  ,o5.active_count_month
  ,month_join.active_count_month AS lag_active_count_month
  ,id
FROM (
  SELECT
    o4.order_week
    ,o4.account_identifier
    ,o4.active_count_week
    ,o4.order_month
    ,o4.active_count_month
    , id
  FROM
  {{ ref('account_activity_intermediate') }} AS o4
  ORDER BY 2,1
) AS o5
LEFT JOIN {{ ref('month_join') }} AS month_join
  ON month_join.order_month = o5.order_month - interval '1 month'
  AND month_join.account_identifier_month = o5.account_identifier
LEFT JOIN {{ ref('week_join') }} AS week_join
  ON week_join.order_week = o5.order_week - interval '1 week'
  AND week_join.account_identifier_week = o5.account_identifier
ORDER BY 2,1

# models/schema.yml
version: 2

models:
  - name: account_activity
    columns:
      - name: id
      - name: account_identifier
      - name: active_count_week
      - name: active_count_month
      - name: lag_active_count_week
      - name: lag_active_count_month
      - name: account_monthly_active
      - name: account_weekly_active
      - name: account_monthly_churn
      - name: account_weekly_churn

Assumptions

Each ${view.SQL_TABLE_NAME} was mapped to {{ ref('view') }}; confirm a dbt model exists for each referenced view.
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.

account_activity.sql · 28 lines

source-preserved validation: not checked

SELECT
  o5.order_week
  ,o5.account_identifier
  ,o5.active_count_week
  ,week_join.active_count_week AS lag_active_count_week
  ,o5.order_month
  ,o5.active_count_month
  ,month_join.active_count_month AS lag_active_count_month
  ,id
FROM (
  SELECT
    o4.order_week
    ,o4.account_identifier
    ,o4.active_count_week
    ,o4.order_month
    ,o4.active_count_month
    , id
  FROM
  ${account_activity_intermediate.SQL_TABLE_NAME} AS o4
  ORDER BY 2,1
) AS o5
LEFT JOIN ${month_join.SQL_TABLE_NAME} AS month_join
  ON month_join.order_month = o5.order_month - interval '1 month'
  AND month_join.account_identifier_month = o5.account_identifier
LEFT JOIN ${week_join.SQL_TABLE_NAME} AS week_join
  ON week_join.order_week = o5.order_week - interval '1 week'
  AND week_join.account_identifier_week = o5.account_identifier
ORDER BY 2,1

account_activity.view.lkml · 130 lines

inferred

view: account_activity {
  derived_table: {
    sql: SELECT
  o5.order_week
  ,o5.account_identifier
  ,o5.active_count_week
  ,week_join.active_count_week AS lag_active_count_week
  ,o5.order_month
  ,o5.active_count_month
  ,month_join.active_count_month AS lag_active_count_month
  ,id
FROM (
  SELECT
    o4.order_week
    ,o4.account_identifier
    ,o4.active_count_week
    ,o4.order_month
    ,o4.active_count_month
    , id
  FROM
  ${account_activity_intermediate.SQL_TABLE_NAME} AS o4
  ORDER BY 2,1
) AS o5
LEFT JOIN ${month_join.SQL_TABLE_NAME} AS month_join
  ON month_join.order_month = o5.order_month - interval '1 month'
  AND month_join.account_identifier_month = o5.account_identifier
LEFT JOIN ${week_join.SQL_TABLE_NAME} AS week_join
  ON week_join.order_week = o5.order_week - interval '1 week'
  AND week_join.account_identifier_week = o5.account_identifier
ORDER BY 2,1 ;;
  }

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

  dimension: account_identifier {
    hidden: yes
    sql: ${TABLE}.account_identifier ;;
  }

  dimension: active_count_week {
    label: "Active Account Count (Week)"
    type: int
    sql: ${TABLE}.active_count_week ;;
  }

  dimension: active_count_month {
    label: "Active Account Count (Month)"
    type: int
    sql: ${TABLE}.active_count_month ;;
  }

  dimension: lag_active_count_week {
    label: "Active Account Count (Previous Week)"
    type: int
    sql: ${TABLE}.lag_active_count_week ;;
  }

  dimension: lag_active_count_month {
    label: "Active Account Count (Previous Month)"
    type: int
    sql: ${TABLE}.lag_active_count_month ;;
  }

  dimension: account_monthly_active {
    label: "Account Active/Inactive? (Month)"
    sql: CASE WHEN ${account_identifier} is not null THEN
  CASE WHEN {% condition order_count_filter %} ${active_count_month} {% endcondition %} THEN 'Active'
  ELSE 'Inactive' end
  ELSE null end ;;
  }

  dimension: account_weekly_active {
    label: "Account Active/Inactive? (Week)"
    sql: CASE WHEN ${account_identifier} is not null THEN
  CASE WHEN {% condition order_count_filter %} ${active_count_week} {% endcondition %} THEN 'Active'
  ELSE 'Inactive' end
  ELSE null end ;;
  }

  dimension: account_monthly_churn {
    label: "Churned? (Month)"
    sql: CASE WHEN ${order.account_identifier} is not null THEN
  CASE WHEN {% condition order_count_filter_churn %} ${order_percent_decrease_month} {% endcondition %} THEN 'Churn'
  ELSE null end
  ELSE null end ;;
  }

  dimension: account_weekly_churn {
    label: "Churned? (Week)"
    sql: CASE WHEN ${order.account_identifier} is not null THEN
  CASE WHEN {% condition order_count_filter_churn %} ${order_percent_decrease_week} {% endcondition %} THEN 'Churn'
  ELSE null end
  ELSE null end ;;
  }

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

  measure: order_percent_decrease_month {
    type: number
    value_format: "#.00\%"
    sql: 100* (${active_count_month}-${lag_active_count_month})/nullif(${lag_active_count_month},0) ;;
  }

  measure: order_percent_decrease_week {
    type: number
    value_format: "#.00\%"
    sql: 100* (${active_count_week}-${lag_active_count_week})/nullif(${lag_active_count_week},0) ;;
  }

  measure: week_churn_count {
    label: "Churn Count (Week)"
    type: count_distinct
    drill_fields: [detail_week]
    sql: CASE WHEN ${account_weekly_churn} = 'Churn' THEN ${account_identifier} ELSE null END ;;
  }

  measure: month_churn_count {
    label: "Churn Count (Month)"
    type: count_distinct
    drill_fields: [detail_month]
    sql: CASE WHEN ${account_monthly_churn} = 'Churn' THEN ${account_identifier} ELSE null END ;;
  }
}

account_activity.view.lookml · 103 lines

source-preserved

- view: account_activity
  derived_table:
    sql: SELECT
  o5.order_week
  ,o5.account_identifier
  ,o5.active_count_week
  ,week_join.active_count_week AS lag_active_count_week
  ,o5.order_month
  ,o5.active_count_month
  ,month_join.active_count_month AS lag_active_count_month
  ,id
FROM (
  SELECT
    o4.order_week
    ,o4.account_identifier
    ,o4.active_count_week
    ,o4.order_month
    ,o4.active_count_month
    , id
  FROM
  ${account_activity_intermediate.SQL_TABLE_NAME} AS o4
  ORDER BY 2,1
) AS o5
LEFT JOIN ${month_join.SQL_TABLE_NAME} AS month_join
  ON month_join.order_month = o5.order_month - interval '1 month'
  AND month_join.account_identifier_month = o5.account_identifier
LEFT JOIN ${week_join.SQL_TABLE_NAME} AS week_join
  ON week_join.order_week = o5.order_week - interval '1 week'
  AND week_join.account_identifier_week = o5.account_identifier
ORDER BY 2,1
  fields:
  - dimension: id
    hidden: true
    sql: ${TABLE}.id
  - dimension: account_identifier
    hidden: true
    sql: ${TABLE}.account_identifier
  - dimension: active_count_week
    type: int
    label: Active Account Count (Week)
    sql: ${TABLE}.active_count_week
  - dimension: active_count_month
    type: int
    label: Active Account Count (Month)
    sql: ${TABLE}.active_count_month
  - dimension: lag_active_count_week
    type: int
    label: Active Account Count (Previous Week)
    sql: ${TABLE}.lag_active_count_week
  - dimension: lag_active_count_month
    type: int
    label: Active Account Count (Previous Month)
    sql: ${TABLE}.lag_active_count_month
  - dimension: account_monthly_active
    label: Account Active/Inactive? (Month)
    sql: CASE WHEN ${account_identifier} is not null THEN
  CASE WHEN {% condition order_count_filter %} ${active_count_month} {% endcondition %} THEN 'Active'
  ELSE 'Inactive' end
  ELSE null end
  - dimension: account_weekly_active
    label: Account Active/Inactive? (Week)
    sql: CASE WHEN ${account_identifier} is not null THEN
  CASE WHEN {% condition order_count_filter %} ${active_count_week} {% endcondition %} THEN 'Active'
  ELSE 'Inactive' end
  ELSE null end
  - dimension: account_monthly_churn
    label: Churned? (Month)
    sql: CASE WHEN ${order.account_identifier} is not null THEN
  CASE WHEN {% condition order_count_filter_churn %} ${order_percent_decrease_month} {% endcondition %} THEN 'Churn'
  ELSE null end
  ELSE null end
  - dimension: account_weekly_churn
    label: Churned? (Week)
    sql: CASE WHEN ${order.account_identifier} is not null THEN
  CASE WHEN {% condition order_count_filter_churn %} ${order_percent_decrease_week} {% endcondition %} THEN 'Churn'
  ELSE null end
  ELSE null end
  - dimension_group: order
    type: time
    timeframes:
    - week
    - month
    sql: ${TABLE}.order_week
  - measure: order_percent_decrease_month
    type: number
    value_format: '#.00\%'
    sql: 100* (${active_count_month}-${lag_active_count_month})/nullif(${lag_active_count_month},0)
  - measure: order_percent_decrease_week
    type: number
    value_format: '#.00\%'
    sql: 100* (${active_count_week}-${lag_active_count_week})/nullif(${lag_active_count_week},0)
  - measure: week_churn_count
    type: count_distinct
    label: Churn Count (Week)
    drill_fields:
    - detail_week
    sql: CASE WHEN ${account_weekly_churn} = 'Churn' THEN ${account_identifier} ELSE null END
  - measure: month_churn_count
    type: count_distinct
    label: Churn Count (Month)
    drill_fields:
    - detail_month
    sql: CASE WHEN ${account_monthly_churn} = 'Churn' THEN ${account_identifier} ELSE null END

account_activity.sql
account_activity.sql
account_activity.view.lkml
account_activity.view.lookml

Account Activity

Blocks

Assumptions

Contents