Articles And Proposals

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

SELECT 'pitch' AS type,
    proposals.id AS "pitch_id",
    DATE((proposals.created_at)::timestamptz AT TIME ZONE 'America/New_York') AS "created_date",
    entities.name AS "entity_name",
    entities.id AS "entity_id",
    proposals.story_id AS "story_id",
    proposals.creator_id AS "creator_id"
FROM proposals
JOIN profiles AS external_profiles ON proposals.brand_profile_id = entities.id
UNION ALL
SELECT 'story' AS type,
    NULL::INT AS "pitch_id",
    DATE((articles.created_at)::timestamptz AT TIME ZONE 'America/New_York') AS "created_date",
    entities.name AS "entity_name",
    entities.id AS "entity_id",
    articles.id AS "story_id",
    articles.creator_id AS "creator_id"
FROM articles
JOIN profiles  AS entities ON articles.brand_profile_id = entities.id

# models/schema.yml
version: 2

models:
  - name: articles_and_proposals
    columns:
      - name: reporting_week
      - name: type
      - name: id
        tests:
          - unique
          - not_null
      - name: entity_name
      - name: creator_id
      - name: entity_id
      - name: pitch_id
      - name: is_same_dow_as_yesterday
      - name: story_id

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 'pitch' AS type,
    proposals.id AS "pitch_id",
    DATE((proposals.created_at)::timestamptz AT TIME ZONE 'America/New_York') AS "created_date",
    entities.name AS "entity_name",
    entities.id AS "entity_id",
    proposals.story_id AS "story_id",
    proposals.creator_id AS "creator_id"
FROM proposals
JOIN profiles AS external_profiles ON proposals.brand_profile_id = entities.id
UNION ALL
SELECT 'story' AS type,
    NULL::INT AS "pitch_id",
    DATE((articles.created_at)::timestamptz AT TIME ZONE 'America/New_York') AS "created_date",
    entities.name AS "entity_name",
    entities.id AS "entity_id",
    articles.id AS "story_id",
    articles.creator_id AS "creator_id"
FROM articles
JOIN profiles  AS entities ON articles.brand_profile_id = entities.id

view: articles_and_proposals {
  derived_table: {
    sql: SELECT 'pitch' AS type,
    proposals.id AS "pitch_id",
    DATE((proposals.created_at)::timestamptz AT TIME ZONE 'America/New_York') AS "created_date",
    entities.name AS "entity_name",
    entities.id AS "entity_id",
    proposals.story_id AS "story_id",
    proposals.creator_id AS "creator_id"
FROM proposals
JOIN profiles AS external_profiles ON proposals.brand_profile_id = entities.id
UNION ALL
SELECT 'story' AS type,
    NULL::INT AS "pitch_id",
    DATE((articles.created_at)::timestamptz AT TIME ZONE 'America/New_York') AS "created_date",
    entities.name AS "entity_name",
    entities.id AS "entity_id",
    articles.id AS "story_id",
    articles.creator_id AS "creator_id"
FROM articles
JOIN profiles  AS entities ON articles.brand_profile_id = entities.id ;;
  }

  dimension: reporting_week {
    sql: TO_CHAR(
    (CASE
        WHEN EXTRACT(DOW FROM ${created_date}) <= 3
        THEN ${created_date} + (3 - EXTRACT(DOW FROM ${created_date})) * INTERVAL '1 days'
        ELSE ${created_date} + ((3 + 7) - EXTRACT(DOW FROM ${created_date})) * INTERVAL '1 days'
    END) + INTERVAL '-6 days', 'YYYY-MM-DD'
  ) ;;
  }

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

  dimension: id {
    primary_key: yes
    sql: cast(coalesce(${TABLE}.story_id,0) as varchar) || '_' || cast(coalesce(${TABLE}.pitch_id,0) as varchar) ;;
  }

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

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

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

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

  dimension: is_same_dow_as_yesterday {
    type: yesno
    sql: EXTRACT( DOW FROM DATE(${TABLE}.created_date)) = EXTRACT( DOW FROM DATE((NOW())::timestamptz AT TIME ZONE 'America/New_York')) - 1 ;;
  }

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

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

  measure: story.count_completed {
    label: "Completed Stories Count"
    type: count
    filters: [articles.completed_date: "-NULL"]
    filters: [type: "story"]
    drill_fields: [story_drill_fields*]
  }

  measure: average_days_from_pitch_to_story {
    type: average
    value_format: "0.#"
    filters: [type: "pitch"]
    sql: ${proposals.days_from_pitch_to_story} ;;
  }
}

- view: articles_and_proposals
  derived_table:
    sql: SELECT 'pitch' AS type,
    proposals.id AS "pitch_id",
    DATE((proposals.created_at)::timestamptz AT TIME ZONE 'America/New_York') AS "created_date",
    entities.name AS "entity_name",
    entities.id AS "entity_id",
    proposals.story_id AS "story_id",
    proposals.creator_id AS "creator_id"
FROM proposals
JOIN profiles AS external_profiles ON proposals.brand_profile_id = entities.id
UNION ALL
SELECT 'story' AS type,
    NULL::INT AS "pitch_id",
    DATE((articles.created_at)::timestamptz AT TIME ZONE 'America/New_York') AS "created_date",
    entities.name AS "entity_name",
    entities.id AS "entity_id",
    articles.id AS "story_id",
    articles.creator_id AS "creator_id"
FROM articles
JOIN profiles  AS entities ON articles.brand_profile_id = entities.id
  fields:
  - dimension: reporting_week
    sql: TO_CHAR(
    (CASE
        WHEN EXTRACT(DOW FROM ${created_date}) <= 3
        THEN ${created_date} + (3 - EXTRACT(DOW FROM ${created_date})) * INTERVAL '1 days'
        ELSE ${created_date} + ((3 + 7) - EXTRACT(DOW FROM ${created_date})) * INTERVAL '1 days'
    END) + INTERVAL '-6 days', 'YYYY-MM-DD'
  )
  - dimension: type
    sql: ${TABLE}.type
  - dimension: id
    primary_key: true
    sql: cast(coalesce(${TABLE}.story_id,0) as varchar) || '_' || cast(coalesce(${TABLE}.pitch_id,0) as varchar)
  - dimension: entity_name
    sql: ${TABLE}.entity_name
  - dimension: creator_id
    type: int
    sql: ${TABLE}.creator_id
  - dimension: entity_id
    type: int
    sql: ${TABLE}.entity_id
  - dimension: pitch_id
    type: int
    sql: ${TABLE}.pitch_id
  - dimension: is_same_dow_as_yesterday
    type: yesno
    sql: EXTRACT( DOW FROM DATE(${TABLE}.created_date)) = EXTRACT( DOW FROM DATE((NOW())::timestamptz AT TIME ZONE 'America/New_York')) - 1
  - dimension: story_id
    type: int
    sql: ${TABLE}.story_id
  - dimension_group: created
    type: time
    timeframes:
    - date
    - week
    - month
    - day_of_week
    - year
    convert_tz: false
    sql: ${TABLE}.created_date
  - measure: story.count_completed
    type: count
    label: Completed Stories Count
    drill_fields:
    - story_drill_fields*
    filters:
    - articles.completed_date: -NULL
    - type: story
  - measure: average_days_from_pitch_to_story
    type: average
    value_format: '0.#'
    filters:
    - type: pitch
    sql: ${proposals.days_from_pitch_to_story}