Cart Item Fact

view: cart_item_fact {
  sql_table_name: warehouse.cart_item_fact ;;

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

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

  dimension: add_to_cart_session_id {
    hidden: yes
    sql: ${TABLE}.add_to_cart_session_id ;;
  }

  dimension: add_to_cart_session_id {
    hidden: yes
    sql: ${TABLE}.add_to_cart_session_id ;;
  }

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

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

  dimension: checkout_week {
    sql: TO_CHAR(cart_item_fact.checkout_dt - EXTRACT(DOW FROM cart_item_fact.checkout_dt) * INTERVAL '1 days', 'YYYY-MM-DD') ;;
  }

  dimension: checkout_session_id {
    hidden: yes
    sql: ${TABLE}.checkout_session_id ;;
  }

  dimension: checkout_session_id {
    hidden: yes
    sql: ${TABLE}.checkout_session_id ;;
  }

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

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

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

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

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

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

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

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

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

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

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

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

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

  dimension: quantity_dim {
    type: number
    hidden: yes
    sql: ${TABLE}.quantity ;;
  }

  dimension: revenue_dim {
    type: number
    hidden: yes
    sql: ${TABLE}.revenue ;;
  }

  dimension: revenue_minus_vat_dim {
    type: number
    hidden: yes
    sql: ${TABLE}.revenue_minus_vat ;;
  }

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

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

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

  dimension_group: add_to_cart {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.add_to_cart_dt ;;
  }

  dimension_group: checkout {
    type: time
    timeframes: [time, date, month, year, hod, dow_num]
    sql: ${TABLE}.checkout_dt ;;
  }

  measure: item_count {
    type: count
  }

  measure: new_buyer_count {
    type: count_distinct
    sql: ${user_id} ;;
  }

  measure: buyer_count {
    type: count
    sql: ${user_id} ;;
  }

  measure: revenue {
    type: sum
    sql: ${revenue_minus_vat_dim} ;;
  }

  measure: avg_cart_revenue {
    type: number
    sql: 1.00*SUM(${revenue_minus_vat_dim})/COUNT(DISTINCT ${cart_fact_id}) ;;
  }

  measure: avg_sale_price {
    type: average
    sql: ${revenue_minus_vat_dim} ;;
  }

  measure: quantity {
    type: sum
    sql: ${quantity_dim} ;;
  }
}

- view: cart_item_fact
  sql_table_name: warehouse.cart_item_fact
  fields:
  - dimension: cart_item_fact_id
    type: int
    primary_key: true
    hidden: true
    sql: ${TABLE}.cart_item_fact_id
  - dimension: add_to_cart_clickstream_id
    type: int
    hidden: true
    sql: ${TABLE}.add_to_cart_clickstream_id
  - dimension: add_to_cart_session_id
    hidden: true
    sql: ${TABLE}.add_to_cart_session_id
  - dimension: add_to_cart_session_id
    hidden: true
    sql: ${TABLE}.add_to_cart_session_id
  - dimension: cart_fact_id
    type: int
    hidden: true
    sql: ${TABLE}.cart_fact_id
  - dimension: checkout_clickstream_id
    type: int
    hidden: true
    sql: ${TABLE}.checkout_clickstream_id
  - dimension: checkout_week
    sql: TO_CHAR(cart_item_fact.checkout_dt - EXTRACT(DOW FROM cart_item_fact.checkout_dt) * INTERVAL '1 days', 'YYYY-MM-DD')
  - dimension: checkout_session_id
    hidden: true
    sql: ${TABLE}.checkout_session_id
  - dimension: checkout_session_id
    hidden: true
    sql: ${TABLE}.checkout_session_id
  - dimension: course_offer_id
    type: int
    hidden: true
    sql: ${TABLE}.course_offer_id
  - dimension: course_or_ecomm
    sql: ${TABLE}.course_or_ecomm
  - dimension: customer_order_id
    type: int
    hidden: true
    sql: ${TABLE}.customer_order_id
  - dimension: deal_offer_id
    type: int
    hidden: true
    sql: ${TABLE}.deal_offer_id
  - dimension: external_link_id
    type: int
    hidden: true
    sql: ${TABLE}.external_link_id
  - dimension: is_gift
    type: yesno
    sql: ${TABLE}.is_gift
  - dimension: is_redemption
    type: yesno
    sql: ${TABLE}.is_redemption
  - dimension: is_shippable
    type: yesno
    sql: ${TABLE}.is_shippable
  - dimension: product_for_sale_action_id
    type: int
    hidden: true
    sql: ${TABLE}.product_for_sale_action_id
  - dimension: product_fulfillment_id
    type: int
    hidden: true
    sql: ${TABLE}.product_fulfillment_id
  - dimension: product_fulfillment_item_id
    type: int
    hidden: true
    sql: ${TABLE}.product_fulfillment_item_id
  - dimension: product_id
    type: int
    hidden: true
    sql: ${TABLE}.product_id
  - dimension: promotional_offer_id
    type: int
    hidden: true
    sql: ${TABLE}.promotional_offer_id
  - dimension: quantity_dim
    type: number
    hidden: true
    sql: ${TABLE}.quantity
  - dimension: revenue_dim
    type: number
    hidden: true
    sql: ${TABLE}.revenue
  - dimension: revenue_minus_vat_dim
    type: number
    hidden: true
    sql: ${TABLE}.revenue_minus_vat
  - dimension: uber_offer_id
    type: int
    hidden: true
    sql: ${TABLE}.uber_offer_id
  - dimension: user_id
    type: int
    hidden: true
    sql: ${TABLE}.user_id
  - dimension: workshop_offer_id
    type: int
    hidden: true
    sql: ${TABLE}.workshop_offer_id
  - dimension_group: add_to_cart
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.add_to_cart_dt
  - dimension_group: checkout
    type: time
    timeframes:
    - time
    - date
    - month
    - year
    - hod
    - dow_num
    sql: ${TABLE}.checkout_dt
  - measure: item_count
    type: count
  - measure: new_buyer_count
    type: count_distinct
    sql: ${user_id}
  - measure: buyer_count
    type: count
    sql: ${user_id}
  - measure: revenue
    type: sum
    sql: ${revenue_minus_vat_dim}
  - measure: avg_cart_revenue
    type: number
    sql: 1.00*SUM(${revenue_minus_vat_dim})/COUNT(DISTINCT ${cart_fact_id})
  - measure: avg_sale_price
    type: average
    sql: ${revenue_minus_vat_dim}
  - measure: quantity
    type: sum
    sql: ${quantity_dim}