Dt S Contributor Admin

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

select
  a.user_id as user_id,
  min(a.creation_date) as first_contribution_date,
  min(b.creationdate) as first_creation_date,
  coalesce((extract(epoch from (min(b.creationdate) - min(a.creation_date)))/(3600*24))::int, 99999) as datediff
from contributions a
join users c
on a.user_id = c.id
left outer join campaigns b
on a.user_Id = b.user_id and b.verified <> 3 and b.deleted = 0
where a.status in ('postauth_accepted', 'postauth_approved', 'sale_approved', 'credit_released', 'preauth_approved')
group by 1

# models/schema.yml
version: 2

models:
  - name: dt_s_contributor_admin
    columns:
      - name: user_id
      - name: datediff
      - name: first_creation

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
  a.user_id as user_id,
  min(a.creation_date) as first_contribution_date,
  min(b.creationdate) as first_creation_date,
  coalesce((extract(epoch from (min(b.creationdate) - min(a.creation_date)))/(3600*24))::int, 99999) as datediff
from contributions a
join users c
on a.user_id = c.id
left outer join campaigns b
on a.user_Id = b.user_id and b.verified <> 3 and b.deleted = 0
where a.status in ('postauth_accepted', 'postauth_approved', 'sale_approved', 'credit_released', 'preauth_approved')
group by 1

view: dt_s_contributor_admin {
  derived_table: {
    sql: select
  a.user_id as user_id,
  min(a.creation_date) as first_contribution_date,
  min(b.creationdate) as first_creation_date,
  coalesce((extract(epoch from (min(b.creationdate) - min(a.creation_date)))/(3600*24))::int, 99999) as datediff
from contributions a
join users c
on a.user_id = c.id
left outer join campaigns b
on a.user_Id = b.user_id and b.verified <> 3 and b.deleted = 0
where a.status in ('postauth_accepted', 'postauth_approved', 'sale_approved', 'credit_released', 'preauth_approved')
group by 1 ;;
  }

  dimension: user_id {
  }

  dimension: datediff {
    type: number
  }

  dimension: first_creation {
    type: time
    sql: ${TABLE}.first_creation_date ;;
  }

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

  measure: distinplatform_contributors_converted {
    type: count_distinct
    filters: [dt_s_contributor_admin.datediff: "NOT 99999,[-inf,0)"]
    sql: ${TABLE}.user_id ;;
  }

  measure: distinplatform_contributors {
    type: count_distinct
    filters: [dt_s_contributor_admin.datediff: "0 to"]
    sql: ${TABLE}.user_id ;;
  }

  measure: distinplatform_contributors_converted_90 {
    type: count_distinct
    filters: [dt_s_contributor_admin.datediff: "0 to 90"]
    sql: ${TABLE}.user_id ;;
  }

  measure: contributor_conversion_rate {
    type: number
    sql: 100.0*${distinplatform_contributors_converted}/${distinplatform_contributors} ;;
  }

  measure: contributor_conversion_rate_90 {
    type: number
    sql: 100.0*${distinplatform_contributors_converted_90}/${distinplatform_contributors} ;;
  }
}

- view: dt_s_contributor_admin
  derived_table:
    sql: select
  a.user_id as user_id,
  min(a.creation_date) as first_contribution_date,
  min(b.creationdate) as first_creation_date,
  coalesce((extract(epoch from (min(b.creationdate) - min(a.creation_date)))/(3600*24))::int, 99999) as datediff
from contributions a
join users c
on a.user_id = c.id
left outer join campaigns b
on a.user_Id = b.user_id and b.verified <> 3 and b.deleted = 0
where a.status in ('postauth_accepted', 'postauth_approved', 'sale_approved', 'credit_released', 'preauth_approved')
group by 1
  fields:
  - dimension: user_id
  - dimension: datediff
    type: number
  - dimension: first_creation
    type: time
    sql: ${TABLE}.first_creation_date
  - dimension_group: first_contribution
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.first_contribution_date
  - measure: distinplatform_contributors_converted
    type: count_distinct
    filters:
    - dt_s_contributor_admin.datediff: NOT 99999,[-inf,0)
    sql: ${TABLE}.user_id
  - measure: distinplatform_contributors
    type: count_distinct
    filters:
    - dt_s_contributor_admin.datediff: 0 to
    sql: ${TABLE}.user_id
  - measure: distinplatform_contributors_converted_90
    type: count_distinct
    filters:
    - dt_s_contributor_admin.datediff: 0 to 90
    sql: ${TABLE}.user_id
  - measure: contributor_conversion_rate
    type: number
    sql: 100.0*${distinplatform_contributors_converted}/${distinplatform_contributors}
  - measure: contributor_conversion_rate_90
    type: number
    sql: 100.0*${distinplatform_contributors_converted_90}/${distinplatform_contributors}