Orders

view: orders {
  sql_table_name: flexdb.orders ;;

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

  dimension: is_test {
    type: yesno
    sql: ${TABLE}.is_test ;;
  }

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

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

  dimension: quantity {
    type: number
    sql: greatest(
      ifnull(
        (
          select sum(quantity)
          from order_details od
          where od.order_id = ${TABLE}.order_id
        )
        , 0
      )
      , ${TABLE}.quantity
    ) ;;
  }

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

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

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

  dimension: order_total {
    type: number
    value_format: "#0.00"
    sql: ${TABLE}.price ;;
  }

  dimension: order_total_with_shipping {
    type: number
    value_format: "#0.00"
    sql: ${TABLE}.price + ${TABLE}.shipping ;;
  }

  dimension: total_amount_of_order_tier {
    description: "puts the total amount of orders into a tier."
    type: tier
    sql: ${order_total} ;;
  }

  dimension: tax {
    type: number
    value_format: "#0.00"
    sql: ${TABLE}.tax ;;
  }

  dimension: shipping {
    type: number
    value_format: "#0.00"
    sql: ${TABLE}.shipping ;;
  }

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

  dimension: next_bill {
    type: time
    sql: ${TABLE}.next_bill ;;
  }

  dimension: next_ship {
    type: time
    sql: ${TABLE}.next_ship ;;
  }

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

  dimension: date_canceled {
    type: time
    sql: ${TABLE}.date_canceled ;;
  }

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

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

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

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

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

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

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

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

  dimension: shipped {
    type: yesno
    sql: ${TABLE}.shipped ;;
  }

  dimension: is_complete {
    type: yesno
    sql: ${TABLE}.is_complete ;;
  }

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

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

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

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

  dimension: cycle {
    type: number
    sql: (SELECT COUNT(*)
FROM orders o
WHERE o.lead_id=${TABLE}.lead_id
AND NOT o.status in ('Declined','Hard Declined','Discarded','Pending','Voided')
AND o.date_ordered <= ${TABLE}.date_ordered
AND o.extended_type = 'Repeater') ;;
  }

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

  dimension: order_sequence_number {
    type: number
    sql: (SELECT COUNT(*)
FROM orders o
WHERE o.order_id < ${TABLE}.order_id
AND o.lead_id=${TABLE}.lead_id) + 1 ;;
  }

  dimension: is_first_purchase {
    type: yesno
    sql: ${order_sequence_number} = 1 ;;
  }

  dimension: days_til_first_cancel {
    type: int
    sql: ROUND((${TABLE}.date_canceled - ${TABLE}.date_ordered)/86400) ;;
  }

  dimension: is_active {
    type: yesno
    sql: (CASE WHEN ${TABLE}.status = 'Shipped' THEN 1
     WHEN ${TABLE}.status = 'Awaiting Shipment' THEN 1
     ELSE 0
END) ;;
  }

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

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

  measure: average_total_amount_of_order {
    type: average
    value_format: "$#,##0.00"
    sql: ${order_total} ;;
  }

  measure: total_first_purchase_revenue {
    type: sum
    value_format: "$#,##0.00"
    filters: [is_first_purchase: "True"]
    sql: ${order_total} ;;
  }

  measure: sum_total_amount_of_order {
    type: sum
    value_format: "$#,##0.00"
    sql: ${order_total} ;;
  }

  measure: sum_total_amount_of_shipping {
    type: sum
    value_format: "$#,##0.00"
    sql: ${shipping} ;;
  }

  measure: sum_of_order_and_shipping {
    type: sum
    value_format: "$#,##0.00"
    sql: ${order_total} + ${shipping} ;;
  }
}

