Item

view: item {
  sql_table_name: item_item ;;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  dimension: percent_discount {
    type: number
    sql: ${TABLE}.percentDiscount ;;
  }

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

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

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

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

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

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

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

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

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

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

  dimension: price_tiered {
    type: tier
    sql: ${price} ;;
  }

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  dimension: things_to_know {
    sql: ${TABLE}.thingsToKnow ;;
  }

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

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

  dimension: platform {
    sql: (CASE WHEN ${post_with} like '%iPhone%' then 'iOS' WHEN ${post_with} like '%iPad%' then 'iOS' WHEN ${post_with} like '%iPod%' then 'iOS' else 'Android' end) ;;
  }

  dimension: weeks_since_registration {
    type: number
    sql: (${post_date} - ${account_user_profile.created_date}) / 7 ;;
  }

  dimension: days_to_sell {
    type: number
    sql: ${sold_date} - ${post_date} ;;
  }

  dimension: weeks_to_sell {
    type: number
    sql: ${days_to_sell} / 7 ;;
  }

  dimension: description_length {
    type: tier
    sql: char_length(${description}) ;;
  }

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

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

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

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

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

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

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

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

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

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

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

  measure: count {
    type: count
    drill_fields: [id, post_from_store_name, location_name, category_id]
  }

  measure: value {
    type: sum
    sql: ${price} ;;
  }

  measure: count_owners {
    type: count_distinct
    sql: ${owner_id} ;;
  }

  measure: asp {
    type: number
    sql: AVG(${price}) ;;
  }

  measure: weekly_cohort_post_percent {
    type: number
    sql: 100 * ${count_owners}::float / NULLIF(AVG(${weekly_registration_cohorts.cohort_size}), 0) ;;
  }

  measure: count_percent_of_total {
    type: percent_of_total
    sql: ${count} ;;
  }
}

