Entity Story Lifetime Facts

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

SELECT
  entities.id AS "id"
  , entities.name AS "name"
  , COUNT(*) AS "lifetime_articles"
  , MIN(s.created_at) AS "first_story_created"
  , MAX(s.created_at) AS "latest_story_created"
  , MIN(s.published_at) AS "first_story_published"
  , MAX(s.published_at) AS "latest_story_published"
  , COUNT(DISTINCT DATE_TRUNC('month', s.created_at)) as number_of_distinct_months_with_story
FROM articles AS s
LEFT JOIN profiles AS external_profiles ON s.brand_profile_id = entities.id
WHERE entities.internal = 'No'
GROUP BY 1
ORDER BY 2 DESC

# models/schema.yml
version: 2

models:
  - name: entity_story_lifetime_facts
    columns:
      - name: id
        tests:
          - unique
          - not_null
      - name: name
      - name: lifetime_articles
      - name: lifetime_articles_tier
      - name: repeat_story_customer
      - name: number_of_distinct_months_with_story
      - name: number_of_distinct_months_with_story_tier

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
  entities.id AS "id"
  , entities.name AS "name"
  , COUNT(*) AS "lifetime_articles"
  , MIN(s.created_at) AS "first_story_created"
  , MAX(s.created_at) AS "latest_story_created"
  , MIN(s.published_at) AS "first_story_published"
  , MAX(s.published_at) AS "latest_story_published"
  , COUNT(DISTINCT DATE_TRUNC('month', s.created_at)) as number_of_distinct_months_with_story
FROM articles AS s
LEFT JOIN profiles AS external_profiles ON s.brand_profile_id = entities.id
WHERE entities.internal = 'No'
GROUP BY 1
ORDER BY 2 DESC

view: entity_story_lifetime_facts {
  derived_table: {
    sql: SELECT
  entities.id AS "id"
  , entities.name AS "name"
  , COUNT(*) AS "lifetime_articles"
  , MIN(s.created_at) AS "first_story_created"
  , MAX(s.created_at) AS "latest_story_created"
  , MIN(s.published_at) AS "first_story_published"
  , MAX(s.published_at) AS "latest_story_published"
  , COUNT(DISTINCT DATE_TRUNC('month', s.created_at)) as number_of_distinct_months_with_story
FROM articles AS s
LEFT JOIN profiles AS external_profiles ON s.brand_profile_id = entities.id
WHERE entities.internal = 'No'
GROUP BY 1
ORDER BY 2 DESC ;;
  }

  dimension: id {
    primary_key: yes
    hidden: yes
    sql: ${TABLE}.id ;;
  }

  dimension: name {
    hidden: yes
    sql: ${TABLE}.name ;;
  }

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

  dimension: lifetime_articles_tier {
    type: tier
    sql: ${lifetime_articles} ;;
  }

  dimension: repeat_story_customer {
    type: yesno
    sql: ${lifetime_articles} > 1 ;;
  }

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

  dimension: number_of_distinct_months_with_story_tier {
    type: tier
    sql: ${number_of_distinct_months_with_story} ;;
  }

  dimension_group: first_story_created {
    type: time
    timeframes: [date, month, year]
    sql: ${TABLE}.first_story_created ;;
  }

  dimension_group: latest_story_created {
    type: time
    timeframes: [date, month, year]
    sql: ${TABLE}.latest_story_created ;;
  }

  dimension_group: first_story_published {
    type: time
    timeframes: [date, month, year]
    sql: ${TABLE}.first_story_published ;;
  }

  dimension_group: latest_story_published {
    type: time
    timeframes: [date, month, year]
    sql: ${TABLE}.latest_story_published ;;
  }

  measure: date_of_first_story_created {
    type: date
    sql: min(${first_story_created_date}) ;;
  }

  measure: date_of_latest_story_created {
    type: date
    sql: max(${latest_story_created_date}) ;;
  }

  measure: date_of_first_story_published {
    type: date
    sql: min(${first_story_published_date}) ;;
  }

  measure: date_of_latest_story_published {
    type: date
    sql: max(${latest_story_published_date}) ;;
  }

  measure: active_pub_trigger {
    type: sum
    sql: case
  when current_date - ${latest_story_date} > 7
  then 0
  else 1
end ;;
  }
}

- view: entity_story_lifetime_facts
  derived_table:
    sql: SELECT
  entities.id AS "id"
  , entities.name AS "name"
  , COUNT(*) AS "lifetime_articles"
  , MIN(s.created_at) AS "first_story_created"
  , MAX(s.created_at) AS "latest_story_created"
  , MIN(s.published_at) AS "first_story_published"
  , MAX(s.published_at) AS "latest_story_published"
  , COUNT(DISTINCT DATE_TRUNC('month', s.created_at)) as number_of_distinct_months_with_story
FROM articles AS s
LEFT JOIN profiles AS external_profiles ON s.brand_profile_id = entities.id
WHERE entities.internal = 'No'
GROUP BY 1
ORDER BY 2 DESC
  fields:
  - dimension: id
    primary_key: true
    hidden: true
    sql: ${TABLE}.id
  - dimension: name
    hidden: true
    sql: ${TABLE}.name
  - dimension: lifetime_articles
    type: number
    sql: ${TABLE}.lifetime_articles
  - dimension: lifetime_articles_tier
    type: tier
    sql: ${lifetime_articles}
  - dimension: repeat_story_customer
    type: yesno
    sql: ${lifetime_articles} > 1
  - dimension: number_of_distinct_months_with_story
    type: number
    sql: ${TABLE}.number_of_distinct_months_with_story
  - dimension: number_of_distinct_months_with_story_tier
    type: tier
    sql: ${number_of_distinct_months_with_story}
  - dimension_group: first_story_created
    type: time
    timeframes:
    - date
    - month
    - year
    sql: ${TABLE}.first_story_created
  - dimension_group: latest_story_created
    type: time
    timeframes:
    - date
    - month
    - year
    sql: ${TABLE}.latest_story_created
  - dimension_group: first_story_published
    type: time
    timeframes:
    - date
    - month
    - year
    sql: ${TABLE}.first_story_published
  - dimension_group: latest_story_published
    type: time
    timeframes:
    - date
    - month
    - year
    sql: ${TABLE}.latest_story_published
  - measure: date_of_first_story_created
    type: date
    sql: min(${first_story_created_date})
  - measure: date_of_latest_story_created
    type: date
    sql: max(${latest_story_created_date})
  - measure: date_of_first_story_published
    type: date
    sql: min(${first_story_published_date})
  - measure: date_of_latest_story_published
    type: date
    sql: max(${latest_story_published_date})
  - measure: active_pub_trigger
    type: sum
    sql: case
  when current_date - ${latest_story_date} > 7
  then 0
  else 1
end