view: messages_user_last {
  # Or, you could make this view a derived table, like this:
  derived_table: {
    sql: WITH messages AS (
        SELECT
          m_id,
          date,
          botSlug,
          author,
          reader,
          status
        FROM
          `analytics.incoming_2019_05_*`
        WHERE
          m_type != 'watermark' ),
        conv AS (
        SELECT
          *,
          RANK() OVER (PARTITION BY botSlug, IF(author.role = 'user', author.id, reader)
          ORDER BY
            date DESC) AS rank,
          LOWER(currentState) IN ('humanloop', 'human-loop', 'human-mode') is_in_humanloop
        FROM
          messages i
        LEFT JOIN
          `analytics.bot_states_2019_05_*` bs
        ON
          (i.m_id = bs.messageId) )
      SELECT
        *
      FROM
        conv
      WHERE
        status = 'RECEIVED'
        AND rank = 1
        AND DATETIME_DIFF('2019-05-04 00:00:00',
          DATETIME(date),
          MINUTE) >= 60
        AND date BETWEEN '2019-05-03 00:00:00'
        AND '2019-05-04 00:00:00'
      ;;
  }

  dimension: m_id {
    primary_key: yes
    description: "Unique ID for each message"
    label: "Id"
    type: string
    sql: ${TABLE}.m_id ;;
  }

  dimension_group: date {
    type: time
    timeframes: [
      raw,
      time,
      date,
      week,
      month,
      quarter,
      year,
      hour
    ]
    sql: ${TABLE}.date ;;
    label: "Date"
  }

  dimension: bot_slug {
    type: string
    sql: ${TABLE}.botSlug ;;
    label: "Bot Slug"
  }

  dimension: author {
    hidden: yes
    sql: ${TABLE}.author ;;
    label: "Author"
  }

  dimension: reader {
    type: string
    sql: ${TABLE}.reader ;;
    label: "Reader"
  }

  dimension: status {
    type: string
    sql: ${TABLE}.status ;;
    label: "Status"
  }

  dimension: rank {
    type: number
    sql: ${TABLE}.rank ;;
    label: "Rank"
  }

  dimension: is_in_humanloop {
    type: yesno
    sql: ${TABLE}.is_in_humanloop ;;
    label: "Is in humanloop"
  }

  measure: count_all {
    description: "Use this for counting lifetime orders across many users"
    type: count
#     sql: ${lifetime_orders} ;;
    label: "Count"
  }
}

view: messages_user_last__author {
  dimension: id {
    primary_key: yes
    type: string
    sql: ${TABLE}.id ;;
    label: "Author Id"
  }

  dimension: role {
    type: string
    sql: ${TABLE}.role ;;
    label: "Author Role"
  }
}