data.ml
SQL Mobile ticketing

Order Refund Fperformances

Order Refund Fperformances model for mobile ticketing.

source-preserved

Blocks

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

select
  rrd.LineID AS order_line_id
  , MAX(rr.ProcessDate) as process_date
  , rr.currencyid as currency_id
  , rr.orderid as order_id
  , SUM(CASE
    WHEN
      rr.StatusId = 8
    THEN rrd.RefundAmount
  ELSE 0
  END) as refund_amount
  , SUM(CASE
    WHEN
      rrd.ValueTypeId =  2
      AND (rrd.ProductType =  'T' OR rrd.ProductType =  'M')
      AND rr.StatusId = 8
    THEN rrd.RefundAmount
  ELSE 0
  END) as booking_fee_refund_amount
  , SUM(CASE
    WHEN
      rrd.ValueTypeId =  1
      AND rrd.ProductType =  'T'
      AND rr.StatusId = 8
    THEN rrd.RefundAmount
  ELSE 0
  END) as face_value_refund_amount
  , SUM(CASE
    WHEN
      rrd.ValueTypeId =  1
      AND rrd.ProductType =  'M'
      AND rr.StatusId = 8
    THEN rrd.RefundAmount
  ELSE 0
  END) as merchandise_face_value_refund_amount
  , SUM(CASE
    WHEN
      rrd.ValueTypeId =  3
      AND rrd.ProductType =  'T'
      AND rr.StatusId = 8
    THEN rrd.RefundAmount
  ELSE 0
  END) as commission_back_refund_amount
  , SUM(CASE
    WHEN
      rrd.ValueTypeId =  4
      AND rrd.ProductType =  'T'
      AND rr.StatusId = 8
    THEN rrd.RefundAmount
  ELSE 0
  END) as performer_commission_back_refund_amount
  , SUM(CASE
    WHEN
      rrd.ValueTypeId =  5
      AND rrd.ProductType =  'S'
      AND rr.StatusId = 8
    THEN rrd.RefundAmount
  ELSE 0
  END) as shipment_refund_amount
  , SUM(CASE
    WHEN
      rrd.ValueTypeId =  6
      AND rrd.ProductType =  'T'
      AND rr.StatusId = 8
    THEN rrd.RefundAmount
  ELSE 0
  END) as unknown_refund_amount,
  MAX(IF(TIMESTAMPDIFF(minute,rr.CreateDate,rr.ProcessDate) <= 5, 1, 0)) as auto_approved
  FROM refund_requests  rr
  LEFT JOIN refund_details  rrd
  on rr.id = rrd.RefundRequestID
GROUP BY rrd.LineID

# models/schema.yml
version: 2

