Orders

view: orders {
  sql_table_name: GENERIC_ORDERS ;;

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

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

  dimension: total_amount_of_order_usd {
    type: number
    sql: (SELECT SUM(order_items.sale_price)
FROM order_items
WHERE order_items.order_id = orders.id) ;;
  }

  dimension: total_amount_of_order_usd_tier {
    type: tier
    sql: ${total_amount_of_order_usd} ;;
  }

  dimension: user_id {
    type: int
    hidden: yes
  }

  dimension: month_text {
  }

  dimension: user_order_month_normalized {
    type: number
    sql: 12* (YEAR(${TABLE}.created_at)  - YEAR(${users.created_date}))  + MONTH(${TABLE}.created_at) - MONTH(${users.created_date}) ;;
  }

  dimension: is_same_dow_as_today {
    type: yesno
    sql: DAYOFWEEK(${TABLE}.created_at) = DAYOFWEEK(current_timestamp) ;;
  }

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

  measure: sum_total_amount_of_order_usd {
    type: sum
    sql: ${total_amount_of_order_usd} ;;
  }

  measure: average_total_amount_of_order_usd {
    type: average
    sql: ${total_amount_of_order_usd} ;;
  }

  measure: this_week_count {
    type: count_distinct
    filters: [created_date: "7 days"]
    drill_fields: [detail]
    sql: ${TABLE}.id ;;
  }

  measure: this_last_14_days_count {
    type: count_distinct
    filters: [created_date: "14 days"]
    drill_fields: [detail]
    sql: ${TABLE}.id ;;
  }

  measure: this_last_30_days_count {
    type: count_distinct
    filters: [created_date: "30 days"]
    drill_fields: [detail]
    sql: ${TABLE}.id ;;
  }

  measure: count {
    type: count_distinct
    drill_fields: [detail*]
    sql: ${TABLE}.id ;;
  }

  measure: order_percent_change {
    type: percent_of_previous
    sql: ${count} ;;
  }

  measure: count_percent_of_total {
    label: "Count (Percent of Total)"
    type: percent_of_total
    drill_fields: [detail*]
    sql: ${count} ;;
  }
}

- view: orders
  sql_table_name: GENERIC_ORDERS
  fields:
  - dimension: id
    type: int
    primary_key: true
    sql: ${TABLE}.id
  - dimension: status
    sql: ${TABLE}.status
  - dimension: total_amount_of_order_usd
    type: number
    sql: (SELECT SUM(order_items.sale_price)
FROM order_items
WHERE order_items.order_id = orders.id)
  - dimension: total_amount_of_order_usd_tier
    type: tier
    sql: ${total_amount_of_order_usd}
  - dimension: user_id
    type: int
    hidden: true
  - dimension: month_text
  - dimension: user_order_month_normalized
    type: number
    sql: 12* (YEAR(${TABLE}.created_at)  - YEAR(${users.created_date}))  + MONTH(${TABLE}.created_at) - MONTH(${users.created_date})
  - dimension: is_same_dow_as_today
    type: yesno
    sql: DAYOFWEEK(${TABLE}.created_at) = DAYOFWEEK(current_timestamp)
  - dimension_group: created
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - month_num
    - year
    - day_of_week_index
    - hour_of_day
    - minute5
    sql: ${TABLE}.created_at
  - measure: sum_total_amount_of_order_usd
    type: sum
    sql: ${total_amount_of_order_usd}
  - measure: average_total_amount_of_order_usd
    type: average
    sql: ${total_amount_of_order_usd}
  - measure: this_week_count
    type: count_distinct
    drill_fields:
    - detail
    filters:
    - created_date: 7 days
    sql: ${TABLE}.id
  - measure: this_last_14_days_count
    type: count_distinct
    drill_fields:
    - detail
    filters:
    - created_date: 14 days
    sql: ${TABLE}.id
  - measure: this_last_30_days_count
    type: count_distinct
    drill_fields:
    - detail
    filters:
    - created_date: 30 days
    sql: ${TABLE}.id
  - measure: count
    type: count_distinct
    drill_fields:
    - detail*
    sql: ${TABLE}.id
  - measure: order_percent_change
    type: percent_of_previous
    sql: ${count}
  - measure: count_percent_of_total
    type: percent_of_total
    label: Count (Percent of Total)
    drill_fields:
    - detail*
    sql: ${count}