Orders

view: orders {
  sql_table_name: spree_orders ;;

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

  dimension: adjustment_amount {
    type: number
    sql: ${TABLE}.adjustment_total ;;
  }

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

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

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

  dimension: is_completed {
    type: yesno
    sql: ${TABLE}.state = 'complete' ;;
  }

  dimension: is_revenue_order {
    type: yesno
    sql: ${TABLE}.state in ('complete','awaiting_return','returned','partially_returned','canceled','resumed') ;;
  }

  dimension: is_to_be_fulfilled_order {
    type: yesno
    sql: ${TABLE}.state in ('complete','awaiting_return','returned','partially_returned','resumed') ;;
  }

  dimension: am_or_pm {
    label: "AM or PM"
    sql: CASE WHEN ${created_hod} < 12 THEN "AM" ELSE "PM" END ;;
  }

  dimension: is_same_dow_as_today {
    type: yesno
    sql: dayofweek(${created_date}) = dayofweek(now()) ;;
  }

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

  dimension: is_first_purchase {
    type: yesno
    sql: (SELECT count(*) = 0
FROM spree_orders o
WHERE o.user_id = ${TABLE}.user_id
AND o.created_at < ${TABLE}.created_at) ;;
  }

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

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

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

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

  dimension: payment_status {
    sql: ${TABLE}.payment_state ;;
  }

  dimension: payment_amount {
    type: number
    sql: ${TABLE}.payment_total ;;
  }

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

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

  dimension: shipment_status {
    sql: ${TABLE}.shipment_state ;;
  }

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

  dimension: order_amount {
    type: number
    sql: ${TABLE}.total ;;
  }

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

  dimension_group: completed {
    type: time
    timeframes: [date]
    sql: ${TABLE}.completed_at ;;
  }

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

  measure: total_adjustment_amount {
    type: sum
    sql: ${adjustment_amount} ;;
  }

  measure: first_purchase_count {
    type: count_distinct
    filters: [is_first_purchase: "True"]
    sql: ${id} ;;
  }

  measure: percent_first_purchase {
    type: number
    sql: 100.0 * ${first_purchase_count}/IFNULL(${count},0) ;;
  }

  measure: repeat_purchase_count {
    type: count_distinct
    filters: [is_first_purchase: "False"]
    sql: ${id} ;;
  }

  measure: percent_repeat_purchase {
    type: number
    sql: 100.0 * ${repeat_purchase_count}/IFNULL(${count},0) ;;
  }

  measure: total_payment_amount {
    type: sum
    sql: ${payment_amount} ;;
  }

  measure: total_order_amount {
    type: sum
    sql: ${order_amount} ;;
  }

  measure: total_merchandise_amount {
    type: sum
    sql: ${order_amount} - ${adjustment_amount} ;;
  }

  measure: average_order_amount {
    type: average
    sql: ${order_amount} ;;
  }

  measure: count {
    type: count
  }
}

- view: orders
  sql_table_name: spree_orders
  fields:
  - dimension: id
    type: int
    primary_key: true
    sql: ${TABLE}.id
  - dimension: adjustment_amount
    type: number
    sql: ${TABLE}.adjustment_total
  - dimension: auspient_id
    type: int
    hidden: true
    sql: ${TABLE}.auspient_id
  - dimension: bill_address_id
    type: int
    hidden: true
    sql: ${TABLE}.bill_address_id
  - dimension: cancel_reason
    sql: ${TABLE}.cancel_reason
  - dimension: is_completed
    type: yesno
    sql: ${TABLE}.state = 'complete'
  - dimension: is_revenue_order
    type: yesno
    sql: ${TABLE}.state in ('complete','awaiting_return','returned','partially_returned','canceled','resumed')
  - dimension: is_to_be_fulfilled_order
    type: yesno
    sql: ${TABLE}.state in ('complete','awaiting_return','returned','partially_returned','resumed')
  - dimension: am_or_pm
    label: AM or PM
    sql: CASE WHEN ${created_hod} < 12 THEN "AM" ELSE "PM" END
  - dimension: is_same_dow_as_today
    type: yesno
    sql: dayofweek(${created_date}) = dayofweek(now())
  - dimension: email
    sql: ${TABLE}.email
  - dimension: is_first_purchase
    type: yesno
    sql: (SELECT count(*) = 0
FROM spree_orders o
WHERE o.user_id = ${TABLE}.user_id
AND o.created_at < ${TABLE}.created_at)
  - dimension: item_total
    type: number
    sql: ${TABLE}.item_total
  - dimension: notes
    sql: ${TABLE}.notes
  - dimension: number
    sql: ${TABLE}.number
  - dimension: other_reason
    sql: ${TABLE}.other_reason
  - dimension: payment_status
    sql: ${TABLE}.payment_state
  - dimension: payment_amount
    type: number
    sql: ${TABLE}.payment_total
  - dimension: reason_for_intervention
    sql: ${TABLE}.reason_for_intervention
  - dimension: ship_address_id
    type: int
    hidden: true
    sql: ${TABLE}.ship_address_id
  - dimension: shipment_status
    sql: ${TABLE}.shipment_state
  - dimension: status
    sql: ${TABLE}.state
  - dimension: order_amount
    type: number
    sql: ${TABLE}.total
  - dimension: user_id
    type: int
    sql: ${TABLE}.user_id
  - dimension_group: completed
    type: time
    timeframes:
    - date
    sql: ${TABLE}.completed_at
  - dimension_group: created
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - dow
    - dow_num
    - hod
    - hour
    sql: ${TABLE}.created_at
  - measure: total_adjustment_amount
    type: sum
    sql: ${adjustment_amount}
  - measure: first_purchase_count
    type: count_distinct
    filters:
    - is_first_purchase: True
    sql: ${id}
  - measure: percent_first_purchase
    type: number
    sql: 100.0 * ${first_purchase_count}/IFNULL(${count},0)
  - measure: repeat_purchase_count
    type: count_distinct
    filters:
    - is_first_purchase: False
    sql: ${id}
  - measure: percent_repeat_purchase
    type: number
    sql: 100.0 * ${repeat_purchase_count}/IFNULL(${count},0)
  - measure: total_payment_amount
    type: sum
    sql: ${payment_amount}
  - measure: total_order_amount
    type: sum
    sql: ${order_amount}
  - measure: total_merchandise_amount
    type: sum
    sql: ${order_amount} - ${adjustment_amount}
  - measure: average_order_amount
    type: average
    sql: ${order_amount}
  - measure: count
    type: count