models:
  - name: order_refund_fperformances
    columns:
      - name: order_line_id
        tests:
          - unique
          - not_null
      - name: order_id
      - name: auto_approved
      - name: refund_amount
        description: "Total Amount Refunded in the Transaction Currency"
      - name: face_value_refund_amount
        description: "Face Value Refund Amount in the Transaction Currency"
      - name: merchandise_face_value_refund_amount
        description: "Merch Face Value Refund Amount in the Transaction Currency"
      - name: booking_fee_refund_amount
        description: "Booking Fee Refund Amount in the Transaction Currency"
      - name: commission_back_refund_amount
        description: "Earn Back Refund Amount in the Transaction Currency"
      - name: performer_commission_back_refund_amount
        description: "Artist Refund Amount in the Transaction Currency"
      - name: shipment_refund_amount
        description: "Shipment Refund Amount in the Transaction Currency"
      - name: unknown_refund_amount
        description: "Unknown Refund Amount in the Transaction Currency"
      - name: currency_id
        description: "Currency ID that the refund was processed in"

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.
order_refund_fperformances.sql · 72 lines
source-preserved validation: parses 
select
  rrd.LineID AS order_line_id
  , MAX(rr.ProcessDate) as process_date
  , rr.currencyid as currency_id
  , rr.orderid as order_id
  , SUM(CASE
    WHEN
      rr.StatusId = 8
    THEN rrd.RefundAmount
  ELSE 0
  END) as refund_amount
  , SUM(CASE
    WHEN
      rrd.ValueTypeId =  2
      AND (rrd.ProductType =  'T' OR rrd.ProductType =  'M')
      AND rr.StatusId = 8
    THEN rrd.RefundAmount
  ELSE 0
  END) as booking_fee_refund_amount
  , SUM(CASE
    WHEN
      rrd.ValueTypeId =  1
      AND rrd.ProductType =  'T'
      AND rr.StatusId = 8
    THEN rrd.RefundAmount
  ELSE 0
  END) as face_value_refund_amount
  , SUM(CASE
    WHEN
      rrd.ValueTypeId =  1
      AND rrd.ProductType =  'M'
      AND rr.StatusId = 8
    THEN rrd.RefundAmount
  ELSE 0
  END) as merchandise_face_value_refund_amount
  , SUM(CASE
    WHEN
      rrd.ValueTypeId =  3
      AND rrd.ProductType =  'T'
      AND rr.StatusId = 8
    THEN rrd.RefundAmount
  ELSE 0
  END) as commission_back_refund_amount
  , SUM(CASE
    WHEN
      rrd.ValueTypeId =  4
      AND rrd.ProductType =  'T'
      AND rr.StatusId = 8
    THEN rrd.RefundAmount
  ELSE 0
  END) as performer_commission_back_refund_amount
  , SUM(CASE
    WHEN
      rrd.ValueTypeId =  5
      AND rrd.ProductType =  'S'
      AND rr.StatusId = 8
    THEN rrd.RefundAmount
  ELSE 0
  END) as shipment_refund_amount
  , SUM(CASE
    WHEN
      rrd.ValueTypeId =  6
      AND rrd.ProductType =  'T'
      AND rr.StatusId = 8
    THEN rrd.RefundAmount
  ELSE 0
  END) as unknown_refund_amount,
  MAX(IF(TIMESTAMPDIFF(minute,rr.CreateDate,rr.ProcessDate) <= 5, 1, 0)) as auto_approved
  FROM refund_requests  rr
  LEFT JOIN refund_details  rrd
  on rr.id = rrd.RefundRequestID
