Orders

view: orders {
  sql_table_name: public.orders ;;

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

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

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

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

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

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

  dimension: platform {
    sql: (case when ${TABLE}.platform like 'i%' then 'iOS' when ${TABLE}.platform like 'a%' then 'Android' when ${TABLE}.platform like 'd%' then 'Desktop' else NULL end) ;;
  }

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

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

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

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

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

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

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

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

  dimension: is_successfully_placed {
    type: yesno
    sql: ${TABLE}.state not in ('new', 'abandoned', 'declined', 'failed') ;;
  }

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

  measure: count {
    type: count
    drill_fields: [id, users.id, users.username]
  }

  measure: unique_users_count {
    type: count_distinct
    sql: ${TABLE}.user_id ;;
  }

  measure: revenue {
    type: sum
    value_format: "$#,##0.00"
    sql: ${total_cents} / 100.0 ;;
  }

  measure: aov {
    type: avg
    value_format: "$#,##0.00"
    sql: ${total_cents} / 100.0 ;;
  }

  measure: avg_shipment_days {
    type: avg
    sql: ${order_shipment_dates.shipped_date} - ${placed_date} ;;
  }

  measure: avg_delivery_days {
    type: avg
    sql: ${order_delivery_dates.delivered_date} - ${placed_date} ;;
  }
}

- view: orders
  sql_table_name: public.orders
  fields:
  - dimension: id
    type: int
    primary_key: true
    sql: ${TABLE}.id
  - dimension: assigned_admin_id
    type: int
    sql: ${TABLE}.assigned_admin_id
  - dimension: error_type
    sql: ${TABLE}.error_type
  - dimension: ip
    sql: ${TABLE}.ip
  - dimension: order_currency
    sql: ${TABLE}.order_currency
  - dimension: payment_profile_id
    type: int
    sql: ${TABLE}.payment_profile_id
  - dimension: platform
    sql: (case when ${TABLE}.platform like 'i%' then 'iOS' when ${TABLE}.platform like 'a%' then 'Android' when ${TABLE}.platform like 'd%' then 'Desktop' else NULL end)
  - dimension: pricing_model_type
    sql: ${TABLE}.pricing_model_type
  - dimension: service_fee_cents
    type: int
    sql: ${TABLE}.service_fee_cents
  - dimension: shipping_cents
    type: int
    sql: ${TABLE}.shipping_cents
  - dimension: state
    sql: ${TABLE}.state
  - dimension: sub_total_cents
    type: int
    sql: ${TABLE}.sub_total_cents
  - dimension: tax_cents
    type: int
    sql: ${TABLE}.tax_cents
  - dimension: total_cents
    type: int
    sql: ${TABLE}.total_cents
  - dimension: user_id
    type: int
    sql: ${TABLE}.user_id
  - dimension: is_successfully_placed
    type: yesno
    sql: ${TABLE}.state not in ('new', 'abandoned', 'declined', 'failed')
  - dimension_group: placed
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - year
    sql: ${TABLE}.placed_at
  - measure: count
    type: count
    drill_fields:
    - id
    - users.id
    - users.username
  - measure: unique_users_count
    type: count_distinct
    sql: ${TABLE}.user_id
  - measure: revenue
    type: sum
    value_format: '$#,##0.00'
    sql: ${total_cents} / 100.0
  - measure: aov
    type: avg
    value_format: '$#,##0.00'
    sql: ${total_cents} / 100.0
  - measure: avg_shipment_days
    type: avg
    sql: ${order_shipment_dates.shipped_date} - ${placed_date}
  - measure: avg_delivery_days
    type: avg
    sql: ${order_delivery_dates.delivered_date} - ${placed_date}