History Events

view: history_events {
  sql_table_name: generic_looker_test.history_events ;;

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

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

  dimension: created_at_day {
    sql: CASE
WHEN EXTRACT(dow FROM CONVERT_TIMEZONE('America/Chicago', ${TABLE}.created_at)) = 0 THEN 'Sunday'
WHEN EXTRACT(dow FROM CONVERT_TIMEZONE('America/Chicago', ${TABLE}.created_at)) = 1 THEN 'Monday'
WHEN EXTRACT(dow FROM CONVERT_TIMEZONE('America/Chicago', ${TABLE}.created_at)) = 2 THEN 'Tuesday'
WHEN EXTRACT(dow FROM CONVERT_TIMEZONE('America/Chicago', ${TABLE}.created_at)) = 3 THEN 'Wednesday'
WHEN EXTRACT(dow FROM CONVERT_TIMEZONE('America/Chicago', ${TABLE}.created_at)) = 4 THEN 'Thursday'
WHEN EXTRACT(dow FROM CONVERT_TIMEZONE('America/Chicago', ${TABLE}.created_at)) = 5 THEN 'Friday'
WHEN EXTRACT(dow FROM CONVERT_TIMEZONE('America/Chicago', ${TABLE}.created_at)) = 6 THEN 'Saturday'
ELSE NULL
END ;;
  }

  dimension: created_at_dom {
    type: int
    sql: EXTRACT(day FROM CONVERT_TIMEZONE('America/Chicago', ${TABLE}.created_at)) ;;
  }

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

  dimension: interaction {
    sql: ${TABLE}.type = 'email_sent' ;;
  }

  dimension: resolve {
    sql: ${TABLE}.type = 'case_resolved' ;;
  }

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

  dimension_group: created_at {
    type: time
    timeframes: [time, date, week, month, hod, dow, month_num, tod]
    sql: CONVERT_TIMEZONE('America/Chicago', ${TABLE}.created_at) ;;
  }

  measure: count {
    type: count
  }

  measure: interaction_count {
    type: count
    filters: [history_events.type: "email_sent"]
  }

  measure: inbound_interaction_count {
    type: count
    filters: [history_events.type: "email_sent"]
    filters: [history_events.user_id: "NULL"]
  }

  measure: outbound_interaction_count {
    type: count
    filters: [history_events.type: "email_sent"]
    filters: [history_events.user_id: "NOT NULL"]
  }

  measure: number_of_agent_opens {
    type: count
    filters: [history_events.type: "queue_item_pulled"]
  }

  measure: number_of_readonly_opens {
    type: count
    filters: [history_events.type: "queue_item_read_only"]
  }

  measure: number_of_resolves {
    type: count
    filters: [history_events.type: "case_resolved"]
  }

  measure: number_of_reopens {
    type: count
    filters: [history_events.type: "case_reopened"]
  }
}

- view: history_events
  sql_table_name: generic_looker_test.history_events
  fields:
  - dimension: id
    type: int
    primary_key: true
    sql: ${TABLE}.id
  - dimension: case_id
    type: int
    hidden: true
    sql: ${TABLE}.case_id
  - dimension: created_at_day
    sql: CASE
WHEN EXTRACT(dow FROM CONVERT_TIMEZONE('America/Chicago', ${TABLE}.created_at)) = 0 THEN 'Sunday'
WHEN EXTRACT(dow FROM CONVERT_TIMEZONE('America/Chicago', ${TABLE}.created_at)) = 1 THEN 'Monday'
WHEN EXTRACT(dow FROM CONVERT_TIMEZONE('America/Chicago', ${TABLE}.created_at)) = 2 THEN 'Tuesday'
WHEN EXTRACT(dow FROM CONVERT_TIMEZONE('America/Chicago', ${TABLE}.created_at)) = 3 THEN 'Wednesday'
WHEN EXTRACT(dow FROM CONVERT_TIMEZONE('America/Chicago', ${TABLE}.created_at)) = 4 THEN 'Thursday'
WHEN EXTRACT(dow FROM CONVERT_TIMEZONE('America/Chicago', ${TABLE}.created_at)) = 5 THEN 'Friday'
WHEN EXTRACT(dow FROM CONVERT_TIMEZONE('America/Chicago', ${TABLE}.created_at)) = 6 THEN 'Saturday'
ELSE NULL
END
  - dimension: created_at_dom
    type: int
    sql: EXTRACT(day FROM CONVERT_TIMEZONE('America/Chicago', ${TABLE}.created_at))
  - dimension: type
    sql: ${TABLE}.type
  - dimension: interaction
    sql: ${TABLE}.type = 'email_sent'
  - dimension: resolve
    sql: ${TABLE}.type = 'case_resolved'
  - dimension: user_id
    type: int
    hidden: true
    sql: ${TABLE}.user_id
  - dimension_group: created_at
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - hod
    - dow
    - month_num
    - tod
    sql: CONVERT_TIMEZONE('America/Chicago', ${TABLE}.created_at)
  - measure: count
    type: count
  - measure: interaction_count
    type: count
    filters:
    - history_events.type: email_sent
  - measure: inbound_interaction_count
    type: count
    filters:
    - history_events.type: email_sent
    - history_events.user_id: NULL
  - measure: outbound_interaction_count
    type: count
    filters:
    - history_events.type: email_sent
    - history_events.user_id: NOT NULL
  - measure: number_of_agent_opens
    type: count
    filters:
    - history_events.type: queue_item_pulled
  - measure: number_of_readonly_opens
    type: count
    filters:
    - history_events.type: queue_item_read_only
  - measure: number_of_resolves
    type: count
    filters:
    - history_events.type: case_resolved
  - measure: number_of_reopens
    type: count
    filters:
    - history_events.type: case_reopened