Water For Good

view: water_for_good {
  sql_table_name: public.wfg ;;

  dimension: id {
    type: int
    primary_key: yes
    sql: ${TABLE}.id ;;
  }

  dimension: amount_of_payment {
    type: int
    sql: ${TABLE}.amount_of_payment ;;
  }

  dimension: liters_per_person {
    type: number
    sql: ${TABLE}.average_liters_per_person ;;
  }

  dimension: time_to_collect_water {
    type: number
    sql: ${TABLE}.avg_time_to_collect_water ;;
  }

  dimension: contract {
    label: "Is there a contract?"
    sql: ${TABLE}.contract ;;
  }

  dimension: county {
    sql: ${TABLE}.county ;;
  }

  dimension: depth {
    label: "Well Depth"
    sql: ${TABLE}.depth ;;
  }

  dimension: district {
    sql: ${TABLE}.district ;;
  }

  dimension: pipeline {
    label: "Part of pipeline?"
    sql: ${TABLE}.pipeline ;;
  }

  dimension: population {
    type: int
    sql: ${TABLE}.population ;;
  }

  dimension: pump_id {
    sql: ${TABLE}.pump_id ;;
  }

  dimension: pump_model {
    label: "Pump Type"
    sql: ${TABLE}.pump_model ;;
  }

  dimension: pump_working {
    label: "Working?"
    sql: ${TABLE}.pump_working ;;
  }

  dimension: reason_not_working {
    sql: ${TABLE}.reason_not_working ;;
  }

  dimension: repair_result_is_pump_working {
    label: "Pump working after repair?"
    sql: ${TABLE}.repair_result_is_pump_working ;;
  }

  dimension: sub_district {
    sql: ${TABLE}.sub_district ;;
  }

  dimension: number_drinking_from_pump {
    type: int
    sql: ${TABLE}.number_drinking_from_pump ;;
  }

  dimension: village_name {
    sql: ${TABLE}.village_name ;;
  }

  dimension: water_disinfected {
    sql: ${TABLE}.water_disinfected ;;
  }

  dimension: water_level {
    sql: ${TABLE}.water_level ;;
  }

  dimension: weeks_out_of_service {
    type: int
    sql: ${TABLE}.weeks_out_of_service ;;
  }

  measure: total_amount_of_payment {
    type: sum
    sql: ${amount_of_payment} ;;
  }

  measure: average_liters_per_person {
    type: avg
    sql: ${liters_per_person} ;;
  }

  measure: average_time_to_collect_water {
    type: avg
    sql: ${time_to_collect_water} ;;
  }

  measure: total_with_contract {
    type: count
    drill_fields: [id, village_name, contract]
  }

  measure: average_population {
    type: avg
    sql: ${population} ;;
  }

  measure: total_population {
    type: sum
    sql: ${population} ;;
  }

  measure: pump_model_count {
    type: count_distinct
    drill_fields: [id, pump_model]
    sql: ${pump_model} ;;
  }

  measure: count_of_working_pumps {
    type: count
    filters: [pump_working: "Oui"]
    drill_fields: [id, village_name, pump_model, pump_working]
  }

  measure: count_of_nonworking_pumps {
    type: count
    filters: [pump_working: "Non"]
    drill_fields: [id, village_name, pump_model, pump_working]
  }

  measure: count_of_working_pumps_after_repair {
    label: "Successful repairs"
    type: count
    filters: [repair_result_is_pump_working: "Oui"]
    drill_fields: [id, village_name, pump_model, repair_result_is_pump_working]
  }

  measure: count_of_nonworking_pumps_after_repair {
    label: "Unsuccessful repairs"
    type: count
    filters: [repair_result_is_pump_working: "Non"]
    drill_fields: [id, village_name, pump_model, repair_result_is_pump_working]
  }

  measure: total_drinking_from_pump {
    type: sum
    sql: ${number_drinking_from_pump} ;;
  }

  measure: average_drinking_from_pump {
    type: avg
    sql: ${number_drinking_from_pump} ;;
  }

  measure: total_water_disinfected {
    type: count
    drill_fields: [id, village_name, pump_model, water_disinfected]
  }

  measure: total_weeks_out_of_service {
    type: sum
    sql: ${weeks_out_of_service} ;;
  }

  measure: average_weeks_out_of_service {
    type: avg
    sql: ${weeks_out_of_service} ;;
  }

  measure: count {
    type: count
    drill_fields: [id, village_name, pump_model, pump_working]
  }
}

