Simple Variants

view: simple_variants {
  sql_table_name: spree_variants ;;

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

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

  dimension: is_sold {
    type: yesno
    sql: (SELECT o.state in ('complete','resumed')
FROM spree_line_items li
LEFT JOIN spree_orders o on o.id = li.order_id
WHERE ${TABLE}.id = li.variant_id
ORDER BY o.created_at DESC
LIMIT 1) ;;
  }

  dimension: price_sold {
    type: number
    sql: (SELECT li.price
FROM spree_line_items li
LEFT JOIN spree_orders o on o.id = li.order_id
WHERE ${TABLE}.id = li.variant_id and o.state in ('complete','resumed')
ORDER BY o.created_at DESC
LIMIT 1) ;;
  }

  dimension: is_demand_sold {
    type: yesno
    sql: (SELECT o.state
IN ('manual_intervention','resumed','complete','awaiting_return','partially_returned','returned','canceled')
FROM spree_line_items li
LEFT JOIN spree_orders o on o.id = li.order_id
WHERE ${TABLE}.id = li.variant_id
ORDER BY o.created_at DESC
LIMIT 1) ;;
  }

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  dimension: age_in_days {
    type: number
    sql: DATEDIFF(now(),${born_date}) ;;
  }

  dimension: age_in_days_tier {
    type: tier
    sql: ${age_in_days} ;;
  }

  dimension: taxons {
    sql: (SELECT GROUP_CONCAT(DISTINCT t.name ORDER BY t.name DESC SEPARATOR ', ' )
FROM store_production.spree_products_taxons AS pt
JOIN store_production.spree_taxons AS t
ON pt.taxon_id = t.id
WHERE pt.product_id = ${TABLE}.product_id
) ;;
  }

  dimension: permalink {
    sql: (SELECT t.permalink
FROM (select product_id, min(taxon_id) as min_taxon_id from store_production.spree_products_taxons group by product_id) as pt
JOIN store_production.spree_taxons as t
ON pt.min_taxon_id = t.id
WHERE pt.product_id = ${TABLE}.product_id
  ) ;;
  }

  dimension_group: born {
    type: time
    timeframes: [yesno, date, week, month]
    sql: ${TABLE}.born_date ;;
  }

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

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

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

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

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

  measure: count {
    type: count
  }

  measure: count_skued {
    type: count
    filters: [is_master: "False"]
  }

  measure: count_tracked {
    type: count
    filters: [shipments.is_tracked: "True"]
  }

  measure: count_shipped {
    type: count
    filters: [shipments.is_shipped: "True"]
  }

  measure: percent_shipped {
    type: number
    sql: 100.0 * ${count_shipped} / nullif(${count_sold},0) ;;
  }

  measure: count_sold {
    type: count
    filters: [is_sold: "True"]
  }

  measure: percent_sold {
    type: number
    sql: 100.0 * ${count_sold} / nullif(${count_skued},0) ;;
  }

  measure: count_demand_sold {
    type: count
    filters: [is_demand_sold: "True"]
  }

  measure: percent_demand_sold {
    type: number
    sql: 100.0 * ${count_demand_sold} / nullif(${count_skued},0) ;;
  }

  measure: count_merchandised {
    type: count
    filters: [flash_sales_variants.is_merchandised: "True"]
  }

  measure: percent_sell_through {
    type: number
    sql: 100.0 * ${count_sold} / nullif(${count_merchandised},0) ;;
  }

  measure: percent_merchandised {
    type: number
    sql: 100.0 * ${count_merchandised} / nullif(${count_skued},0) ;;
  }

  measure: count_returned {
    type: count
    filters: [inventory_units.is_returned: "True"]
  }

  measure: sales_before_discount {
    label: "Total Sales Revenue Before Discount"
    type: sum
    sql: ${price_sold} ;;
  }
}

