data.ml
SQL Genomics health services

Patient Refunds

Patient Refunds model for genomics health services.

source-preserved

Blocks

patient_refunds.sql · 29 lines
inferred 
-- models/patient_refunds.sql
{{ config(materialized='view') }}

SELECT
payment.invoice_id AS invoice_id
, payment.net_refund_amount AS net_refund_amount
FROM (
  SELECT
    invoice_id
    , invoice_number
    , payment_method
    , amount
    , SUM(payment.amount) OVER(PARTITION BY payment.invoice_id) AS net_refund_amount
  FROM ${SCHEMA}.payment
  WHERE invoice_type='Customer' AND ((payment_method='ref' AND amount<0) OR (payment_method='void' AND amount>0))
  GROUP BY 1,2,3,4) AS payment
GROUP BY 1,2

# models/schema.yml
version: 2

models:
  - name: patient_refunds
    columns:
      - name: invoice_id
        tests:
          - unique
          - not_null
      - name: net_refund_amount

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.
patient_refunds.sql · 14 lines
source-preserved validation: not checked 
SELECT
payment.invoice_id AS invoice_id
, payment.net_refund_amount AS net_refund_amount
FROM (
  SELECT
    invoice_id
    , invoice_number
    , payment_method
    , amount
    , SUM(payment.amount) OVER(PARTITION BY payment.invoice_id) AS net_refund_amount
  FROM ${SCHEMA}.payment
  WHERE invoice_type='Customer' AND ((payment_method='ref' AND amount<0) OR (payment_method='void' AND amount>0))
  GROUP BY 1,2,3,4) AS payment
GROUP BY 1,2
patient_refunds.view.lkml · 46 lines
inferred 
view: patient_refunds {
  derived_table: {
    sql: SELECT
payment.invoice_id AS invoice_id
, payment.net_refund_amount AS net_refund_amount
FROM (
  SELECT
    invoice_id
    , invoice_number
    , payment_method
    , amount
    , SUM(payment.amount) OVER(PARTITION BY payment.invoice_id) AS net_refund_amount
  FROM ${SCHEMA}.payment
  WHERE invoice_type='Customer' AND ((payment_method='ref' AND amount<0) OR (payment_method='void' AND amount>0))
  GROUP BY 1,2,3,4) AS payment
GROUP BY 1,2 ;;
  }

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

  dimension: net_refund_amount {
    type: number
    hidden: yes
    sql: ${TABLE}.net_refund_amount ;;
  }

  measure: total_net_refund_amount {
    label: "Total Patient Refund Amount"
    type: sum
    value_format: "$#,###"
    drill_fields: [detail*]
    sql: ABS(${net_refund_amount}) ;;
  }

  measure: average_net_refund_amount {
    label: "Average Patient Refund Amount"
    type: average
    value_format: "$#,###"
    drill_fields: [detail*]
    sql: ABS(${net_refund_amount}) ;;
  }
}
patient_refunds.view.lookml · 39 lines
source-preserved 
- view: patient_refunds
  derived_table:
    sql: SELECT
payment.invoice_id AS invoice_id
, payment.net_refund_amount AS net_refund_amount
FROM (
  SELECT
    invoice_id
    , invoice_number
    , payment_method
    , amount
    , SUM(payment.amount) OVER(PARTITION BY payment.invoice_id) AS net_refund_amount
  FROM ${SCHEMA}.payment
  WHERE invoice_type='Customer' AND ((payment_method='ref' AND amount<0) OR (payment_method='void' AND amount>0))
  GROUP BY 1,2,3,4) AS payment
GROUP BY 1,2
  fields:
  - dimension: invoice_id
    primary_key: true
    hidden: true
    sql: ${TABLE}.invoice_id
  - dimension: net_refund_amount
    type: number
    hidden: true
    sql: ${TABLE}.net_refund_amount
  - measure: total_net_refund_amount
    type: sum
    label: Total Patient Refund Amount
    value_format: '$#,###'
    drill_fields:
    - detail*
    sql: ABS(${net_refund_amount})
  - measure: average_net_refund_amount
    type: average
    label: Average Patient Refund Amount
    value_format: '$#,###'
    drill_fields:
    - detail*
    sql: ABS(${net_refund_amount})

Contents

  • patient_refunds.sql
  • patient_refunds.sql
  • patient_refunds.view.lkml
  • patient_refunds.view.lookml