- view: orders
  sql_table_name: flexdb.orders
  fields:
  - dimension: order_id
    type: int
    primary_key: true
    sql: ${TABLE}.order_id
  - dimension: is_test
    type: yesno
    sql: ${TABLE}.is_test
  - dimension: lead_id
    type: int
    sql: ${TABLE}.lead_id
  - dimension: product_id
    type: int
    sql: ${TABLE}.product_id
  - dimension: quantity
    type: number
    sql: greatest(
      ifnull(
        (
          select sum(quantity)
          from order_details od
          where od.order_id = ${TABLE}.order_id
        )
        , 0
      )
      , ${TABLE}.quantity
    )
  - dimension: retail_products
    sql: ${TABLE}.retail_products
  - dimension: onetime_skus
    sql: ${TABLE}.onetime_skus
  - dimension: recur_skus
    sql: ${TABLE}.recur_skus
  - dimension: order_total
    type: number
    value_format: '#0.00'
    sql: ${TABLE}.price
  - dimension: order_total_with_shipping
    type: number
    value_format: '#0.00'
    sql: ${TABLE}.price + ${TABLE}.shipping
  - dimension: total_amount_of_order_tier
    type: tier
    description: puts the total amount of orders into a tier.
    sql: ${order_total}
  - dimension: tax
    type: number
    value_format: '#0.00'
    sql: ${TABLE}.tax
  - dimension: shipping
    type: number
    value_format: '#0.00'
    sql: ${TABLE}.shipping
  - dimension: type
    sql: ${TABLE}.type
  - dimension: next_bill
    type: time
    sql: ${TABLE}.next_bill
  - dimension: next_ship
    type: time
    sql: ${TABLE}.next_ship
  - dimension: ship_interval
    type: number
    sql: ${TABLE}.ship_interval
  - dimension: date_canceled
    type: time
    sql: ${TABLE}.date_canceled
  - dimension: date_rma
    sql: ${TABLE}.date_rma
  - dimension: date_reactivated
    sql: ${TABLE}.date_reactivated
  - dimension: date_extended
    sql: ${TABLE}.date_extended
  - dimension: date_restarted
    sql: ${TABLE}.date_restarted
  - dimension: reason
    sql: ${TABLE}.reason
  - dimension: cancel_notes
    sql: ${TABLE}.cancel_notes
  - dimension: status
    sql: ${TABLE}.status
  - dimension: source
    sql: ${TABLE}.source
  - dimension: shipped
    type: yesno
    sql: ${TABLE}.shipped
  - dimension: is_complete
    type: yesno
    sql: ${TABLE}.is_complete
  - dimension: selldown_id
    type: number
    sql: ${TABLE}.selldown_id
  - dimension: extended_type
    sql: ${TABLE}.extended_type
  - dimension: from_reattempt
    type: number
    sql: ${TABLE}.from_reattempt
  - dimension: can_ignore
    type: number
    sql: ${TABLE}.can_ignore
  - dimension: cycle
    type: number
    sql: (SELECT COUNT(*)
FROM orders o
WHERE o.lead_id=${TABLE}.lead_id
AND NOT o.status in ('Declined','Hard Declined','Discarded','Pending','Voided')
AND o.date_ordered <= ${TABLE}.date_ordered
AND o.extended_type = 'Repeater')
  - dimension: has_recurring_upsell
    sql: ${TABLE}.has_recurring_upsell
  - dimension: order_sequence_number
    type: number
    sql: (SELECT COUNT(*)
FROM orders o
WHERE o.order_id < ${TABLE}.order_id
AND o.lead_id=${TABLE}.lead_id) + 1
  - dimension: is_first_purchase
    type: yesno
    sql: ${order_sequence_number} = 1
  - dimension: days_til_first_cancel
    type: int
    sql: ROUND((${TABLE}.date_canceled - ${TABLE}.date_ordered)/86400)
  - dimension: is_active
    type: yesno
    sql: (CASE WHEN ${TABLE}.status = 'Shipped' THEN 1
     WHEN ${TABLE}.status = 'Awaiting Shipment' THEN 1
     ELSE 0
END)
  - dimension_group: date_ordered
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    datatype: epoch
    sql: ${TABLE}.date_ordered
  - measure: count
    type: count
    drill_fields:
    - detail*
  - measure: average_total_amount_of_order
    type: average
    value_format: '$#,##0.00'
    sql: ${order_total}
  - measure: total_first_purchase_revenue
    type: sum
    value_format: '$#,##0.00'
    filters:
    - is_first_purchase: True
    sql: ${order_total}
  - measure: sum_total_amount_of_order
    type: sum
    value_format: '$#,##0.00'
    sql: ${order_total}
  - measure: sum_total_amount_of_shipping
    type: sum
    value_format: '$#,##0.00'
    sql: ${shipping}
  - measure: sum_of_order_and_shipping
    type: sum
    value_format: '$#,##0.00'
    sql: ${order_total} + ${shipping}