Business Update Key

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

select 'week 1',
min (date_field) as start_date, max (date_field) as end_date from
data_campaign_dashboard
where
  date_field::date >= current_date - interval '8 days'
and
  date_field::date < current_date - interval '1 days'
group by 1
union all
select 'week 2',
min (date_field)as start_date, max (date_field) as end_date from
data_campaign_dashboard
where
  date_field::date >= current_date - interval '15 days'
and
  date_field::date < current_date - interval '8 days'
group by 1
union all
select 'week 3',
min (date_field)as start_date, max (date_field) as end_date from
data_campaign_dashboard
where
  date_field::date >= current_date - interval '22 days'
and
  date_field::date < current_date - interval '15 days'
group by 1
union all
select 'week 4',
min (date_field)as start_date, max (date_field)  as end_date from
data_campaign_dashboard
where
  date_field::date >= current_date - interval '29 days'
and
  date_field::date < current_date - interval '22 days'
group by 1

# models/schema.yml
version: 2

models:
  - name: business_update_key
    columns:
      - name: week
      - name: start_date
      - name: end_date

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 'week 1',
min (date_field) as start_date, max (date_field) as end_date from
data_campaign_dashboard
where
  date_field::date >= current_date - interval '8 days'
and
  date_field::date < current_date - interval '1 days'
group by 1
union all
select 'week 2',
min (date_field)as start_date, max (date_field) as end_date from
data_campaign_dashboard
where
  date_field::date >= current_date - interval '15 days'
and
  date_field::date < current_date - interval '8 days'
group by 1
union all
select 'week 3',
min (date_field)as start_date, max (date_field) as end_date from
data_campaign_dashboard
where
  date_field::date >= current_date - interval '22 days'
and
  date_field::date < current_date - interval '15 days'
group by 1
union all
select 'week 4',
min (date_field)as start_date, max (date_field)  as end_date from
data_campaign_dashboard
where
  date_field::date >= current_date - interval '29 days'
and
  date_field::date < current_date - interval '22 days'
group by 1

view: business_update_key {
  derived_table: {
    sql: select 'week 1',
min (date_field) as start_date, max (date_field) as end_date from
data_campaign_dashboard
where
  date_field::date >= current_date - interval '8 days'
and
  date_field::date < current_date - interval '1 days'
group by 1
union all
select 'week 2',
min (date_field)as start_date, max (date_field) as end_date from
data_campaign_dashboard
where
  date_field::date >= current_date - interval '15 days'
and
  date_field::date < current_date - interval '8 days'
group by 1
union all
select 'week 3',
min (date_field)as start_date, max (date_field) as end_date from
data_campaign_dashboard
where
  date_field::date >= current_date - interval '22 days'
and
  date_field::date < current_date - interval '15 days'
group by 1
union all
select 'week 4',
min (date_field)as start_date, max (date_field)  as end_date from
data_campaign_dashboard
where
  date_field::date >= current_date - interval '29 days'
and
  date_field::date < current_date - interval '22 days'
group by 1 ;;
  }

  dimension: week {
    sql: ${TABLE}."?column?" ;;
  }

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

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

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

- view: business_update_key
  derived_table:
    sql: select 'week 1',
min (date_field) as start_date, max (date_field) as end_date from
data_campaign_dashboard
where
  date_field::date >= current_date - interval '8 days'
and
  date_field::date < current_date - interval '1 days'
group by 1
union all
select 'week 2',
min (date_field)as start_date, max (date_field) as end_date from
data_campaign_dashboard
where
  date_field::date >= current_date - interval '15 days'
and
  date_field::date < current_date - interval '8 days'
group by 1
union all
select 'week 3',
min (date_field)as start_date, max (date_field) as end_date from
data_campaign_dashboard
where
  date_field::date >= current_date - interval '22 days'
and
  date_field::date < current_date - interval '15 days'
group by 1
union all
select 'week 4',
min (date_field)as start_date, max (date_field)  as end_date from
data_campaign_dashboard
where
  date_field::date >= current_date - interval '29 days'
and
  date_field::date < current_date - interval '22 days'
group by 1
  fields:
  - dimension: week
    sql: ${TABLE}."?column?"
  - dimension: start_date
    sql: ${TABLE}.start_date
  - dimension: end_date
    sql: ${TABLE}.end_date
  - measure: count
    type: count
    drill_fields:
    - detail*