- view: water_for_good
  sql_table_name: public.wfg
  fields:
  - dimension: id
    type: int
    primary_key: true
    sql: ${TABLE}.id
  - dimension: amount_of_payment
    type: int
    sql: ${TABLE}.amount_of_payment
  - dimension: liters_per_person
    type: number
    sql: ${TABLE}.average_liters_per_person
  - dimension: time_to_collect_water
    type: number
    sql: ${TABLE}.avg_time_to_collect_water
  - dimension: contract
    label: Is there a contract?
    sql: ${TABLE}.contract
  - dimension: county
    sql: ${TABLE}.county
  - dimension: depth
    label: Well Depth
    sql: ${TABLE}.depth
  - dimension: district
    sql: ${TABLE}.district
  - dimension: pipeline
    label: Part of pipeline?
    sql: ${TABLE}.pipeline
  - dimension: population
    type: int
    sql: ${TABLE}.population
  - dimension: pump_id
    sql: ${TABLE}.pump_id
  - dimension: pump_model
    label: Pump Type
    sql: ${TABLE}.pump_model
  - dimension: pump_working
    label: Working?
    sql: ${TABLE}.pump_working
  - dimension: reason_not_working
    sql: ${TABLE}.reason_not_working
  - dimension: repair_result_is_pump_working
    label: Pump working after repair?
    sql: ${TABLE}.repair_result_is_pump_working
  - dimension: sub_district
    sql: ${TABLE}.sub_district
  - dimension: number_drinking_from_pump
    type: int
    sql: ${TABLE}.number_drinking_from_pump
  - dimension: village_name
    sql: ${TABLE}.village_name
  - dimension: water_disinfected
    sql: ${TABLE}.water_disinfected
  - dimension: water_level
    sql: ${TABLE}.water_level
  - dimension: weeks_out_of_service
    type: int
    sql: ${TABLE}.weeks_out_of_service
  - measure: total_amount_of_payment
    type: sum
    sql: ${amount_of_payment}
  - measure: average_liters_per_person
    type: avg
    sql: ${liters_per_person}
  - measure: average_time_to_collect_water
    type: avg
    sql: ${time_to_collect_water}
  - measure: total_with_contract
    type: count
    drill_fields:
    - id
    - village_name
    - contract
  - measure: average_population
    type: avg
    sql: ${population}
  - measure: total_population
    type: sum
    sql: ${population}
  - measure: pump_model_count
    type: count_distinct
    drill_fields:
    - id
    - pump_model
    sql: ${pump_model}
  - measure: count_of_working_pumps
    type: count
    drill_fields:
    - id
    - village_name
    - pump_model
    - pump_working
    filters:
    - pump_working: Oui
  - measure: count_of_nonworking_pumps
    type: count
    drill_fields:
    - id
    - village_name
    - pump_model
    - pump_working
    filters:
    - pump_working: Non
  - measure: count_of_working_pumps_after_repair
    type: count
    label: Successful repairs
    drill_fields:
    - id
    - village_name
    - pump_model
    - repair_result_is_pump_working
    filters:
    - repair_result_is_pump_working: Oui
  - measure: count_of_nonworking_pumps_after_repair
    type: count
    label: Unsuccessful repairs
    drill_fields:
    - id
    - village_name
    - pump_model
    - repair_result_is_pump_working
    filters:
    - repair_result_is_pump_working: Non
  - measure: total_drinking_from_pump
    type: sum
    sql: ${number_drinking_from_pump}
  - measure: average_drinking_from_pump
    type: avg
    sql: ${number_drinking_from_pump}
  - measure: total_water_disinfected
    type: count
    drill_fields:
    - id
    - village_name
    - pump_model
    - water_disinfected
  - measure: total_weeks_out_of_service
    type: sum
    sql: ${weeks_out_of_service}
  - measure: average_weeks_out_of_service
    type: avg
    sql: ${weeks_out_of_service}
  - measure: count
    type: count
    drill_fields:
    - id
    - village_name
    - pump_model
    - pump_working