Active Within Period Over Time

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

with mesh_days as (
  select distinct date_trunc('day', message_ts) as early_day, mesh_id
  from {{ this }}.view_access join analytics.user_account_map using(mesh_id)
  where message_ts >= '2015-04-01' and message_ts < localtimestamp and not coalesce(is_internal, 0) and created >= '2014-05-20' and dateadd(hour, 24, created) <= message_ts
), activity_days as (
  select distinct date_trunc('day', message_ts) as late_day
  from {{ this }}.device_info
  where message_ts >= '2015-05-01' and message_ts < localtimestamp
), descent_into_madness as (
  select * from mesh_days, activity_days
  where early_day <= late_day and early_day + (interval '30 days') >= late_day
) select
  to_char(late_day, 'YYYY-MM-DD') as "day",
  count(distinct case when extract(day from late_day - early_day) < 7 then mesh_id else null end) as active_within_7,
  count(distinct case when extract(day from late_day - early_day) < 30 then mesh_id else null end) as active_within_30
from descent_into_madness
group by late_day

# models/schema.yml
version: 2

models:
  - name: active_within_period_over_time
    columns:
      - name: date
        description: "YYYY-MM-DD"
      - name: active_within_7_days
      - name: active_within_30_days

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.

with mesh_days as (
  select distinct date_trunc('day', message_ts) as early_day, mesh_id
  from ${TABLE}.view_access join analytics.user_account_map using(mesh_id)
  where message_ts >= '2015-04-01' and message_ts < localtimestamp and not coalesce(is_internal, 0) and created >= '2014-05-20' and dateadd(hour, 24, created) <= message_ts
), activity_days as (
  select distinct date_trunc('day', message_ts) as late_day
  from ${TABLE}.device_info
  where message_ts >= '2015-05-01' and message_ts < localtimestamp
), descent_into_madness as (
  select * from mesh_days, activity_days
  where early_day <= late_day and early_day + (interval '30 days') >= late_day
) select
  to_char(late_day, 'YYYY-MM-DD') as "day",
  count(distinct case when extract(day from late_day - early_day) < 7 then mesh_id else null end) as active_within_7,
  count(distinct case when extract(day from late_day - early_day) < 30 then mesh_id else null end) as active_within_30
from descent_into_madness
group by late_day

view: active_within_period_over_time {
  derived_table: {
    sql: with mesh_days as (
  select distinct date_trunc('day', message_ts) as early_day, mesh_id
  from ${TABLE}.view_access join analytics.user_account_map using(mesh_id)
  where message_ts >= '2015-04-01' and message_ts < localtimestamp and not coalesce(is_internal, 0) and created >= '2014-05-20' and dateadd(hour, 24, created) <= message_ts
), activity_days as (
  select distinct date_trunc('day', message_ts) as late_day
  from ${TABLE}.device_info
  where message_ts >= '2015-05-01' and message_ts < localtimestamp
), descent_into_madness as (
  select * from mesh_days, activity_days
  where early_day <= late_day and early_day + (interval '30 days') >= late_day
) select
  to_char(late_day, 'YYYY-MM-DD') as "day",
  count(distinct case when extract(day from late_day - early_day) < 7 then mesh_id else null end) as active_within_7,
  count(distinct case when extract(day from late_day - early_day) < 30 then mesh_id else null end) as active_within_30
from descent_into_madness
group by late_day ;;
  }

  dimension: date {
    description: "YYYY-MM-DD"
    type: string
    sql: ${TABLE}.day ;;
  }

  dimension: active_within_7_days {
    type: number
    sql: ${TABLE}.active_within_7 ;;
  }

  dimension: active_within_30_days {
    type: number
    sql: ${TABLE}.active_within_30 ;;
  }
}

- view: active_within_period_over_time
  derived_table:
    sql: with mesh_days as (
  select distinct date_trunc('day', message_ts) as early_day, mesh_id
  from ${TABLE}.view_access join analytics.user_account_map using(mesh_id)
  where message_ts >= '2015-04-01' and message_ts < localtimestamp and not coalesce(is_internal, 0) and created >= '2014-05-20' and dateadd(hour, 24, created) <= message_ts
), activity_days as (
  select distinct date_trunc('day', message_ts) as late_day
  from ${TABLE}.device_info
  where message_ts >= '2015-05-01' and message_ts < localtimestamp
), descent_into_madness as (
  select * from mesh_days, activity_days
  where early_day <= late_day and early_day + (interval '30 days') >= late_day
) select
  to_char(late_day, 'YYYY-MM-DD') as "day",
  count(distinct case when extract(day from late_day - early_day) < 7 then mesh_id else null end) as active_within_7,
  count(distinct case when extract(day from late_day - early_day) < 30 then mesh_id else null end) as active_within_30
from descent_into_madness
group by late_day
  fields:
  - dimension: date
    type: string
    description: YYYY-MM-DD
    sql: ${TABLE}.day
  - dimension: active_within_7_days
    type: number
    sql: ${TABLE}.active_within_7
  - dimension: active_within_30_days
    type: number
    sql: ${TABLE}.active_within_30