User

view: user {
  sql_table_name: "user_table" ;;

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

  dimension: apns_token {
    hidden: yes
    sql: ${TABLE}.apns_token ;;
  }

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

  dimension: age {
    type: int
    sql: date_part('year',age(current_date, ${TABLE}.birthday)) ;;
  }

  dimension: age_tier {
    type: tier
    sql: ${age} ;;
  }

  dimension: days_on_platform {
    type: number
    sql: CASE
WHEN ${TABLE}.suspended_at IS NULL THEN NOW()::DATE - ${created_date}
ELSE ${suspended_date} - ${created_date}
END ;;
  }

  dimension: weeks_on_platform {
    type: number
    sql: ${days_on_platform} / 7 ;;
  }

  dimension: estimated_ltv {
    description: "Estimated value, does not include refunds, chargebacks, etc."
    type: number
    sql: CASE
WHEN ${payment_plan} = 'monthly' THEN (${days_on_platform}/30)*${payment_plan_value}
WHEN ${payment_plan} = 'weekly' THEN (${days_on_platform}/7)*${payment_plan_value}
WHEN ${payment_plan} = 'trimonthly' THEN (${days_on_platform}/90)*${payment_plan_value}
ELSE 0
END ;;
  }

  dimension: current_login_ip {
    hidden: yes
    sql: ${TABLE}.current_login_ip ;;
  }

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

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

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

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

  dimension: last_login_ip {
    hidden: yes
    sql: ${TABLE}.last_login_ip ;;
  }

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

  dimension: name {
    sql: ${firstname} || ' ' || ${lastname} ;;
  }

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

  dimension: metrics_id {
    hidden: yes
    sql: ${TABLE}.metrics_id ;;
  }

  dimension: password {
    hidden: yes
    sql: ${TABLE}.password ;;
  }

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

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

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

  dimension: stripe_customer_id {
    hidden: yes
    sql: ${TABLE}.stripe_customer_id ;;
  }

  dimension: is_active {
    type: yesno
    sql: ${TABLE}.suspended_at IS NULL OR ${TABLE}.suspended_at > NOW() AT TIME ZONE 'utc' ;;
  }

  dimension: history {
    sql: ${TABLE}.id ;;
  }

  dimension: has_entry {
    type: yesno
    sql: EXISTS(
  SELECT me.*
  FROM meal_entry AS me
  LEFT JOIN meal AS m ON me.meal_id = m.id
  WHERE ${TABLE}.id = m.client_id) ;;
  }

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

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

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

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

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

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

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

  measure: count_clients {
    type: count
    filters: [roles_users.role_name: "client"]
    drill_fields: [detail*]
  }

  measure: count_active_clients {
    type: count
    filters: [is_active: "True"]
    filters: [roles_users.role_name: "client"]
    drill_fields: [detail*]
  }

  measure: count_coaches {
    type: count
    filters: [roles_users.role_name: "coach"]
    drill_fields: [detail*]
  }

  measure: count_has_entry {
    type: count
    filters: [has_entry: "yes"]
    drill_fields: [detail*]
  }

  measure: percent_clients {
    type: number
    sql: 100.0 * ${count_clients}/NULLIF(${count_coaches}+${count_clients},0) ;;
  }

  measure: percent_active_clients {
    type: number
    sql: 100.0 * ${count_active_clients}/NULLIF(${count},0) ;;
  }

  measure: percent_coaches {
    type: number
    sql: 100.0 * ${count_coaches}/NULLIF(${count_coaches}+${count_clients},0) ;;
  }

  measure: total_estimated_ltv {
    type: sum
    sql: ${estimated_ltv} ;;
  }

  measure: avg_estimated_ltv {
    type: avg
    sql: ${estimated_ltv} ;;
  }

  measure: avg_days_on_platform {
    type: avg
    sql: ${days_on_platform} ;;
  }

  measure: percent_of_total_users {
    type: percent_of_total
    sql: ${count} ;;
  }

  measure: percent_of_total_coaches {
    type: percent_of_total
    sql: ${count_coaches} ;;
  }

  measure: percent_of_total_clients {
    type: percent_of_total
    sql: ${count_clients} ;;
  }
}

- view: user
  sql_table_name: '"user_table"'
  fields:
  - dimension: id
    primary_key: true
    sql: ${TABLE}.id
  - dimension: apns_token
    hidden: true
    sql: ${TABLE}.apns_token
  - dimension: attributes
    sql: ${TABLE}.attributes
  - dimension: age
    type: int
    sql: date_part('year',age(current_date, ${TABLE}.birthday))
  - dimension: age_tier
    type: tier
    sql: ${age}
  - dimension: days_on_platform
    type: number
    sql: CASE
