Loans

view: loans {
  sql_table_name: demo_company.sample_original ;;

  dimension: id_loan {
    label: "Loan ID"
    primary_key: yes
    sql: ${TABLE}.id_loan ;;
  }

  dimension: cd_msa {
    label: "Cbsa Code"
    sql: ${TABLE}.cd_msa ;;
  }

  dimension: vintage {
    type: int
    sql: ${dt_first_pi_year} ;;
  }

  dimension: fico {
    label: "Origination FICO"
    type: number
    sql: ${TABLE}.fico ;;
  }

  dimension: fico_tier {
    type: tier
    sql: ${fico} ;;
  }

  dimension: fico_more_than_700 {
    type: yesno
    sql: ${fico} > 700 ;;
  }

  dimension: int_rt {
    label: "Origination Coupon"
    type: number
    sql: ${TABLE}.int_rt ;;
  }

  dimension: prop_type {
    label: "Property Type"
    sql: ${TABLE}.prop_type ;;
  }

  dimension: servicer_name {
    label: "Servicer Name"
    sql: ${TABLE}.servicer_name ;;
  }

  dimension: zipcode {
    label: "Zip Code"
    type: zipcode
    sql: ${TABLE}.zipcode ;;
  }

  dimension: ltv {
    label: "Origination LTV"
    type: number
    sql: ${TABLE}.ltv ;;
  }

  dimension: orig_upb {
    label: "Original Balance (upb)"
    type: number
    sql: ${TABLE}.orig_upb ;;
  }

  dimension: channel {
    hidden: yes
  }

  dimension: cnt_borr {
    label: "Number of Borrowers"
    type: number
    hidden: yes
    sql: ${TABLE}.cnt_borr ;;
  }

  dimension: cnt_units {
    label: "Number of Property Units"
    type: number
    hidden: yes
    sql: ${TABLE}.cnt_units ;;
  }

  dimension: dti_over_65 {
    label: "Is Debt-to-Income over 65% (dti)"
    type: yesno
    hidden: yes
    sql: (${TABLE}.dti = '   ') ;;
  }

  dimension: total_ending_upb {
    label: "Total Balance at Prepay or FC"
    type: number
    hidden: yes
    sql: CASE
  WHEN (${last_reporting.is_prepaid} OR ${last_reporting.is_foreclosure}) THEN ${last_reporting.act_endg_upb}
  ELSE NULL
END ;;
  }

  dimension: fico_over_700 {
    label: "fico is over 700"
    type: yesno
    hidden: yes
    sql: ${fico} >= 700 ;;
  }

  dimension: fico_tiered_100 {
    label: "fico tiered (by 100, 6 bins)"
    type: tier
    hidden: yes
    sql: ${fico} ;;
  }

  dimension: fico_tiered_50 {
    label: "fico tiered (by 50, 12 bins)"
    type: tier
    hidden: yes
    sql: ${fico} ;;
  }

  dimension: bad_fico {
    label: "fico is below 380"
    type: yesno
    hidden: yes
    sql: ${fico} < 380 ;;
  }

  dimension: flag_fthb {
    label: "First Time Homebuyer (fthb)"
    type: yesno
    hidden: yes
    sql: ${TABLE}.flag_fthb ;;
  }

  dimension: loan_purpose {
  }

  dimension: ltv_tier {
    type: tier
    hidden: yes
    sql: ${ltv} ;;
  }

  dimension: mi_pct {
    label: "Mortgage Insurance Percentage (pct)"
    type: number
    hidden: yes
    sql: ${TABLE}.mi_pct ;;
  }

  dimension: occpy_sts {
    label: "Occupancy Status"
    hidden: yes
  }

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

  dimension: orig_property_value {
    type: number
    hidden: yes
    sql: 1.0 * ${orig_upb} / ${ltv} ;;
  }

  dimension: amount_upb_tier {
    type: tier
    hidden: yes
    sql: ${orig_upb} ;;
  }

  dimension: ppmt_pnlty {
    type: yesno
    hidden: yes
    sql: ${TABLE}.ppmt_pnlty ;;
  }

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

  dimension: st {
    label: "State"
    hidden: yes
    sql: ${TABLE}.st ;;
  }

  dimension: dti {
    label: "Original Debt-to-Income (dti)"
    type: number
    hidden: yes
    sql: ${TABLE}.dti ;;
  }

  dimension_group: dt_first_pi {
    label: "Vintage"
    type: time
    timeframes: [year, month, month_num]
    sql: ${TABLE}.dt_first_pi ;;
  }

  dimension_group: dt_matr {
    label: "Loan Maturity (dt_matr)"
    type: time
    timeframes: [year, month]
    sql: trunc(dateadd(day,1,TO_DATE(${TABLE}.dt_matr,'YYYYMM'))) ;;
  }

  measure: count_fico_more_than_700 {
    type: count
    filters: [fico_more_than_700: "True"]
  }

  measure: count {
    label: "Loan Count"
    type: count
  }

  measure: total_orig_upb {
    label: "Total Balance"
    type: sum
    sql: ${orig_upb} ;;
  }

  measure: total_orig_upb_700 {
    label: "Total Balance > 700"
    type: sum
    filters: [fico_more_than_700: "True"]
    sql: ${orig_upb} ;;
  }

  measure: int_times_upb {
    type: sum
    hidden: yes
    sql: 1.0 * ${int_rt} * ${orig_upb} ;;
  }

  measure: wa_int_rate {
    label: "WA Coupon"
    type: number
    sql: ${int_times_upb} / ${total_orig_upb} ;;
  }

  measure: average_orig_upb {
    label: "Average Balance"
    type: avg
    sql: ${orig_upb} ;;
  }

  measure: count_bad_ficos {
    type: count
    hidden: yes
    filters: [bad_fico: "True"]
  }

  measure: percent_bad_ficos {
    type: number
    hidden: yes
    sql: 100.0 * ${count_bad_ficos}/NULLIF(${count},0) ;;
  }

  measure: average_int_rt {
    type: avg
    sql: ${int_rt} ;;
  }

  measure: average_fico {
    type: avg
    hidden: yes
    sql: ${fico} ;;
  }

  measure: average_ltv {
    type: avg
    hidden: yes
    sql: ${ltv} ;;
  }

  measure: average_dti {
    label: "Average Original Debt-to-Income (DTI) Ratio"
    type: avg
    hidden: yes
    sql: ${dti} ;;
  }

  measure: count_percent_of_previous {
    type: percent_of_previous
    hidden: yes
    sql: ${count} ;;
  }

  measure: total_amount_percent_of_previous {
    type: percent_of_previous
    hidden: yes
    sql: ${total_orig_upb} ;;
  }
}

