Existing Daily Active

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

select a.fund_id,COALESCE((CASE upper(a.currencycode)
       WHEN '<% currency_AUD %>' THEN a.amount*.88
       WHEN '<% currency_GBP %>' THEN a.amount*1.59
       WHEN '<% currency_EUR %>' THEN a.amount*1.27
       WHEN '<% currency_CAD %>' THEN a.amount*.94
      ELSE a.amount
      END),0) AS amount,  a.created_at
from donations a left outer join
(select fund_id, min(CONVERT_TIMEZONE ('UTC','America/Chicago',created_at))as min_week
from donations a
where verified=1
and type in (0,1,2,5,6)
and a.created_at>='2014-01-01'
group by fund_id)b
on a.fund_id=b.fund_id
and to_char(CONVERT_TIMEZONE ('UTC','America/Chicago',created_at),'MM-DD-YY')= to_char(min_week, 'MM-DD-YY')
where a.verified=1
and a.created_at>='2014-01-01'
and a.type in (0,1,2,5,6)
and b.fund_id is null

# models/schema.yml
version: 2

models:
  - name: existing_daily_active
    columns:
      - name: fund_id
      - name: amount

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.fund_id,COALESCE((CASE upper(a.currencycode)
       WHEN '<% currency_AUD %>' THEN a.amount*.88
       WHEN '<% currency_GBP %>' THEN a.amount*1.59
       WHEN '<% currency_EUR %>' THEN a.amount*1.27
       WHEN '<% currency_CAD %>' THEN a.amount*.94
      ELSE a.amount
      END),0) AS amount,  a.created_at
from donations a left outer join
(select fund_id, min(CONVERT_TIMEZONE ('UTC','America/Chicago',created_at))as min_week
from donations a
where verified=1
and type in (0,1,2,5,6)
and a.created_at>='2014-01-01'
group by fund_id)b
on a.fund_id=b.fund_id
and to_char(CONVERT_TIMEZONE ('UTC','America/Chicago',created_at),'MM-DD-YY')= to_char(min_week, 'MM-DD-YY')
where a.verified=1
and a.created_at>='2014-01-01'
and a.type in (0,1,2,5,6)
and b.fund_id is null

view: existing_daily_active {
  derived_table: {
    sql: select a.fund_id,COALESCE((CASE upper(a.currencycode)
       WHEN '<% currency_AUD %>' THEN a.amount*.88
       WHEN '<% currency_GBP %>' THEN a.amount*1.59
       WHEN '<% currency_EUR %>' THEN a.amount*1.27
       WHEN '<% currency_CAD %>' THEN a.amount*.94
      ELSE a.amount
      END),0) AS amount,  a.created_at
from donations a left outer join
(select fund_id, min(CONVERT_TIMEZONE ('UTC','America/Chicago',created_at))as min_week
from donations a
where verified=1
and type in (0,1,2,5,6)
and a.created_at>='2014-01-01'
group by fund_id)b
on a.fund_id=b.fund_id
and to_char(CONVERT_TIMEZONE ('UTC','America/Chicago',created_at),'MM-DD-YY')= to_char(min_week, 'MM-DD-YY')
where a.verified=1
and a.created_at>='2014-01-01'
and a.type in (0,1,2,5,6)
and b.fund_id is null ;;
  }

  dimension: fund_id {
    type: number
    sql: ${TABLE}.fund_id ;;
  }

  dimension: amount {
    type: number
    sql: ${TABLE}.amount ;;
  }

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

  measure: total {
    type: sum
    value_format: "$#,###"
    sql: ${TABLE}.amount ;;
  }

  measure: count_funds {
    type: count_distinct
    sql: ${TABLE}.fund_id ;;
  }
}

- view: existing_daily_active
  derived_table:
    sql: select a.fund_id,COALESCE((CASE upper(a.currencycode)
       WHEN '<% currency_AUD %>' THEN a.amount*.88
       WHEN '<% currency_GBP %>' THEN a.amount*1.59
       WHEN '<% currency_EUR %>' THEN a.amount*1.27
       WHEN '<% currency_CAD %>' THEN a.amount*.94
      ELSE a.amount
      END),0) AS amount,  a.created_at
from donations a left outer join
(select fund_id, min(CONVERT_TIMEZONE ('UTC','America/Chicago',created_at))as min_week
from donations a
where verified=1
and type in (0,1,2,5,6)
and a.created_at>='2014-01-01'
group by fund_id)b
on a.fund_id=b.fund_id
and to_char(CONVERT_TIMEZONE ('UTC','America/Chicago',created_at),'MM-DD-YY')= to_char(min_week, 'MM-DD-YY')
where a.verified=1
and a.created_at>='2014-01-01'
and a.type in (0,1,2,5,6)
and b.fund_id is null
  fields:
  - dimension: fund_id
    type: number
    sql: ${TABLE}.fund_id
  - dimension: amount
    type: number
    sql: ${TABLE}.amount
  - dimension_group: created
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.created_at
  - measure: total
    type: sum
    value_format: '$#,###'
    sql: ${TABLE}.amount
  - measure: count_funds
    type: count_distinct
    sql: ${TABLE}.fund_id