GROUP BY rrd.LineID
order_refund_fperformances.view.lkml · 174 lines
inferred 
view: order_refund_fperformances {
  derived_table: {
    sql: select
  rrd.LineID AS order_line_id
  , MAX(rr.ProcessDate) as process_date
  , rr.currencyid as currency_id
  , rr.orderid as order_id
  , SUM(CASE
    WHEN
      rr.StatusId = 8
    THEN rrd.RefundAmount
  ELSE 0
  END) as refund_amount
  , SUM(CASE
    WHEN
      rrd.ValueTypeId =  2
      AND (rrd.ProductType =  'T' OR rrd.ProductType =  'M')
      AND rr.StatusId = 8
    THEN rrd.RefundAmount
  ELSE 0
  END) as booking_fee_refund_amount
  , SUM(CASE
    WHEN
      rrd.ValueTypeId =  1
      AND rrd.ProductType =  'T'
      AND rr.StatusId = 8
    THEN rrd.RefundAmount
  ELSE 0
  END) as face_value_refund_amount
  , SUM(CASE
    WHEN
      rrd.ValueTypeId =  1
      AND rrd.ProductType =  'M'
      AND rr.StatusId = 8
    THEN rrd.RefundAmount
  ELSE 0
  END) as merchandise_face_value_refund_amount
  , SUM(CASE
    WHEN
      rrd.ValueTypeId =  3
      AND rrd.ProductType =  'T'
      AND rr.StatusId = 8
    THEN rrd.RefundAmount
  ELSE 0
  END) as commission_back_refund_amount
  , SUM(CASE
    WHEN
      rrd.ValueTypeId =  4
      AND rrd.ProductType =  'T'
      AND rr.StatusId = 8
    THEN rrd.RefundAmount
  ELSE 0
  END) as performer_commission_back_refund_amount
  , SUM(CASE
    WHEN
      rrd.ValueTypeId =  5
      AND rrd.ProductType =  'S'
      AND rr.StatusId = 8
    THEN rrd.RefundAmount
  ELSE 0
  END) as shipment_refund_amount
  , SUM(CASE
    WHEN
      rrd.ValueTypeId =  6
      AND rrd.ProductType =  'T'
      AND rr.StatusId = 8
    THEN rrd.RefundAmount
  ELSE 0
  END) as unknown_refund_amount,
  MAX(IF(TIMESTAMPDIFF(minute,rr.CreateDate,rr.ProcessDate) <= 5, 1, 0)) as auto_approved
  FROM refund_requests  rr
  LEFT JOIN refund_details  rrd
  on rr.id = rrd.RefundRequestID
GROUP BY rrd.LineID ;;
  }

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

  dimension: order_id {
    hidden: yes
    sql: ${TABLE}.order_id ;;
  }

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

  dimension: refund_amount {
    label: "Total Amount Refunded"
    description: "Total Amount Refunded in the Transaction Currency"
    type: number
    hidden: yes
    sql: ${face_value_refund_amount} + ${merchandise_face_value_refund_amount} + ${booking_fee_refund_amount} + ${commission_back_refund_amount} + ${performer_commission_back_refund_amount} ;;
  }

  dimension: face_value_refund_amount {
    description: "Face Value Refund Amount in the Transaction Currency"
    type: number
    hidden: yes
    sql: ${TABLE}.face_value_refund_amount ;;
  }

  dimension: merchandise_face_value_refund_amount {
    description: "Merch Face Value Refund Amount in the Transaction Currency"
    type: number
    hidden: yes
    sql: ${TABLE}.merchandise_face_value_refund_amount ;;
  }

  dimension: booking_fee_refund_amount {
    description: "Booking Fee Refund Amount in the Transaction Currency"
    type: number
    hidden: yes
    sql: ${TABLE}.booking_fee_refund_amount ;;
  }

  dimension: commission_back_refund_amount {
    description: "Earn Back Refund Amount in the Transaction Currency"
    type: number
    hidden: yes
    sql: ${TABLE}.commission_back_refund_amount ;;
  }

  dimension: performer_commission_back_refund_amount {
    description: "Artist Refund Amount in the Transaction Currency"
    type: number
    hidden: yes
    sql: ${TABLE}.performer_commission_back_refund_amount ;;
  }

  dimension: shipment_refund_amount {
    description: "Shipment Refund Amount in the Transaction Currency"
    type: number
    hidden: yes
    sql: ${TABLE}.shipment_refund_amount ;;
  }

  dimension: unknown_refund_amount {
    description: "Unknown Refund Amount in the Transaction Currency"
    type: number
    hidden: yes
    sql: ${TABLE}.unknown_refund_amount ;;
  }

  dimension: currency_id {
    description: "Currency ID that the refund was processed in"
    sql: ${TABLE}.currency_id ;;
  }

  dimension_group: return_process_date {
    label: "Latest Refund Date"
    description: "Date of the most refund processed"
    type: time
    timeframes: [date, week, month]
    sql: ${TABLE}.process_date ;;
  }

  measure: refund_order_count {
    description: "This is the number of customer_purchases that had at least 1 refund"
    type: count_distinct
    sql: ${order_id} ;;
  }

  measure: refund_item_count {
    description: "This is the number of items refunded"
    type: count_distinct
    sql: ${order_line_id} ;;
  }
}
order_refund_fperformances.view.lookml · 144 lines
source-preserved 
- view: order_refund_fperformances
  derived_table:
    sql: select
  rrd.LineID AS order_line_id
  , MAX(rr.ProcessDate) as process_date
  , rr.currencyid as currency_id
  , rr.orderid as order_id
  , SUM(CASE
    WHEN
      rr.StatusId = 8
    THEN rrd.RefundAmount
  ELSE 0
  END) as refund_amount
  , SUM(CASE
    WHEN
      rrd.ValueTypeId =  2
      AND (rrd.ProductType =  'T' OR rrd.ProductType =  'M')
      AND rr.StatusId = 8
    THEN rrd.RefundAmount
  ELSE 0
  END) as booking_fee_refund_amount
  , SUM(CASE
    WHEN
      rrd.ValueTypeId =  1
      AND rrd.ProductType =  'T'
      AND rr.StatusId = 8
    THEN rrd.RefundAmount
  ELSE 0
  END) as face_value_refund_amount
  , SUM(CASE
    WHEN
      rrd.ValueTypeId =  1
      AND rrd.ProductType =  'M'
      AND rr.StatusId = 8
    THEN rrd.RefundAmount
  ELSE 0
  END) as merchandise_face_value_refund_amount
  , SUM(CASE
    WHEN
      rrd.ValueTypeId =  3
      AND rrd.ProductType =  'T'
      AND rr.StatusId = 8
    THEN rrd.RefundAmount
  ELSE 0
  END) as commission_back_refund_amount
  , SUM(CASE
    WHEN
      rrd.ValueTypeId =  4
      AND rrd.ProductType =  'T'
      AND rr.StatusId = 8
    THEN rrd.RefundAmount
  ELSE 0
  END) as performer_commission_back_refund_amount
  , SUM(CASE
    WHEN
      rrd.ValueTypeId =  5
      AND rrd.ProductType =  'S'
      AND rr.StatusId = 8
    THEN rrd.RefundAmount
  ELSE 0
  END) as shipment_refund_amount
  , SUM(CASE
    WHEN
      rrd.ValueTypeId =  6
      AND rrd.ProductType =  'T'
      AND rr.StatusId = 8
    THEN rrd.RefundAmount
  ELSE 0
  END) as unknown_refund_amount,
  MAX(IF(TIMESTAMPDIFF(minute,rr.CreateDate,rr.ProcessDate) <= 5, 1, 0)) as auto_approved
  FROM refund_requests  rr
  LEFT JOIN refund_details  rrd
  on rr.id = rrd.RefundRequestID
