Call Refund Aggregate

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

select
  c.call_content_type_id, c.call_object_id,
  sum(mt.amount * mt.is_refund::integer) as total_refund,
  sum(mt.amount * (mt.braintree_transaction_status = 5)::integer) as total_voids,
  sum(mt.amount * (not mt.is_refund)::integer) as total_charges,
  (c.chargeable AND
  c.is_demo = False AND
  c.is_test = False AND
  (
    max(dg.group_id) is NULL OR
    max(dg.group_id) not in (1,8)) AND
    (
      count(mt.id) = 0 OR
      (
        sum(mt.amount * mt.is_refund::integer) <= 0 AND
        sum(mt.amount * (mt.braintree_transaction_status = 5)::integer) <= 0
      )
    )
  ) as is_org_chargeable
FROM combined_call as c
LEFT JOIN members_genericmember as m on c.member_id = m.id
LEFT JOIN payments_transaction as mt on c.call_object_id = mt.call_object_id and c.call_content_type_id = mt.call_content_type_id
LEFT JOIN users_user_data_groups as dg on m.user_id = dg.user_data_id
GROUP BY c.call_content_type_id, c.call_object_id, c.chargeable, c.is_demo, c.is_test, m.is_demo

# models/schema.yml
version: 2

models:
  - name: call_refund_aggregate
    columns:
      - name: call_content_type_id_and_call_object_id
        tests:
          - unique
          - not_null
      - name: total_refund
      - name: total_voids
      - name: total_charges

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
  c.call_content_type_id, c.call_object_id,
  sum(mt.amount * mt.is_refund::integer) as total_refund,
  sum(mt.amount * (mt.braintree_transaction_status = 5)::integer) as total_voids,
  sum(mt.amount * (not mt.is_refund)::integer) as total_charges,
  (c.chargeable AND
  c.is_demo = False AND
  c.is_test = False AND
  (
    max(dg.group_id) is NULL OR
    max(dg.group_id) not in (1,8)) AND
    (
      count(mt.id) = 0 OR
      (
        sum(mt.amount * mt.is_refund::integer) <= 0 AND
        sum(mt.amount * (mt.braintree_transaction_status = 5)::integer) <= 0
      )
    )
  ) as is_org_chargeable
FROM combined_call as c
LEFT JOIN members_genericmember as m on c.member_id = m.id
LEFT JOIN payments_transaction as mt on c.call_object_id = mt.call_object_id and c.call_content_type_id = mt.call_content_type_id
LEFT JOIN users_user_data_groups as dg on m.user_id = dg.user_data_id
GROUP BY c.call_content_type_id, c.call_object_id, c.chargeable, c.is_demo, c.is_test, m.is_demo

view: call_refund_aggregate {
  derived_table: {
    sql: select
  c.call_content_type_id, c.call_object_id,
  sum(mt.amount * mt.is_refund::integer) as total_refund,
  sum(mt.amount * (mt.braintree_transaction_status = 5)::integer) as total_voids,
  sum(mt.amount * (not mt.is_refund)::integer) as total_charges,
  (c.chargeable AND
  c.is_demo = False AND
  c.is_test = False AND
  (
    max(dg.group_id) is NULL OR
    max(dg.group_id) not in (1,8)) AND
    (
      count(mt.id) = 0 OR
      (
        sum(mt.amount * mt.is_refund::integer) <= 0 AND
        sum(mt.amount * (mt.braintree_transaction_status = 5)::integer) <= 0
      )
    )
  ) as is_org_chargeable
FROM combined_call as c
LEFT JOIN members_genericmember as m on c.member_id = m.id
LEFT JOIN payments_transaction as mt on c.call_object_id = mt.call_object_id and c.call_content_type_id = mt.call_content_type_id
LEFT JOIN users_user_data_groups as dg on m.user_id = dg.user_data_id
GROUP BY c.call_content_type_id, c.call_object_id, c.chargeable, c.is_demo, c.is_test, m.is_demo ;;
  }

  dimension: call_content_type_id_and_call_object_id {
    primary_key: yes
    hidden: yes
    sql: CASE WHEN ${TABLE}.call_content_type_id IS NULL THEN NULL
     ELSE CONCAT(${TABLE}.call_content_type_id::text, ':', ${TABLE}.call_object_id::text)
END ;;
  }

  dimension: total_refund {
    type: number
    sql: COALESCE(${TABLE}.total_refund, 0) ;;
  }

  dimension: total_voids {
    type: number
    sql: COALESCE(${TABLE}.total_voids, 0) ;;
  }

  dimension: total_charges {
    type: number
    sql: COALESCE(${TABLE}.total_charges, 0) ;;
  }

  measure: count {
    type: count
  }

  measure: sum_total_refund {
    type: sum
    sql: ${total_refund} ;;
  }

  measure: sum_total_voids {
    type: sum
    sql: ${total_voids} ;;
  }

  measure: sum_total_charges {
    type: sum
    sql: ${total_charges} ;;
  }
}

- view: call_refund_aggregate
  derived_table:
    sql: select
  c.call_content_type_id, c.call_object_id,
  sum(mt.amount * mt.is_refund::integer) as total_refund,
  sum(mt.amount * (mt.braintree_transaction_status = 5)::integer) as total_voids,
  sum(mt.amount * (not mt.is_refund)::integer) as total_charges,
  (c.chargeable AND
  c.is_demo = False AND
  c.is_test = False AND
  (
    max(dg.group_id) is NULL OR
    max(dg.group_id) not in (1,8)) AND
    (
      count(mt.id) = 0 OR
      (
        sum(mt.amount * mt.is_refund::integer) <= 0 AND
        sum(mt.amount * (mt.braintree_transaction_status = 5)::integer) <= 0
      )
    )
  ) as is_org_chargeable
FROM combined_call as c
LEFT JOIN members_genericmember as m on c.member_id = m.id
LEFT JOIN payments_transaction as mt on c.call_object_id = mt.call_object_id and c.call_content_type_id = mt.call_content_type_id
LEFT JOIN users_user_data_groups as dg on m.user_id = dg.user_data_id
GROUP BY c.call_content_type_id, c.call_object_id, c.chargeable, c.is_demo, c.is_test, m.is_demo
  fields:
  - dimension: call_content_type_id_and_call_object_id
    primary_key: true
    hidden: true
    sql: CASE WHEN ${TABLE}.call_content_type_id IS NULL THEN NULL
     ELSE CONCAT(${TABLE}.call_content_type_id::text, ':', ${TABLE}.call_object_id::text)
END
  - dimension: total_refund
    type: number
    sql: COALESCE(${TABLE}.total_refund, 0)
  - dimension: total_voids
    type: number
    sql: COALESCE(${TABLE}.total_voids, 0)
  - dimension: total_charges
    type: number
    sql: COALESCE(${TABLE}.total_charges, 0)
  - measure: count
    type: count
  - measure: sum_total_refund
    type: sum
    sql: ${total_refund}
  - measure: sum_total_voids
    type: sum
    sql: ${total_voids}
  - measure: sum_total_charges
    type: sum
    sql: ${total_charges}