Partner Action Duration

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

select
partner_actions.launched_at as launched_date,
partners.name as partner,
partner_actions._type as partner_action_type,
EXTRACT(epoch FROM partner_actions.closed_at - partner_actions.launched_at)/86400 as partner_action_duration_days,
case when packages.name like '%CustomerType5erType1%' then 'CustomerType5erType1s'
 when packages.name like '%CustomerType5erType2%' then 'CustomerType5erType2'
 when packages.name like '%CustomerType5erType3%' then 'CustomerType5erType3'
 when packages.name like '%CustomerType5erType4%' then 'CustomerType5erType3'
 when packages.name like '%CustomerType5%' then 'CustomerType5'
 else 'Other' end
 as customer_type
from partner_actions, subscriptions, packages, partners, member_filters

where
subscriptions.package_id = packages.id and
partners.id = subscriptions.embedded_in_id and
partner_actions.partner_id = partners.id and
 partner_actions.id = member_filters.embedded_in_id and
  member_filters.embedded_in_table = 'partner_actions'
  and partner_actions.launched_at is not null and partner_actions.closed_at is not null

# models/schema.yml
version: 2

models:
  - name: partner_action_duration
    columns:
      - name: partner
      - name: partner_action_type
      - name: customer_type

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
partner_actions.launched_at as launched_date,
partners.name as partner,
partner_actions._type as partner_action_type,
EXTRACT(epoch FROM partner_actions.closed_at - partner_actions.launched_at)/86400 as partner_action_duration_days,
case when packages.name like '%CustomerType5erType1%' then 'CustomerType5erType1s'
 when packages.name like '%CustomerType5erType2%' then 'CustomerType5erType2'
 when packages.name like '%CustomerType5erType3%' then 'CustomerType5erType3'
 when packages.name like '%CustomerType5erType4%' then 'CustomerType5erType3'
 when packages.name like '%CustomerType5%' then 'CustomerType5'
 else 'Other' end
 as customer_type
from partner_actions, subscriptions, packages, partners, member_filters

where
subscriptions.package_id = packages.id and
partners.id = subscriptions.embedded_in_id and
partner_actions.partner_id = partners.id and
 partner_actions.id = member_filters.embedded_in_id and
  member_filters.embedded_in_table = 'partner_actions'
  and partner_actions.launched_at is not null and partner_actions.closed_at is not null

view: partner_action_duration {
  derived_table: {
    sql: select
partner_actions.launched_at as launched_date,
partners.name as partner,
partner_actions._type as partner_action_type,
EXTRACT(epoch FROM partner_actions.closed_at - partner_actions.launched_at)/86400 as partner_action_duration_days,
case when packages.name like '%CustomerType5erType1%' then 'CustomerType5erType1s'
 when packages.name like '%CustomerType5erType2%' then 'CustomerType5erType2'
 when packages.name like '%CustomerType5erType3%' then 'CustomerType5erType3'
 when packages.name like '%CustomerType5erType4%' then 'CustomerType5erType3'
 when packages.name like '%CustomerType5%' then 'CustomerType5'
 else 'Other' end
 as customer_type
from partner_actions, subscriptions, packages, partners, member_filters

where
subscriptions.package_id = packages.id and
partners.id = subscriptions.embedded_in_id and
partner_actions.partner_id = partners.id and
 partner_actions.id = member_filters.embedded_in_id and
  member_filters.embedded_in_table = 'partner_actions'
  and partner_actions.launched_at is not null and partner_actions.closed_at is not null ;;
  }

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

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

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

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

  measure: count {
    type: count
    drill_fields: [detail*]
  }

  measure: average_count_per_partner {
    type: number
    drill_fields: [detail*]
    sql: count(*) / count(distinct ${TABLE}.partner) ;;
  }

  measure: partner_action_duration_days {
    type: avg
    sql: ${TABLE}.partner_action_duration_days ;;
  }
}

- view: partner_action_duration
  derived_table:
    sql: select
partner_actions.launched_at as launched_date,
partners.name as partner,
partner_actions._type as partner_action_type,
EXTRACT(epoch FROM partner_actions.closed_at - partner_actions.launched_at)/86400 as partner_action_duration_days,
case when packages.name like '%CustomerType5erType1%' then 'CustomerType5erType1s'
 when packages.name like '%CustomerType5erType2%' then 'CustomerType5erType2'
 when packages.name like '%CustomerType5erType3%' then 'CustomerType5erType3'
 when packages.name like '%CustomerType5erType4%' then 'CustomerType5erType3'
 when packages.name like '%CustomerType5%' then 'CustomerType5'
 else 'Other' end
 as customer_type
from partner_actions, subscriptions, packages, partners, member_filters

where
subscriptions.package_id = packages.id and
partners.id = subscriptions.embedded_in_id and
partner_actions.partner_id = partners.id and
 partner_actions.id = member_filters.embedded_in_id and
  member_filters.embedded_in_table = 'partner_actions'
  and partner_actions.launched_at is not null and partner_actions.closed_at is not null
  fields:
  - dimension: partner
    sql: ${TABLE}.partner
  - dimension: partner_action_type
    sql: ${TABLE}.partner_action_type
  - dimension: customer_type
    sql: ${TABLE}.customer_type
  - dimension_group: launched_date
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.launched_date
  - measure: count
    type: count
    drill_fields:
    - detail*
  - measure: average_count_per_partner
    type: number
    drill_fields:
    - detail*
    sql: count(*) / count(distinct ${TABLE}.partner)
  - measure: partner_action_duration_days
    type: avg
    sql: ${TABLE}.partner_action_duration_days