Call Membertransaction Aggregate

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

SELECT call.call_content_type_id
 , call.call_object_id
 , COUNT(distinct membertransaction.id) AS number_transactions
 , SUM(
    CASE
      WHEN membertransaction.is_refund
        THEN 0
      WHEN membertransaction.braintree_transaction_status IN (6,7,8,10)
        THEN COALESCE(membertransaction.amount, 0)
      ELSE
        0
    END) AS gross_revenue
  , SUM(
    CASE
      WHEN membertransaction.is_refund
        THEN COALESCE(membertransaction.amount, 0)
      ELSE
        0
    END) AS refunds
    , SUM(
    CASE
      WHEN membertransaction.is_refund
        THEN -1 * COALESCE(membertransaction.amount, 0)
      WHEN membertransaction.braintree_transaction_status IN (6,7,8,10)
        THEN COALESCE(membertransaction.amount, 0)
      ELSE
        0
    END) AS net_revenue
    , SUM(COALESCE(callsegment.organization_subsidy, 0) * call.segment_count) AS total_organization_subsidy
FROM combined_call AS call
LEFT JOIN calls_callsegment AS callsegment ON call.segment_id = callsegment.id
LEFT JOIN payments_transaction as membertransaction ON
  (call.call_object_id = membertransaction.call_object_id AND call.call_content_type_id = membertransaction.call_content_type_id)
GROUP BY call.call_content_type_id, call.call_object_id

# models/schema.yml
version: 2

models:
  - name: call_membertransaction_aggregate
    columns:
      - name: call_content_type_id_and_call_object_id
        tests:
          - unique
          - not_null
      - name: number_transactions
      - name: gross_revenue
      - name: refunds
      - name: net_revenue
      - name: total_organization_subsidy

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 call.call_content_type_id
 , call.call_object_id
 , COUNT(distinct membertransaction.id) AS number_transactions
 , SUM(
    CASE
      WHEN membertransaction.is_refund
        THEN 0
      WHEN membertransaction.braintree_transaction_status IN (6,7,8,10)
        THEN COALESCE(membertransaction.amount, 0)
      ELSE
        0
    END) AS gross_revenue
  , SUM(
    CASE
      WHEN membertransaction.is_refund
        THEN COALESCE(membertransaction.amount, 0)
      ELSE
        0
    END) AS refunds
    , SUM(
    CASE
      WHEN membertransaction.is_refund
        THEN -1 * COALESCE(membertransaction.amount, 0)
      WHEN membertransaction.braintree_transaction_status IN (6,7,8,10)
        THEN COALESCE(membertransaction.amount, 0)
      ELSE
        0
    END) AS net_revenue
    , SUM(COALESCE(callsegment.organization_subsidy, 0) * call.segment_count) AS total_organization_subsidy
FROM combined_call AS call
LEFT JOIN calls_callsegment AS callsegment ON call.segment_id = callsegment.id
LEFT JOIN payments_transaction as membertransaction ON
  (call.call_object_id = membertransaction.call_object_id AND call.call_content_type_id = membertransaction.call_content_type_id)
GROUP BY call.call_content_type_id, call.call_object_id

view: call_membertransaction_aggregate {
  derived_table: {
    sql: SELECT call.call_content_type_id
 , call.call_object_id
 , COUNT(distinct membertransaction.id) AS number_transactions
 , SUM(
    CASE
      WHEN membertransaction.is_refund
        THEN 0
      WHEN membertransaction.braintree_transaction_status IN (6,7,8,10)
        THEN COALESCE(membertransaction.amount, 0)
      ELSE
        0
    END) AS gross_revenue
  , SUM(
    CASE
      WHEN membertransaction.is_refund
        THEN COALESCE(membertransaction.amount, 0)
      ELSE
        0
    END) AS refunds
    , SUM(
    CASE
      WHEN membertransaction.is_refund
        THEN -1 * COALESCE(membertransaction.amount, 0)
      WHEN membertransaction.braintree_transaction_status IN (6,7,8,10)
        THEN COALESCE(membertransaction.amount, 0)
      ELSE
        0
    END) AS net_revenue
    , SUM(COALESCE(callsegment.organization_subsidy, 0) * call.segment_count) AS total_organization_subsidy
FROM combined_call AS call
LEFT JOIN calls_callsegment AS callsegment ON call.segment_id = callsegment.id
LEFT JOIN payments_transaction as membertransaction ON
  (call.call_object_id = membertransaction.call_object_id AND call.call_content_type_id = membertransaction.call_content_type_id)
GROUP BY call.call_content_type_id, call.call_object_id ;;
  }

  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: number_transactions {
    type: number
    sql: ${TABLE}.number_transactions ;;
  }

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

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

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

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

  measure: sum_organization_subsidy {
    type: sum
    sql: ${total_organization_subsidy} ;;
  }

  measure: count {
    type: count
  }

  measure: sum_gross_revenue {
    type: sum
    sql: ${gross_revenue} ;;
  }

  measure: sum_refunds {
    type: sum
    sql: ${refunds} ;;
  }

  measure: sum_net_revenue {
    type: sum
    sql: ${net_revenue} ;;
  }
}

- view: call_membertransaction_aggregate
  derived_table:
    sql: SELECT call.call_content_type_id
 , call.call_object_id
 , COUNT(distinct membertransaction.id) AS number_transactions
 , SUM(
    CASE
      WHEN membertransaction.is_refund
        THEN 0
      WHEN membertransaction.braintree_transaction_status IN (6,7,8,10)
        THEN COALESCE(membertransaction.amount, 0)
      ELSE
        0
    END) AS gross_revenue
  , SUM(
    CASE
      WHEN membertransaction.is_refund
        THEN COALESCE(membertransaction.amount, 0)
      ELSE
        0
    END) AS refunds
    , SUM(
    CASE
      WHEN membertransaction.is_refund
        THEN -1 * COALESCE(membertransaction.amount, 0)
      WHEN membertransaction.braintree_transaction_status IN (6,7,8,10)
        THEN COALESCE(membertransaction.amount, 0)
      ELSE
        0
    END) AS net_revenue
    , SUM(COALESCE(callsegment.organization_subsidy, 0) * call.segment_count) AS total_organization_subsidy
FROM combined_call AS call
LEFT JOIN calls_callsegment AS callsegment ON call.segment_id = callsegment.id
LEFT JOIN payments_transaction as membertransaction ON
  (call.call_object_id = membertransaction.call_object_id AND call.call_content_type_id = membertransaction.call_content_type_id)
GROUP BY call.call_content_type_id, call.call_object_id
  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: number_transactions
    type: number
    sql: ${TABLE}.number_transactions
  - dimension: gross_revenue
    type: number
    sql: ${TABLE}.gross_revenue
  - dimension: refunds
    type: number
    sql: ${TABLE}.refunds
  - dimension: net_revenue
    type: number
    sql: ${TABLE}.net_revenue
  - dimension: total_organization_subsidy
    type: number
    sql: ${TABLE}.total_organization_subsidy
  - measure: sum_organization_subsidy
    type: sum
    sql: ${total_organization_subsidy}
  - measure: count
    type: count
  - measure: sum_gross_revenue
    type: sum
    sql: ${gross_revenue}
  - measure: sum_refunds
    type: sum
    sql: ${refunds}
  - measure: sum_net_revenue
    type: sum
    sql: ${net_revenue}