Session Duration Fact

view: session_duration_fact {
  sql_table_name: public.session_duration_fact ;;

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

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

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

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

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

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

  dimension_group: session_duration {
    type: time
    timeframes: [date, week, month, month_num, day_of_week, day_of_week_index, week_of_year, day_of_month, time, hour_of_day, hour, time_of_day]
    sql: (SUBSTRING(${date_id}, 1, 4) || '-' || SUBSTRING(${date_id}, 5, 2) || '-' || SUBSTRING(${date_id}, 7, 2)|| ' ' || ${time_dim.minute_description} || ':00')::timestamp ;;
  }

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

  measure: sum_duration {
    type: sum
    sql: ${duration} ;;
  }

  measure: count_of_sessions {
    type: count_distinct
    sql: ${id} ;;
  }

  measure: sum_sessions {
    type: sum
    sql: ${id} ;;
  }

  measure: count_of_users {
    type: count_distinct
    sql: ${user_id} ;;
  }

  measure: avg_session_duration {
    type: number
    sql: ( 1.0 * ${sum_duration})/NULLIF(${count_of_sessions},0) ;;
  }

  measure: min_session_duration {
    type: min
    sql: ${duration} ;;
  }

  measure: max_session_duration {
    type: max
    sql: ${duration} ;;
  }

  measure: avg_sessions {
    type: number
    sql: ( 1.0 * ${count_of_sessions})/NULLIF(${count_of_users},0) ;;
  }
}

- view: session_duration_fact
  sql_table_name: public.session_duration_fact
  fields:
  - dimension: id
    type: int
    primary_key: true
    sql: ${TABLE}.id
  - dimension: client_id
    type: int
    sql: ${TABLE}.client_id
  - dimension: date_id
    type: int
    sql: ${TABLE}.date_id
  - dimension: duration
    type: int
    sql: ${TABLE}.duration
  - dimension: time_id
    type: int
    sql: ${TABLE}.time_id
  - dimension: user_id
    type: int
    sql: ${TABLE}.user_id
  - dimension_group: session_duration
    type: time
    timeframes:
    - date
    - week
    - month
    - month_num
    - day_of_week
    - day_of_week_index
    - week_of_year
    - day_of_month
    - time
    - hour_of_day
    - hour
    - time_of_day
    sql: (SUBSTRING(${date_id}, 1, 4) || '-' || SUBSTRING(${date_id}, 5, 2) || '-' || SUBSTRING(${date_id}, 7, 2)|| ' ' || ${time_dim.minute_description} || ':00')::timestamp
  - measure: count
    type: count
    drill_fields:
    - id
  - measure: sum_duration
    type: sum
    sql: ${duration}
  - measure: count_of_sessions
    type: count_distinct
    sql: ${id}
  - measure: sum_sessions
    type: sum
    sql: ${id}
  - measure: count_of_users
    type: count_distinct
    sql: ${user_id}
  - measure: avg_session_duration
    type: number
    sql: ( 1.0 * ${sum_duration})/NULLIF(${count_of_sessions},0)
  - measure: min_session_duration
    type: min
    sql: ${duration}
  - measure: max_session_duration
    type: max
    sql: ${duration}
  - measure: avg_sessions
    type: number
    sql: ( 1.0 * ${count_of_sessions})/NULLIF(${count_of_users},0)