Dt C Active Campaigns

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

select date_trunc('week', date_lookup) as date,
  case when a.api_user_id = '1' then 'PlatformDomain.com'
  else 'iOS' end as type,
  count(distinct case when a.creationdate::date <= b.date_lookup
  and a.expiration::date > b.date_lookup then a.id end) as active_campaigns
  from campaigns a
  join
  (
  select distinct creationdate::date as date_lookup from campaigns
  ) b
  on a.creationdate::date <= b.date_lookup
  where a.title not ilike '%test%'
  and b.date_lookup >= '2012-02-01'
  and a.api_user_id in ('1', '<% parameter api_user_id_placeholder %>')
  and a.verified <> 3
  and a.deleted = 0
  group by 1,2
  order by 1

# models/schema.yml
version: 2

models:
  - name: dt_c_active_campaigns
    columns:
      - name: date
      - name: 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 date_trunc('week', date_lookup) as date,
  case when a.api_user_id = '1' then 'PlatformDomain.com'
  else 'iOS' end as type,
  count(distinct case when a.creationdate::date <= b.date_lookup
  and a.expiration::date > b.date_lookup then a.id end) as active_campaigns
  from campaigns a
  join
  (
  select distinct creationdate::date as date_lookup from campaigns
  ) b
  on a.creationdate::date <= b.date_lookup
  where a.title not ilike '%test%'
  and b.date_lookup >= '2012-02-01'
  and a.api_user_id in ('1', '<% parameter api_user_id_placeholder %>')
  and a.verified <> 3
  and a.deleted = 0
  group by 1,2
  order by 1

view: dt_c_active_campaigns {
  derived_table: {
    sql: select date_trunc('week', date_lookup) as date,
  case when a.api_user_id = '1' then 'PlatformDomain.com'
  else 'iOS' end as type,
  count(distinct case when a.creationdate::date <= b.date_lookup
  and a.expiration::date > b.date_lookup then a.id end) as active_campaigns
  from campaigns a
  join
  (
  select distinct creationdate::date as date_lookup from campaigns
  ) b
  on a.creationdate::date <= b.date_lookup
  where a.title not ilike '%test%'
  and b.date_lookup >= '2012-02-01'
  and a.api_user_id in ('1', '<% parameter api_user_id_placeholder %>')
  and a.verified <> 3
  and a.deleted = 0
  group by 1,2
  order by 1 ;;
  }

  dimension: date {
    type: date
    sql: ${TABLE}.date ;;
  }

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

  measure: active_campaigns {
    type: sum
    sql: ${TABLE}.active_campaigns ;;
  }
}

- view: dt_c_active_campaigns
  derived_table:
    sql: select date_trunc('week', date_lookup) as date,
  case when a.api_user_id = '1' then 'PlatformDomain.com'
  else 'iOS' end as type,
  count(distinct case when a.creationdate::date <= b.date_lookup
  and a.expiration::date > b.date_lookup then a.id end) as active_campaigns
  from campaigns a
  join
  (
  select distinct creationdate::date as date_lookup from campaigns
  ) b
  on a.creationdate::date <= b.date_lookup
  where a.title not ilike '%test%'
  and b.date_lookup >= '2012-02-01'
  and a.api_user_id in ('1', '<% parameter api_user_id_placeholder %>')
  and a.verified <> 3
  and a.deleted = 0
  group by 1,2
  order by 1
  fields:
  - dimension: date
    type: date
    sql: ${TABLE}.date
  - dimension: type
    sql: ${TABLE}.type
  - measure: active_campaigns
    type: sum
    sql: ${TABLE}.active_campaigns