view: facts_prod_apple_podcasts_subscription_snapshot {

  sql_table_name: FACTS.PROD.APPLE_PODCASTS_SUBSCRIPTION_SNAPSHOT ;;

  dimension_group: report_date {
    type: time
    description: "Refers to the reporting date that the report was initially generated by Apple"
    sql: ${TABLE}.REPORT_DATE ;;
    view_label: "Date"
    label: "Report"
    timeframes: [date,
                 day_of_week]
  }

  dimension: storefront_name {
    type: string
    description: "Territory in which the Podcast was consumed"
    sql: ${TABLE}.STOREFRONT_NAME ;;
  }

  dimension: channel_apple_id {
    primary_key: no
    type: string
    description: "The unique string which Apple uses to label a podcast channel"
    sql: ${TABLE}.CHANNEL_APPLE_ID ;;
  }

  dimension: channel_name {
    type: string
    description: "Sony designated name for the channel"
    sql: ${TABLE}.CHANNEL_NAME ;;
  }

  dimension: subscription_apple_id {
    type: string
    description: "The unique string which Apple associates with any particular subscription offering"
    sql: ${TABLE}.SUBSCRIPTION_APPLE_ID ;;
  }

  dimension: subscription_name {
    type: string
    description: "Sony designated name for the subscription offering and duration"
    sql: ${TABLE}.SUBSCRIPTION_NAME ;;
  }

  dimension: subscription_state {
    type: string
    description: "The current state the subscription is in (Full Paid, Intro Offer, Error Grace, Error Retry)."
    sql: ${TABLE}.SUBSCRIPTION_STATE ;;
  }

  dimension: subscription_sub_type {
    type: string
    description: "Used to delineate between subscription states that are free or paid. If a subscriber is in their free trial, that will be indicated in this field. If the subscriber is in a paying state, this field will be blank."
    sql: ${TABLE}.SUBSCRIPTION_SUB_TYPE ;;
  }

  dimension: subscription_duration {
    type: string
    description: "Duration of subscription"
    sql: ${TABLE}.SUBSCRIPTION_DURATION ;;
  }

  dimension: device {
    type: string
    description: "The type of device where the event occurred"
    sql: ${TABLE}.DEVICE ;;
  }

  measure: customer_price {
    type: sum
    description: "The price charged to the customer."
    sql: ${TABLE}.CUSTOMER_PRICE ;;
    value_format: "#,##0.00"
  }

  dimension: currency_of_proceeds {
    type: string
    description: "Currency of Sony proceeds"
    sql: ${TABLE}.CURRENCY_OF_PROCEEDS ;;
  }

  measure: developer_proceeds {
    type: sum
    description: "The per-unit proceeds for each item delivered"
    sql: ${TABLE}.DEVELOPER_PROCEEDS ;;
    value_format: "#,##0.00"
  }

  dimension: preserved_pricing {
    type: string
    description: "When a subscription renews and the original price is preserved, this field equals “Yes.” Otherwise, it is blank"
    sql: ${TABLE}.PRESERVED_PRICING ;;
  }

  dimension: proceeds_reason {
    type: string
    description: "When a subscription renews and the subscription has been active for more than a year, you receive 85% of the customer price minus applicable taxes, and this field equals “Rate After One Year.” Otherwise, you receive 70% and the field is blank."
    sql: ${TABLE}.PROCEEDS_REASON ;;
  }

  measure: subscription_count {
    type: sum
    description: "The cumulative number of active subscriptions."
    sql: ${TABLE}.SUBSCRIPTION_COUNT ;;
  }

  dimension_group: download_date {
    view_label: "Date"
    label: "Download"
    description: "Refers to when the report was downloaded from Apple Podcasts Connect"
    type: time
    sql: ${TABLE}.DOWNLOAD_DATE ;;
    timeframes: [date,
                 day_of_week]
  }

  measure: sony_proceeds {
    type: number
    description: "Developers proceeds * Units"
    sql: sum(${TABLE}.developer_proceeds) * sum(${TABLE}.units) ;;
    label: "Sony Proceeds"
    value_format: "#,##0.00"
  }

  measure: apple_proceeds {
    type: number
    description: "Subscription count * Customer price"
    sql: sum(${TABLE}.subscription_count) * sum(${TABLE}.customer_price) ;;
    label: "Apple Proceeds"
    value_format: "#,##0.00"
  }

  dimension: apple_vendor_id {
    description: "Apple Podcasts identifier that is associated to the Legal Entity of the Apple Podcasts Connect account transactions"
    type: number
    label: "Apple Vendor ID"
    sql: ${TABLE}.vendor_id ;;
  }

  dimension: apple_vendor_name {
    description: "Description for the Apple Vendor ID that is associated to the Legal Entity of the Apple Podcasts Connect account transactions"
    type: string
    label: "Apple Vendor Name"
    sql: ${TABLE}.vendor_name ;;
  }

}