Partner Actions By Day

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

with wd(date) as (
select
        date_trunc('day', dates.d)
from generate_series((select date_trunc('day',min(created_at)) from partner_actions), now(), '1 day') as dates(d)
group by 1
order by 1)

SELECT
  wd.date,
  partner_actions._type AS "partner_actions._type",
  partner_actions.type AS "partner_actions.challenge_type",
  partner_actions.id AS "partner_actions.id",
  partner_actions.partner_id as "partner_actions.partner_id",
  case when date_trunc('day',partner_actions.launched_at) = wd.date then partner_actions.id else null end as "partner_actions.launched",
  case when date_trunc('day',partner_actions.expires_at) = wd.date then partner_actions.id else null end as "partner_actions.expired"
FROM partner_actions, wd

WHERE
  date_trunc('day',partner_actions.expires_at) >= wd.date AND
  date_trunc('day',partner_actions.launched_at) <= wd.date

# models/schema.yml
version: 2

models:
  - name: partner_actions_by_day
    columns:
      - name: partner_actions_type
      - name: partner_id
      - name: partner_action_id
      - name: partner_actions_challenge_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.

with wd(date) as (
select
        date_trunc('day', dates.d)
from generate_series((select date_trunc('day',min(created_at)) from partner_actions), now(), '1 day') as dates(d)
group by 1
order by 1)

SELECT
  wd.date,
  partner_actions._type AS "partner_actions._type",
  partner_actions.type AS "partner_actions.challenge_type",
  partner_actions.id AS "partner_actions.id",
  partner_actions.partner_id as "partner_actions.partner_id",
  case when date_trunc('day',partner_actions.launched_at) = wd.date then partner_actions.id else null end as "partner_actions.launched",
  case when date_trunc('day',partner_actions.expires_at) = wd.date then partner_actions.id else null end as "partner_actions.expired"
FROM partner_actions, wd

WHERE
  date_trunc('day',partner_actions.expires_at) >= wd.date AND
  date_trunc('day',partner_actions.launched_at) <= wd.date

view: partner_actions_by_day {
  derived_table: {
    sql: with wd(date) as (
select
        date_trunc('day', dates.d)
from generate_series((select date_trunc('day',min(created_at)) from partner_actions), now(), '1 day') as dates(d)
group by 1
order by 1)

SELECT
  wd.date,
  partner_actions._type AS "partner_actions._type",
  partner_actions.type AS "partner_actions.challenge_type",
  partner_actions.id AS "partner_actions.id",
  partner_actions.partner_id as "partner_actions.partner_id",
  case when date_trunc('day',partner_actions.launched_at) = wd.date then partner_actions.id else null end as "partner_actions.launched",
  case when date_trunc('day',partner_actions.expires_at) = wd.date then partner_actions.id else null end as "partner_actions.expired"
FROM partner_actions, wd

WHERE
  date_trunc('day',partner_actions.expires_at) >= wd.date AND
  date_trunc('day',partner_actions.launched_at) <= wd.date ;;
  }

  dimension: partner_actions_type {
    sql: ${TABLE}."partner_actions._type" ;;
  }

  dimension: partner_id {
    sql: ${TABLE}."partner_actions.partner_id" ;;
  }

  dimension: partner_action_id {
    sql: ${TABLE}."partner_actions.id" ;;
  }

  dimension: partner_actions_challenge_type {
    sql: ${TABLE}."partner_actions.challenge_type" ;;
  }

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

  measure: partner_actions_launched {
    type: count_distinct
    sql: ${TABLE}."partner_actions.launched" ;;
  }

  measure: partner_actions_expired {
    type: count_distinct
    sql: ${TABLE}."partner_actions.expired" ;;
  }

  measure: partner_actions_active {
    type: count_distinct
    sql: ${TABLE}."partner_actions.id" ;;
  }
}

- view: partner_actions_by_day
  derived_table:
    sql: with wd(date) as (
select
        date_trunc('day', dates.d)
from generate_series((select date_trunc('day',min(created_at)) from partner_actions), now(), '1 day') as dates(d)
group by 1
order by 1)

SELECT
  wd.date,
  partner_actions._type AS "partner_actions._type",
  partner_actions.type AS "partner_actions.challenge_type",
  partner_actions.id AS "partner_actions.id",
  partner_actions.partner_id as "partner_actions.partner_id",
  case when date_trunc('day',partner_actions.launched_at) = wd.date then partner_actions.id else null end as "partner_actions.launched",
  case when date_trunc('day',partner_actions.expires_at) = wd.date then partner_actions.id else null end as "partner_actions.expired"
FROM partner_actions, wd

WHERE
  date_trunc('day',partner_actions.expires_at) >= wd.date AND
  date_trunc('day',partner_actions.launched_at) <= wd.date
  fields:
  - dimension: partner_actions_type
    sql: ${TABLE}."partner_actions._type"
  - dimension: partner_id
    sql: ${TABLE}."partner_actions.partner_id"
  - dimension: partner_action_id
    sql: ${TABLE}."partner_actions.id"
  - dimension: partner_actions_challenge_type
    sql: ${TABLE}."partner_actions.challenge_type"
  - dimension_group: date
    type: time
    timeframes:
    - date
    - week
    - month
    convert_tz: false
    sql: ${TABLE}.date
  - measure: partner_actions_launched
    type: count_distinct
    sql: ${TABLE}."partner_actions.launched"
  - measure: partner_actions_expired
    type: count_distinct
    sql: ${TABLE}."partner_actions.expired"
  - measure: partner_actions_active
    type: count_distinct
    sql: ${TABLE}."partner_actions.id"