- view: loans
  sql_table_name: demo_company.sample_original
  fields:
  - dimension: id_loan
    primary_key: true
    label: Loan ID
    sql: ${TABLE}.id_loan
  - dimension: cd_msa
    label: Cbsa Code
    sql: ${TABLE}.cd_msa
  - dimension: vintage
    type: int
    sql: ${dt_first_pi_year}
  - dimension: fico
    type: number
    label: Origination FICO
    sql: ${TABLE}.fico
  - dimension: fico_tier
    type: tier
    sql: ${fico}
  - dimension: fico_more_than_700
    type: yesno
    sql: ${fico} > 700
  - dimension: int_rt
    type: number
    label: Origination Coupon
    sql: ${TABLE}.int_rt
  - dimension: prop_type
    label: Property Type
    sql: ${TABLE}.prop_type
  - dimension: servicer_name
    label: Servicer Name
    sql: ${TABLE}.servicer_name
  - dimension: zipcode
    type: zipcode
    label: Zip Code
    sql: ${TABLE}.zipcode
  - dimension: ltv
    type: number
    label: Origination LTV
    sql: ${TABLE}.ltv
  - dimension: orig_upb
    type: number
    label: Original Balance (upb)
    sql: ${TABLE}.orig_upb
  - dimension: channel
    hidden: true
  - dimension: cnt_borr
    type: number
    hidden: true
    label: Number of Borrowers
    sql: ${TABLE}.cnt_borr
  - dimension: cnt_units
    type: number
    hidden: true
    label: Number of Property Units
    sql: ${TABLE}.cnt_units
  - dimension: dti_over_65
    type: yesno
    hidden: true
    label: 'Is Debt-to-Income over 65% (dti)'
    sql: (${TABLE}.dti = '   ')
  - dimension: total_ending_upb
    type: number
    hidden: true
    label: Total Balance at Prepay or FC
    sql: CASE
  WHEN (${last_reporting.is_prepaid} OR ${last_reporting.is_foreclosure}) THEN ${last_reporting.act_endg_upb}
  ELSE NULL
