Applicant

view: applicant {
  sql_table_name: applicantfact ;;

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

  dimension: candidate_id {
    type: int
    hidden: yes
    sql: ${TABLE}.candidate_id ;;
  }

  dimension: jobactivity_id {
    type: int
    hidden: yes
    sql: ${TABLE}.jobactivity_id ;;
  }

  dimension: company_candidate_employer_job_id {
    type: int
    hidden: yes
    sql: ${TABLE}.company_candidateemployerjob_id ;;
  }

  dimension: date_id {
    type: int
    hidden: yes
    sql: ${TABLE}.date_id ;;
  }

  dimension: employer_id {
    type: int
    hidden: yes
    sql: ${TABLE}.employer_id ;;
  }

  dimension: inconsistency_flag {
    type: yesno
    sql: ${created_date} < ${candidate.created_date} ;;
  }

  dimension: fit_id {
    type: int
    hidden: yes
    sql: ${TABLE}.fit_id ;;
  }

  dimension: fit_score {
    type: number
    sql: ${TABLE}.fitscore_number ;;
  }

  dimension: fittype_id {
    type: int
    hidden: yes
    sql: ${TABLE}.fittype_id ;;
  }

  dimension: job_id {
    type: int
    hidden: yes
    sql: ${TABLE}.job_id ;;
  }

  dimension: record_batch_id {
    type: int
    hidden: yes
    sql: ${TABLE}.record_batch_id ;;
  }

  dimension: requirement_score {
    type: number
    sql: ${TABLE}.requirementscore_number ;;
  }

  dimension: source_id {
    type: int
    hidden: yes
    sql: ${TABLE}.source_id ;;
  }

  dimension: stage {
    description: "At which stage the applicant is for the job"
    sql: ${TABLE}.stage_text ;;
  }

  dimension: comment_flag {
    description: "Does the applicant have a comment from the employer?"
    type: yesno
    sql: ${TABLE}.commented_flag ;;
  }

  dimension_group: created {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.created_time ;;
  }

  measure: count {
    type: count
    drill_fields: [drill_fields*]
  }

  measure: count_percent_of_total {
    label: "Count (Percent of Total)"
    type: percent_of_total
    drill_fields: [drill_fields*]
    sql: ${count} ;;
  }

  measure: count_yes {
    description: "Completed Applicants"
    type: count
    filters: [fit.completed: "True"]
    drill_fields: [drill_fields*]
  }

  measure: assessment_completion_rate {
    type: number
    value_format: "#.00%"
    drill_fields: [detail2*]
    sql: ${count_yes}::float / NULLIF(${count}::float,0) ;;
  }

  measure: count_no {
    description: "Incomplete Applicants"
    type: count
    filters: [fit.completed: "False"]
    drill_fields: [drill_fields*]
  }

  measure: unique_applicant {
    type: count_distinct
    drill_fields: [drill_fields*]
    sql: ${TABLE}.company_candidateemployerjob_id ;;
  }
}

- view: applicant
  sql_table_name: applicantfact
  fields:
  - dimension: applicantfact_id
    type: int
    primary_key: true
    sql: ${TABLE}.applicantfact_id
  - dimension: candidate_id
    type: int
    hidden: true
    sql: ${TABLE}.candidate_id
  - dimension: jobactivity_id
    type: int
    hidden: true
    sql: ${TABLE}.jobactivity_id
  - dimension: company_candidate_employer_job_id
    type: int
    hidden: true
    sql: ${TABLE}.company_candidateemployerjob_id
  - dimension: date_id
    type: int
    hidden: true
    sql: ${TABLE}.date_id
  - dimension: employer_id
    type: int
    hidden: true
    sql: ${TABLE}.employer_id
  - dimension: inconsistency_flag
    type: yesno
    sql: ${created_date} < ${candidate.created_date}
  - dimension: fit_id
    type: int
    hidden: true
    sql: ${TABLE}.fit_id
  - dimension: fit_score
    type: number
    sql: ${TABLE}.fitscore_number
  - dimension: fittype_id
    type: int
    hidden: true
    sql: ${TABLE}.fittype_id
  - dimension: job_id
    type: int
    hidden: true
    sql: ${TABLE}.job_id
  - dimension: record_batch_id
    type: int
    hidden: true
    sql: ${TABLE}.record_batch_id
  - dimension: requirement_score
    type: number
    sql: ${TABLE}.requirementscore_number
  - dimension: source_id
    type: int
    hidden: true
    sql: ${TABLE}.source_id
  - dimension: stage
    description: At which stage the applicant is for the job
    sql: ${TABLE}.stage_text
  - dimension: comment_flag
    type: yesno
    description: Does the applicant have a comment from the employer?
    sql: ${TABLE}.commented_flag
  - dimension_group: created
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.created_time
  - measure: count
    type: count
    drill_fields:
    - drill_fields*
  - measure: count_percent_of_total
    type: percent_of_total
    label: Count (Percent of Total)
    drill_fields:
    - drill_fields*
    sql: ${count}
  - measure: count_yes
    type: count
    description: Completed Applicants
    drill_fields:
    - drill_fields*
    filters:
    - fit.completed: True
  - measure: assessment_completion_rate
    type: number
    value_format: '#.00%'
    drill_fields:
    - detail2*
    sql: ${count_yes}::float / NULLIF(${count}::float,0)
  - measure: count_no
    type: count
    description: Incomplete Applicants
    drill_fields:
    - drill_fields*
    filters:
    - fit.completed: False
  - measure: unique_applicant
    type: count_distinct
    drill_fields:
    - drill_fields*
    sql: ${TABLE}.company_candidateemployerjob_id