WHEN ${TABLE}.suspended_at IS NULL THEN NOW()::DATE - ${created_date}
ELSE ${suspended_date} - ${created_date}
END
  - dimension: weeks_on_platform
    type: number
    sql: ${days_on_platform} / 7
  - dimension: estimated_ltv
    type: number
    description: Estimated value, does not include refunds, chargebacks, etc.
    sql: CASE
WHEN ${payment_plan} = 'monthly' THEN (${days_on_platform}/30)*${payment_plan_value}
WHEN ${payment_plan} = 'weekly' THEN (${days_on_platform}/7)*${payment_plan_value}
WHEN ${payment_plan} = 'trimonthly' THEN (${days_on_platform}/90)*${payment_plan_value}
ELSE 0
END
  - dimension: current_login_ip
    hidden: true
    sql: ${TABLE}.current_login_ip
  - dimension: email
    sql: ${TABLE}.email
  - dimension: firstname
    sql: ${TABLE}.firstname
  - dimension: gender
    sql: ${TABLE}.gender
  - dimension: height
    type: int
    sql: ${TABLE}.height
  - dimension: last_login_ip
    hidden: true
    sql: ${TABLE}.last_login_ip
  - dimension: lastname
    sql: ${TABLE}.lastname
  - dimension: name
    sql: ${firstname} || ' ' || ${lastname}
  - dimension: login_count
    type: int
    hidden: true
    sql: ${TABLE}.login_count
  - dimension: metrics_id
    hidden: true
    sql: ${TABLE}.metrics_id
  - dimension: password
    hidden: true
    sql: ${TABLE}.password
  - dimension: phone_number
    sql: ${TABLE}.phone_number
  - dimension: picture
    sql: ${TABLE}.picture
  - dimension: plan_id
    type: int
    hidden: true
    sql: ${TABLE}.plan_id
  - dimension: stripe_customer_id
    hidden: true
    sql: ${TABLE}.stripe_customer_id
  - dimension: is_active
    type: yesno
    sql: ${TABLE}.suspended_at IS NULL OR ${TABLE}.suspended_at > NOW() AT TIME ZONE 'utc'
  - dimension: history
    sql: ${TABLE}.id
  - dimension: has_entry
    type: yesno
    sql: EXISTS(
  SELECT me.*
  FROM meal_entry AS me
  LEFT JOIN meal AS m ON me.meal_id = m.id
  WHERE ${TABLE}.id = m.client_id)
  - dimension_group: birthday
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.birthday
  - dimension_group: confirmed
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.confirmed_at
  - dimension_group: created
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.created_at
  - dimension_group: current_login
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.current_login_at
  - dimension_group: last_login
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.last_login_at
  - dimension_group: suspended
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.suspended_at
  - measure: count
    type: count
    drill_fields:
    - detail*
  - measure: count_clients
    type: count
    drill_fields:
    - detail*
    filters:
    - roles_users.role_name: client
  - measure: count_active_clients
    type: count
    drill_fields:
    - detail*
    filters:
    - is_active: True
    - roles_users.role_name: client
  - measure: count_coaches
    type: count
    drill_fields:
    - detail*
    filters:
    - roles_users.role_name: coach
  - measure: count_has_entry
    type: count
    drill_fields:
    - detail*
    filters:
    - has_entry: yes
  - measure: percent_clients
    type: number
    sql: 100.0 * ${count_clients}/NULLIF(${count_coaches}+${count_clients},0)
  - measure: percent_active_clients
    type: number
    sql: 100.0 * ${count_active_clients}/NULLIF(${count},0)
  - measure: percent_coaches
    type: number
    sql: 100.0 * ${count_coaches}/NULLIF(${count_coaches}+${count_clients},0)
  - measure: total_estimated_ltv
    type: sum
    sql: ${estimated_ltv}
  - measure: avg_estimated_ltv
    type: avg
    sql: ${estimated_ltv}
  - measure: avg_days_on_platform
    type: avg
    sql: ${days_on_platform}
  - measure: percent_of_total_users
    type: percent_of_total
    sql: ${count}
  - measure: percent_of_total_coaches
    type: percent_of_total
    sql: ${count_coaches}
  - measure: percent_of_total_clients
    type: percent_of_total
    sql: ${count_clients}