Edume PDT

Edume PDT

view: edume_lessons {
  derived_table: {
    sql:
SELECT
    courses_modules_completiondate,
    courses_modules_id,
    courses_modules_status,
    courses_modules_title,
    courses_id,
    courses_title,
    activated,
    createdat,
    email,
    employeeid,
    firstname,
    group_name,id,
    lastname,
    phone,
    teams,
    lessons_courseid,
    lessons_id,
    lessons_lessonnumber,
    lessons_moduleid,
    lessons_score,
    lessons_title,
    lessons_date,
    user_id,
    test_2.country_modules_number AS total_country_modules,
    test_3.completed_modules_by_user AS completed_modules_by_user,
    test_4.average_lessons_score AS team_average_lessons_score,
    test_4.total_lessons_score AS team_total_lesson_scores,
    test_4.lessons AS team_lesson_count,
    FIRST_VALUE(lessons_date) OVER (
                              PARTITION BY lessons_moduleid,lessons_title,user_id
                              ORDER BY lessons_date ASC
                              ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
        AS ealiest_lesson_completion_date

      FROM edume.test AS test_1

  LEFT JOIN (

  SELECT group_name AS name, COUNT(DISTINCT courses_modules_title) AS country_modules_number
        FROM edume.test
        GROUP BY name) AS test_2
    ON test_1.group_name = test_2.name

  LEFT JOIN (

    SELECT user_id AS user_identity, COUNT(DISTINCT
        CASE
          WHEN courses_modules_status = 'complete'
            THEN
              courses_modules_title
            ELSE NULL
        END)  AS completed_modules_by_user
          FROM edume.test
          GROUP BY user_identity) AS test_3
    ON test_1.user_id = test_3.user_identity

  LEFT JOIN (

    SELECT teams AS team, SUM(lessons_score) AS total_lessons_score, COUNT(lessons_title) AS lessons,
            (total_lessons_score/lessons) AS average_lessons_score
          FROM edume.test
                    GROUP BY team) AS test_4
      ON test_1.teams = test_4.team
    ;;

    sql_trigger_value: SELECT COUNT(*) FROM edume.test ;;
    distribution_style: even
    sortkeys: ["lessons_date"]
    }

  dimension: unique_id {
    primary_key: yes
    type: string
    sql: ${user_id}||${lesson_name}||${lessons_date_time} ;;
  }

  dimension: course_completion_date {
    type: date
    sql: ${TABLE}.courses_modules_completiondate ;;
  }

  dimension: module_id {
    type: number
    sql: ${TABLE}.courses_modules_id ;;
  }

  dimension: module_status {
    type: string
    sql: ${TABLE}.courses_modules_status ;;
  }

  dimension: module_title {
    type: string
    sql: ${TABLE}.courses_modules_title ;;
  }

  dimension: course_id {
    type: number
    sql: ${TABLE}.courses_id ;;
  }

  dimension: course_title {
    type: string
    sql: ${TABLE}.courses_title ;;
  }

  dimension: activated {
    type: string
    sql: ${TABLE}.activated ;;
  }

  dimension_group: edume_log_in {
    type: time
    timeframes: [time, date, week, month, raw]
    sql: ${TABLE}.createdat ;;
  }

  dimension: email {
    type: string
    sql: ${TABLE}.email ;;
  }

  dimension: employee_id {
    type: string
    sql: ${TABLE}.employeeid ;;
  }

  dimension: first_name {
    type: string
    sql: ${TABLE}.firstname ;;
  }

  dimension: lower_case_first_name {
    hidden: yes
    type: string
    sql: LOWER (${first_name}) ;;
  }

  dimension: group_name {
    type: string
    sql: ${TABLE}.group_name ;;
  }

  dimension: id {
    type: number
    sql: ${TABLE}.id ;;
  }

  dimension: last_name {
    type: string
    sql: ${TABLE}.lastname ;;
  }

  dimension: lower_case_last_name {
    hidden: yes
    type: string
    sql: LOWER (${last_name}) ;;
  }

  dimension: first_last_name  {
    type: string
    sql:REPLACE(${lower_case_first_name}||${lower_case_last_name},' ','') ;;
  }

  dimension: mobile_number {
    type: string
    sql: '+'||${TABLE}.phone ;;
  }

  dimension: teams {
    type: string
    sql: ${TABLE}.teams ;;
  }

  dimension: lesson_number {
    type: string
    sql: lessons_courseid ;;
  }

  dimension: lesson_id {
    type: number
    sql: lessons_moduleid ;;
  }

  dimension: lesson_score {
    type: number
    sql: ${TABLE}.lessons_score ;;
  }

  dimension: lesson_name {
    type: string
    sql: ${TABLE}.lessons_title ;;
  }

  dimension: user_id {
    type: number
    sql: ${TABLE}.user_id ;;
  }

  dimension: lessons_date_time {
    hidden: yes
    type: date_time
    sql: ${TABLE}.lessons_date ;;
  }

  dimension: earliest_lesson_attempt_date_time {
    type: date_time
    sql: ${TABLE}.ealiest_lesson_completion_date ;;
  }

  dimension: earliest_lesson_attempt_score {
    hidden: yes
    type: string
    value_format_name: decimal_2
    sql:
    CASE
      WHEN ${earliest_lesson_attempt_date_time} = ${lessons_date_time}
        THEN ${lesson_score}
      ELSE NULL
    END
    ;;
  }

  dimension: first_attempt_total_lesson_score {
    hidden: yes
    type: string
    sql: ${earliest_lesson_attempt_score} ;;
  }

  measure: count_distinct_edume_users {
    type: count_distinct
    filters: {
      field: agent_status.is_active_agent
      value: "yes"
    }
    sql: ${user_id} ;;
  }

  measure: total_lesson_score {
    description: "lesson score at the first lesson attempt"
    type: sum
    sql: CAST(COALESCE(${first_attempt_total_lesson_score},'0') AS float)
     ;;
  }

  dimension: agent_uses_edume {
    type: yesno
    sql: ${angaza_users.agent_phone_number} = ${mobile_number} ;;
  }

  measure: edume_utilization_rate {
    type: number
  value_format_name: percent_1
    sql: 1.0 * ${count_distinct_edume_users}/NULLIF(${agent_status.count_active_agents},0) ;;
  }

  dimension: agent_has_completed_module_on_collections {
   type: string
    sql:
      CASE
        WHEN ${module_status} = 'complete' AND ${module_title} ILIKE '%collection%'
          THEN
            'yes'
        ELSE
            'no'
    END ;;
  }

  dimension: modules_completed_by_user {
    type: number
    value_format_name: decimal_1
    sql: ${TABLE}.completed_modules_by_user ;;
  }

  dimension: total_country_modules {
    type: number
    value_format_name: decimal_1
    sql: ${TABLE}.total_country_modules ;;
  }

  dimension: percentage_completed_modules {
    type: number
    value_format_name: decimal_1
    sql: 100.0 * ${modules_completed_by_user} / NULLIF(${total_country_modules},0) ;;
    }

  dimension: module_completion_tiers {
    type: string
    sql:
      CASE
        WHEN ${percentage_completed_modules} BETWEEN 0  AND 25  THEN '0 to 25%'
        WHEN ${percentage_completed_modules} BETWEEN 26 AND 50  THEN '26 to 50%'
        WHEN ${percentage_completed_modules} BETWEEN 51 AND 75  THEN '51 to 75%'
        WHEN ${percentage_completed_modules} BETWEEN 76 AND 100 THEN '76 to 100%'
        ELSE 'Not using EduMe'
      END ;;

  }

  dimension: team_average_lessons_score {
    type: number
    sql: ${TABLE}.team_average_lessons_score ;;
  }

  dimension: team_total_lesson_score {
    type: number
    sql: ${TABLE}.team_total_lesson_scores ;;
  }

  dimension: team_lesson_count {
    type: number
    sql: ${TABLE}.team_lesson_count ;;
  }

  measure: count {
    type: count
  }
}