- view: simple_variants
  sql_table_name: spree_variants
  fields:
  - dimension: id
    type: int
    primary_key: true
    sql: ${TABLE}.id
  - dimension: bin
    sql: ${TABLE}.bin
  - dimension: is_sold
    type: yesno
    sql: (SELECT o.state in ('complete','resumed')
FROM spree_line_items li
LEFT JOIN spree_orders o on o.id = li.order_id
WHERE ${TABLE}.id = li.variant_id
ORDER BY o.created_at DESC
LIMIT 1)
  - dimension: price_sold
    type: number
    sql: (SELECT li.price
FROM spree_line_items li
LEFT JOIN spree_orders o on o.id = li.order_id
WHERE ${TABLE}.id = li.variant_id and o.state in ('complete','resumed')
ORDER BY o.created_at DESC
LIMIT 1)
  - dimension: is_demand_sold
    type: yesno
    sql: (SELECT o.state
IN ('manual_intervention','resumed','complete','awaiting_return','partially_returned','returned','canceled')
FROM spree_line_items li
LEFT JOIN spree_orders o on o.id = li.order_id
WHERE ${TABLE}.id = li.variant_id
ORDER BY o.created_at DESC
LIMIT 1)
  - dimension: condition_description
    sql: ${TABLE}.condition_description
  - dimension: consigner_id
    type: int
    hidden: true
    sql: ${TABLE}.consigner_id
  - dimension: cost_price
    type: number
    sql: ${TABLE}.cost_price
  - dimension: count_on_hand
    type: int
    sql: ${TABLE}.count_on_hand
  - dimension: depth
    type: number
    sql: ${TABLE}.depth
  - dimension: fabric_content
    sql: ${TABLE}.fabric_content
  - dimension: field_merchandiser_id
    type: int
    sql: ${TABLE}.field_merchandiser_id
  - dimension: foreign_size
    sql: ${TABLE}.foreign_size
  - dimension: has_discounts
    type: yesno
    sql: ${TABLE}.has_discounts
  - dimension: height
    type: number
    sql: ${TABLE}.height
  - dimension: hold_user_id
    type: int
    sql: ${TABLE}.hold_user_id
  - dimension: is_killed
    type: yesno
    sql: ${TABLE}.is_killed
  - dimension: is_master
    type: yesno
    sql: ${TABLE}.is_master
  - dimension: measurements
    sql: ${TABLE}.measurements
  - dimension: minimum_price
    type: number
    sql: ${TABLE}.minimum_price
  - dimension: notes
    sql: ${TABLE}.notes
  - dimension: original_price
    type: number
    sql: ${TABLE}.original_price
  - dimension: original_size
    sql: ${TABLE}.original_size
  - dimension: photos_available
    type: yesno
    sql: ${TABLE}.photos_available
  - dimension: position
    type: int
    sql: ${TABLE}.position
  - dimension: price
    type: number
    sql: ${TABLE}.price
  - dimension: product_id
    type: int
    sql: ${TABLE}.product_id
  - dimension: ready_for_sale
    type: yesno
    sql: ${TABLE}.ready_for_sale
  - dimension: sku
    sql: ${TABLE}.sku
  - dimension: check_price
    type: yesno
    sql: ${TABLE}.check_price
  - dimension: warehouse_id
    type: int
    hidden: true
    sql: ${TABLE}.warehouse_id
  - dimension: weight
    type: number
    sql: ${TABLE}.weight
  - dimension: width
    type: number
    sql: ${TABLE}.width
  - dimension: age_in_days
    type: number
    sql: DATEDIFF(now(),${born_date})
  - dimension: age_in_days_tier
    type: tier
    sql: ${age_in_days}
  - dimension: taxons
    sql: (SELECT GROUP_CONCAT(DISTINCT t.name ORDER BY t.name DESC SEPARATOR ', ' )
FROM store_production.spree_products_taxons AS pt
JOIN store_production.spree_taxons AS t
ON pt.taxon_id = t.id
WHERE pt.product_id = ${TABLE}.product_id
)
  - dimension: permalink
    sql: (SELECT t.permalink
FROM (select product_id, min(taxon_id) as min_taxon_id from store_production.spree_products_taxons group by product_id) as pt
JOIN store_production.spree_taxons as t
ON pt.min_taxon_id = t.id
WHERE pt.product_id = ${TABLE}.product_id
  )
  - dimension_group: born
    type: time
    timeframes:
    - yesno
    - date
    - week
    - month
    sql: ${TABLE}.born_date
  - dimension_group: created
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.created_at
  - dimension_group: born_date
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.born_date
  - dimension_group: deleted
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.deleted_at
  - dimension_group: killed
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.killed_at
  - dimension_group: updated
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.updated_at
  - measure: count
    type: count
  - measure: count_skued
    type: count
    filters:
    - is_master: False
  - measure: count_tracked
    type: count
    filters:
    - shipments.is_tracked: True
  - measure: count_shipped
    type: count
    filters:
    - shipments.is_shipped: True
  - measure: percent_shipped
    type: number
    sql: 100.0 * ${count_shipped} / nullif(${count_sold},0)
  - measure: count_sold
    type: count
    filters:
    - is_sold: True
  - measure: percent_sold
    type: number
    sql: 100.0 * ${count_sold} / nullif(${count_skued},0)
  - measure: count_demand_sold
    type: count
    filters:
    - is_demand_sold: True
  - measure: percent_demand_sold
    type: number
    sql: 100.0 * ${count_demand_sold} / nullif(${count_skued},0)
  - measure: count_merchandised
    type: count
    filters:
    - flash_sales_variants.is_merchandised: True
  - measure: percent_sell_through
    type: number
    sql: 100.0 * ${count_sold} / nullif(${count_merchandised},0)
  - measure: percent_merchandised
    type: number
    sql: 100.0 * ${count_merchandised} / nullif(${count_skued},0)
  - measure: count_returned
    type: count
    filters:
    - inventory_units.is_returned: True
  - measure: sales_before_discount
    type: sum
    label: Total Sales Revenue Before Discount
    sql: ${price_sold}