END
  - dimension: fico_over_700
    type: yesno
    hidden: true
    label: fico is over 700
    sql: ${fico} >= 700
  - dimension: fico_tiered_100
    type: tier
    hidden: true
    label: fico tiered (by 100, 6 bins)
    sql: ${fico}
  - dimension: fico_tiered_50
    type: tier
    hidden: true
    label: fico tiered (by 50, 12 bins)
    sql: ${fico}
  - dimension: bad_fico
    type: yesno
    hidden: true
    label: fico is below 380
    sql: ${fico} < 380
  - dimension: flag_fthb
    type: yesno
    hidden: true
    label: First Time Homebuyer (fthb)
    sql: ${TABLE}.flag_fthb
  - dimension: loan_purpose
  - dimension: ltv_tier
    type: tier
    hidden: true
    sql: ${ltv}
  - dimension: mi_pct
    type: number
    hidden: true
    label: Mortgage Insurance Percentage (pct)
    sql: ${TABLE}.mi_pct
  - dimension: occpy_sts
    hidden: true
    label: Occupancy Status
  - dimension: orig_loan_term
    type: number
    hidden: true
    sql: ${TABLE}.orig_loan_term
  - dimension: orig_property_value
    type: number
    hidden: true
    sql: 1.0 * ${orig_upb} / ${ltv}
  - dimension: amount_upb_tier
    type: tier
    hidden: true
    sql: ${orig_upb}
  - dimension: ppmt_pnlty
    type: yesno
    hidden: true
    sql: ${TABLE}.ppmt_pnlty
  - dimension: seller_name
    hidden: true
    sql: ${TABLE}.seller_name
  - dimension: st
    hidden: true
    label: State
    sql: ${TABLE}.st
  - dimension: dti
    type: number
    hidden: true
    label: Original Debt-to-Income (dti)
    sql: ${TABLE}.dti
  - dimension_group: dt_first_pi
    type: time
    timeframes:
    - year
    - month
    - month_num
    sql: ${TABLE}.dt_first_pi
  - dimension_group: dt_matr
    type: time
    timeframes:
    - year
    - month
    sql: trunc(dateadd(day,1,TO_DATE(${TABLE}.dt_matr,'YYYYMM')))
  - measure: count_fico_more_than_700
    type: count
    filters:
    - fico_more_than_700: True
  - measure: count
    type: count
    label: Loan Count
  - measure: total_orig_upb
    type: sum
    label: Total Balance
    sql: ${orig_upb}
  - measure: total_orig_upb_700
    type: sum
    label: 'Total Balance > 700'
    filters:
    - fico_more_than_700: True
    sql: ${orig_upb}
  - measure: int_times_upb
    type: sum
    sql: 1.0 * ${int_rt} * ${orig_upb}
  - measure: wa_int_rate
    type: number
    label: WA Coupon
    sql: ${int_times_upb} / ${total_orig_upb}
  - measure: average_orig_upb
    type: avg
    label: Average Balance
    sql: ${orig_upb}
  - measure: count_bad_ficos
    type: count
    filters:
    - bad_fico: True
  - measure: percent_bad_ficos
    type: number
    sql: 100.0 * ${count_bad_ficos}/NULLIF(${count},0)
  - measure: average_int_rt
    type: avg
    sql: ${int_rt}
  - measure: average_fico
    type: avg
    sql: ${fico}
  - measure: average_ltv
    type: avg
    sql: ${ltv}
  - measure: average_dti
    type: avg
    label: Average Original Debt-to-Income (DTI) Ratio
    sql: ${dti}
  - measure: count_percent_of_previous
    type: percent_of_previous
    sql: ${count}
  - measure: total_amount_percent_of_previous
    type: percent_of_previous
    sql: ${total_orig_upb}