GROUP BY rrd.LineID
  fields:
  - dimension: order_line_id
    primary_key: true
    hidden: true
    sql: ${TABLE}.order_line_id
  - dimension: order_id
    hidden: true
    sql: ${TABLE}.order_id
  - dimension: auto_approved
    type: yesno
    sql: ${TABLE}.auto_approved
  - dimension: refund_amount
    type: number
    hidden: true
    label: Total Amount Refunded
    description: Total Amount Refunded in the Transaction Currency
    sql: ${face_value_refund_amount} + ${merchandise_face_value_refund_amount} + ${booking_fee_refund_amount} + ${commission_back_refund_amount} + ${performer_commission_back_refund_amount}
  - dimension: face_value_refund_amount
    type: number
    hidden: true
    description: Face Value Refund Amount in the Transaction Currency
    sql: ${TABLE}.face_value_refund_amount
  - dimension: merchandise_face_value_refund_amount
    type: number
    hidden: true
    description: Merch Face Value Refund Amount in the Transaction Currency
    sql: ${TABLE}.merchandise_face_value_refund_amount
  - dimension: booking_fee_refund_amount
    type: number
    hidden: true
    description: Booking Fee Refund Amount in the Transaction Currency
    sql: ${TABLE}.booking_fee_refund_amount
  - dimension: commission_back_refund_amount
    type: number
    hidden: true
    description: Earn Back Refund Amount in the Transaction Currency
    sql: ${TABLE}.commission_back_refund_amount
  - dimension: performer_commission_back_refund_amount
    type: number
    hidden: true
    description: Artist Refund Amount in the Transaction Currency
    sql: ${TABLE}.performer_commission_back_refund_amount
  - dimension: shipment_refund_amount
    type: number
    hidden: true
    description: Shipment Refund Amount in the Transaction Currency
    sql: ${TABLE}.shipment_refund_amount
  - dimension: unknown_refund_amount
    type: number
    hidden: true
    description: Unknown Refund Amount in the Transaction Currency
    sql: ${TABLE}.unknown_refund_amount
  - dimension: currency_id
    description: Currency ID that the refund was processed in
    sql: ${TABLE}.currency_id
  - dimension_group: return_process_date
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.process_date
  - measure: refund_order_count
    type: count_distinct
    description: This is the number of customer_purchases that had at least 1 refund
    sql: ${order_id}
  - measure: refund_item_count
    type: count_distinct
    description: This is the number of items refunded
    sql: ${order_line_id}

Contents

  • order_refund_fperformances.sql
  • order_refund_fperformances.sql
  • order_refund_fperformances.view.lkml
  • order_refund_fperformances.view.lookml