Recurring Subscription

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

SELECT *
FROM recurring_donation_table rd

-- Month aggregates from the recurring_donation_tableevent table (the group by month is crucial)
LEFT JOIN
  (SELECT
    recurring_donation_table recurring_donation_table_
    , COUNT(months) months_subscribed
    , DATE_TRUNC('month', MAX(months)) max_month
  FROM
    (SELECT
      recurring_donation_table
      , DATE_TRUNC('month', createdate) months
    FROM recurring_donation_tableevent
    GROUP BY 1,2) rde_months
  GROUP BY 1) rde_months ON rde_months.recurring_donation_table_ = rd.id

-- Stop date calculation
LEFT JOIN
  (SELECT
    rd.id id_
    , rde.max_date stop_date
  FROM recurring_donation_table rd
  LEFT JOIN
    (SELECT
      recurring_donation_table
      , MAX(createdate) max_date
    FROM recurring_donation_tableevent
    GROUP BY 1) rde ON rd.id = rde.recurring_donation_table
  WHERE rd.active IS FALSE) rde_stopdate ON rde_stopdate.id_ = rd.id

# models/schema.yml
version: 2

models:
  - name: recurring_subscription
    columns:
      - name: id
        tests:
          - unique
          - not_null
      - name: donorid
      - name: start_fy
      - name: stop
      - name: stop_fy
      - name: number_of_months
      - name: active
      - name: payment_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.

SELECT *
FROM recurring_donation_table rd

-- Month aggregates from the recurring_donation_tableevent table (the group by month is crucial)
LEFT JOIN
  (SELECT
    recurring_donation_table recurring_donation_table_
    , COUNT(months) months_subscribed
    , DATE_TRUNC('month', MAX(months)) max_month
  FROM
    (SELECT
      recurring_donation_table
      , DATE_TRUNC('month', createdate) months
    FROM recurring_donation_tableevent
    GROUP BY 1,2) rde_months
  GROUP BY 1) rde_months ON rde_months.recurring_donation_table_ = rd.id

-- Stop date calculation
LEFT JOIN
  (SELECT
    rd.id id_
    , rde.max_date stop_date
  FROM recurring_donation_table rd
  LEFT JOIN
    (SELECT
      recurring_donation_table
      , MAX(createdate) max_date
    FROM recurring_donation_tableevent
    GROUP BY 1) rde ON rd.id = rde.recurring_donation_table
  WHERE rd.active IS FALSE) rde_stopdate ON rde_stopdate.id_ = rd.id

view: recurring_subscription {
  derived_table: {
    sql: SELECT *
FROM recurring_donation_table rd

-- Month aggregates from the recurring_donation_tableevent table (the group by month is crucial)
LEFT JOIN
  (SELECT
    recurring_donation_table recurring_donation_table_
    , COUNT(months) months_subscribed
    , DATE_TRUNC('month', MAX(months)) max_month
  FROM
    (SELECT
      recurring_donation_table
      , DATE_TRUNC('month', createdate) months
    FROM recurring_donation_tableevent
    GROUP BY 1,2) rde_months
  GROUP BY 1) rde_months ON rde_months.recurring_donation_table_ = rd.id

-- Stop date calculation
LEFT JOIN
  (SELECT
    rd.id id_
    , rde.max_date stop_date
  FROM recurring_donation_table rd
  LEFT JOIN
    (SELECT
      recurring_donation_table
      , MAX(createdate) max_date
    FROM recurring_donation_tableevent
    GROUP BY 1) rde ON rd.id = rde.recurring_donation_table
  WHERE rd.active IS FALSE) rde_stopdate ON rde_stopdate.id_ = rd.id ;;
  }

  dimension: id {
    label: "ID"
    type: int
    primary_key: yes
  }

  dimension: donorid {
    label: "DONOR ID"
    type: int
  }

  dimension: start_fy {
    label: "Start FY"
    hidden: yes
    sql: ('FY' || to_char(fiscal_year_start(${TABLE}.createdate) + interval '1 year', 'YY')) ;;
  }

  dimension: stop {
    type: time
    sql: ${TABLE}.stop_date ;;
  }

  dimension: stop_fy {
    label: "Stop FY"
    hidden: yes
    sql: ('FY' || to_char(fiscal_year_start(${TABLE}.stop_date) + interval '1 year', 'YY')) ;;
  }

  dimension: number_of_months {
    type: int
    sql: (CASE WHEN ${active} AND (DATE_TRUNC('month', current_date) != ${TABLE}.max_month) THEN ${TABLE}.months_subscribed + 1 ELSE ${TABLE}.months_subscribed END) ;;
  }

  dimension: active {
    type: yesno
  }

  dimension: payment_type {
    sql: (CASE WHEN ${TABLE}.cctype IS NOT NULL THEN ${TABLE}.paymenttype || ' (' || ${TABLE}.cctype || ')' ELSE ${TABLE}.paymenttype END) ;;
  }

  dimension_group: start {
    type: time
    timeframes: [date]
    sql: ${TABLE}.createdate ;;
  }

  measure: payment.total_amount {
    type: sum
    sql: ${payment.amount} ;;
  }
}

- view: recurring_subscription
  derived_table:
    sql: SELECT *
FROM recurring_donation_table rd

-- Month aggregates from the recurring_donation_tableevent table (the group by month is crucial)
LEFT JOIN
  (SELECT
    recurring_donation_table recurring_donation_table_
    , COUNT(months) months_subscribed
    , DATE_TRUNC('month', MAX(months)) max_month
  FROM
    (SELECT
      recurring_donation_table
      , DATE_TRUNC('month', createdate) months
    FROM recurring_donation_tableevent
    GROUP BY 1,2) rde_months
  GROUP BY 1) rde_months ON rde_months.recurring_donation_table_ = rd.id

-- Stop date calculation
LEFT JOIN
  (SELECT
    rd.id id_
    , rde.max_date stop_date
  FROM recurring_donation_table rd
  LEFT JOIN
    (SELECT
      recurring_donation_table
      , MAX(createdate) max_date
    FROM recurring_donation_tableevent
    GROUP BY 1) rde ON rd.id = rde.recurring_donation_table
  WHERE rd.active IS FALSE) rde_stopdate ON rde_stopdate.id_ = rd.id
  fields:
  - dimension: id
    type: int
    primary_key: true
    label: ID
  - dimension: donorid
    type: int
    label: DONOR ID
  - dimension: start_fy
    hidden: true
    label: Start FY
    sql: ('FY' || to_char(fiscal_year_start(${TABLE}.createdate) + interval '1 year', 'YY'))
  - dimension: stop
    type: time
    sql: ${TABLE}.stop_date
  - dimension: stop_fy
    hidden: true
    label: Stop FY
    sql: ('FY' || to_char(fiscal_year_start(${TABLE}.stop_date) + interval '1 year', 'YY'))
  - dimension: number_of_months
    type: int
    sql: (CASE WHEN ${active} AND (DATE_TRUNC('month', current_date) != ${TABLE}.max_month) THEN ${TABLE}.months_subscribed + 1 ELSE ${TABLE}.months_subscribed END)
  - dimension: active
    type: yesno
  - dimension: payment_type
    sql: (CASE WHEN ${TABLE}.cctype IS NOT NULL THEN ${TABLE}.paymenttype || ' (' || ${TABLE}.cctype || ')' ELSE ${TABLE}.paymenttype END)
  - dimension_group: start
    type: time
    timeframes:
    - date
    sql: ${TABLE}.createdate
  - measure: payment.total_amount
    type: sum
    sql: ${payment.amount}