- view: item
  sql_table_name: item_item
  fields:
  - dimension: id
    type: int
    primary_key: true
    sql: ${TABLE}.id
  - dimension: accept_order
    type: number
    sql: ${TABLE}.accept_order
  - dimension: category_id
    type: int
    sql: ${TABLE}.category_id
  - dimension: condition
    type: number
    sql: ${TABLE}.condition
  - dimension: description
    sql: ${TABLE}.description
  - dimension: facebook_token
    sql: ${TABLE}.facebook_token
  - dimension: image
    sql: ${TABLE}.image
  - dimension: image_height
    type: number
    sql: ${TABLE}.image_height
  - dimension: image_phash
    sql: ${TABLE}.image_phash
  - dimension: image_width
    type: number
    sql: ${TABLE}.image_width
  - dimension: internal_transaction
    type: yesno
    sql: ${TABLE}.internal_transaction
  - dimension: latitude
    type: number
    sql: ${TABLE}.latitude
  - dimension: listing_type
    type: number
    sql: ${TABLE}.listing_type
  - dimension: location
    sql: ${TABLE}.location
  - dimension: location_name
    sql: ${TABLE}.location_name
  - dimension: longitude
    type: number
    sql: ${TABLE}.longitude
  - dimension: original_price
    type: number
    sql: ${TABLE}.originalPrice
  - dimension: owner_id
    type: int
    sql: ${TABLE}.owner_id
  - dimension: percent_discount
    type: number
    sql: ${TABLE}.percentDiscount
  - dimension: pickup_from_id
    type: int
    sql: ${TABLE}.pickup_from_id
  - dimension: pickup_paid
    type: int
    sql: ${TABLE}.pickup_paid
  - dimension: post_from_ip
    sql: ${TABLE}.post_from_ip
  - dimension: post_from_latitude
    type: number
    sql: ${TABLE}.post_from_latitude
  - dimension: post_from_longitude
    type: number
    sql: ${TABLE}.post_from_longitude
  - dimension: post_from_store_address
    sql: ${TABLE}.post_from_store_address
  - dimension: post_from_store_name
    sql: ${TABLE}.post_from_store_name
  - dimension: post_from_zipcode
    sql: ${TABLE}.post_from_zipcode
  - dimension: post_with
    sql: ${TABLE}.post_with
  - dimension: price
    type: number
    sql: ${TABLE}.price
  - dimension: price_tiered
    type: tier
    sql: ${price}
  - dimension: priority
    type: number
    sql: ${TABLE}.priority
  - dimension: quantity
    type: number
    sql: ${TABLE}.quantity
  - dimension: recommended
    type: yesno
    sql: ${TABLE}.recommended
  - dimension: reject_order
    type: number
    sql: ${TABLE}.reject_order
  - dimension: requires_shipping_address
    type: yesno
    sql: ${TABLE}.requires_shipping_address
  - dimension: reservable
    type: yesno
    sql: ${TABLE}.reservable
  - dimension: review_notes
    sql: ${TABLE}.review_notes
  - dimension: review_status
    type: number
    sql: ${TABLE}.review_status
  - dimension: review_user_id
    type: int
    sql: ${TABLE}.review_user_id
  - dimension: share_craigslist
    type: yesno
    sql: ${TABLE}.share_craigslist
  - dimension: share_facebook
    type: yesno
    sql: ${TABLE}.share_facebook
  - dimension: share_facebook_id
    sql: ${TABLE}.share_facebook_id
  - dimension: share_fanpage
    type: yesno
    sql: ${TABLE}.share_fanpage
  - dimension: share_fanpage_id
    sql: ${TABLE}.share_fanpage_id
  - dimension: share_pinterest
    type: yesno
    sql: ${TABLE}.share_pinterest
  - dimension: share_pinterest_id
    sql: ${TABLE}.share_pinterest_id
  - dimension: share_twitter
    type: yesno
    sql: ${TABLE}.share_twitter
  - dimension: share_twitter_id
    sql: ${TABLE}.share_twitter_id
  - dimension: sold_offer_id
    type: int
    sql: ${TABLE}.sold_offer_id
  - dimension: state
    type: number
    sql: ${TABLE}.state
  - dimension: store_id
    type: int
    sql: ${TABLE}.store_id
  - dimension: things_to_know
    sql: ${TABLE}.thingsToKnow
  - dimension: title
    sql: ${TABLE}.title
  - dimension: votes
    type: number
    sql: ${TABLE}.votes
  - dimension: platform
    sql: (CASE WHEN ${post_with} like '%iPhone%' then 'iOS' WHEN ${post_with} like '%iPad%' then 'iOS' WHEN ${post_with} like '%iPod%' then 'iOS' else 'Android' end)
  - dimension: weeks_since_registration
    type: number
    sql: (${post_date} - ${account_user_profile.created_date}) / 7
  - dimension: days_to_sell
    type: number
    sql: ${sold_date} - ${post_date}
  - dimension: weeks_to_sell
    type: number
    sql: ${days_to_sell} / 7
  - dimension: description_length
    type: tier
    sql: char_length(${description})
  - dimension_group: archived
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.archived_date
  - dimension_group: list_until
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.listUntil
  - dimension_group: modified
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.modified
  - dimension_group: pickup
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.pickup_on
  - dimension_group: post
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.post_date
  - dimension_group: review
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.review_date
  - dimension_group: share_craigslist
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.share_craigslist_date
  - dimension_group: sold
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.sold_date
  - dimension_group: sold_notification_sent
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.sold_notification_sent
  - dimension_group: start
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.startDate
  - dimension_group: unarchived
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.unarchived_date
  - measure: count
    type: count
    drill_fields:
    - id
    - post_from_store_name
    - location_name
    - category_id
  - measure: value
    type: sum
    sql: ${price}
  - measure: count_owners
    type: count_distinct
    sql: ${owner_id}
  - measure: asp
    type: number
    sql: AVG(${price})
  - measure: weekly_cohort_post_percent
    type: number
    sql: 100 * ${count_owners}::float / NULLIF(AVG(${weekly_registration_cohorts.cohort_size}), 0)
  - measure: count_percent_of_total
    type: percent_of_total
    sql: ${count}