view: int_dbt_prod_agg_amazon_monthly_rollup_user_aggregation {
  sql_table_name:  INTELLIGENCE.DBT_PROD.agg_amazon_monthly_rollup_user_aggregation ;;

  dimension_group: activity_date {
    view_label: "Date"
    label: "Activity"
    type: time
    sql: ${TABLE}.ACTIVITY_MONTH ;;
    description: "The date when activity occured"
    timeframes: [year,
      quarter,
      month,
      week,
      date,
      day_of_year,
      day_of_week,
      week_of_year,
      month_name,
      month_num,
      fiscal_month_num,
      fiscal_quarter,
      fiscal_quarter_of_year,
      fiscal_year]
  }

  measure: count_days {
    view_label: "Date"
    label: "Count Days"
    type: count_distinct
    sql: ${TABLE}.ACTIVITY_MONTH ;;
    description: "Number of days a track has activity"
  }

  # dimension: user_id {
  #   type: string
  #   view_label: "User"
  #   label: "User ID"
  #   description: "Unique ID assigned to each user by the store"
  #   sql: ${TABLE}.customer_id ;;
  # }

  # measure: number_of_users {
  #   type: count_distinct
  #   view_label: "User"
  #   label: "Number of Users"
  #   description: "Count of distict user ids"
  #   sql: ${TABLE}.customer_id ;;
  # }

  dimension: stream_source_id {
    type: string
    view_label: "Source of Stream"
    label: "Stream Source ID"
    description: "The unique source id provided by the store"
    sql: ${TABLE}.stream_source_id ;;
  }

  dimension: store_and_product_type {
    type: string
    view_label: "Store"
    label: "Store & Product Type"
    description: "Type of subscription offered by the store"
    sql: ${TABLE}.vendor_name ;;
  }

  dimension: provider_genre {
    type: string
    view_label: "Track"
    label: "Provider Genre"
    sql: ${TABLE}.provider_genre ;;
    description: "Genre of the track as provided by the store"
  }

  dimension: artist_name {
    type: string
    view_label: "Artist"
    label: "Artist Name"
    sql: ${TABLE}.artist_name ;;
    description: "Name of the main artist associated with a release"
  }

  dimension: isrc {
    type: string
    view_label: "Track"
    label: "ISRC"
    description: "Unique alphanumeric code assigned to each track"
    sql: ${TABLE}.isrc_track_number ;;
  }

  measure: number_of_tracks {
    type: count_distinct
    view_label: "Track"
    label: "Number of Tracks"
    sql: ${TABLE}.isrc_track_number ;;
    description: "Number of unique ISRCs"
  }

  dimension: upc {
    type: string
    view_label: "Release"
    label: "UPC"
    sql: ltrim(${TABLE}.upc_code,0) ;;
    description: "Stands for Unique product Code. Numeric assigned to every release"
  }

  dimension: label_id {
    type: number
    view_label: "Label"
    label: "Label ID"
    sql: ${TABLE}.label_id ;;
    description: "ID of the label associated with the track"
  }

  dimension: release_name {
    type: string
    view_label: "Release"
    label: "Release Name"
    sql: ${TABLE}.product_title ;;
    description: "Name of the release as stored as reported by the store"
  }

  dimension: track_playlist_position  {
    type: string
    view_label: "Playlist"
    label: "Track Position in Playlist"
    sql: ${TABLE}.location_of_track_in_playlist;;
    description: "Position of the track where is appears in the playlist"
  }

  measure: max_track_playist_position{
    type: number
    view_label: "Playlist"
    label: "Max track Position In Playlist"
    sql: min(${TABLE}.location_of_track_in_playlist) ;;
    description: "The lowest position at which a track has ever been in a playlist"
  }

  dimension: device_type {
    type: string
    view_label: "Source of Stream"
    label: "Device Type"
    sql: ${TABLE}.device_type ;;
    description: "The user device type from where the stream has generated"
  }

  dimension: os_type {
    type: string
    view_label: "Source of Stream"
    label: "OS Type"
    sql: ${TABLE}.os_type ;;
    description: "Operating System of the user device from where the stream has generated"
  }

  dimension: licensor {
    type: string
    view_label: "Licensor"
    label: "Licensor"
    sql: ${TABLE}.licensor ;;
    description: "Name of the company which distributes the track. Can be theorchard, sme etc."
  }

  dimension: source_of_stream_original_values {
    type: string
    view_label: "Source of Stream"
    label: "Source Name (Original Value)"
    hidden: yes
    sql: ${TABLE}.selection_source_type ;;
  }

  dimension: source_of_stream {
    type: string
    view_label: "Source of Stream"
    label: "Source Name (Original)"
    description: "The exact source from where a track was played on the store"
    sql: CASE
      when (${TABLE}.selection_source_type = 'ALBUM') THEN 'Album'
      when (${TABLE}.selection_source_type = 'AMF_STATION') THEN 'AMF Station'
      when (${TABLE}.selection_source_type = 'AMF_STATION_SEED') THEN 'AMF Station Seed'
      when (${TABLE}.selection_source_type = 'ARTIST') THEN 'Artist'
      when (${TABLE}.selection_source_type = 'AUTO_PLAYLIST') THEN 'Auto Playlist'
      when (${TABLE}.selection_source_type = 'GENRE') THEN 'Genre'
      when (${TABLE}.selection_source_type = 'GOLDEN_PLAYLIST') THEN 'Golden Playlist'
      when (${TABLE}.selection_source_type = 'PERSONALIZE_PLAYLIST') THEN 'Personalized Playlist'
      when (${TABLE}.selection_source_type = 'PRIME_PLAYLIST') THEN 'Prime Playlist'
      when (${TABLE}.selection_source_type = 'PRIME_STATION') THEN 'Prime Station'
      when (${TABLE}.selection_source_type = 'PRIME_STATION_SEED') THEN 'Prime Station Seed'
      when (${TABLE}.selection_source_type = 'RECENTLY_ADDED') THEN 'Recently Added'
      when (${TABLE}.selection_source_type = 'RECENTLY_PLAYED') THEN 'Recently Played'
      when (${TABLE}.selection_source_type = 'SHARED_PLAYLIST') THEN 'Shared Playlist'
      when (${TABLE}.selection_source_type = 'SIMILARITY') THEN 'Similarity'
      when (${TABLE}.selection_source_type = 'SIMILARITY_STATION') THEN 'Similarity Station'
      when (${TABLE}.selection_source_type = 'SONGS') THEN 'Songs'
      when (${TABLE}.selection_source_type = 'UNKNOWN') THEN 'Unknown'
      when (${TABLE}.selection_source_type = 'UNLIMITED_PLAYLIST') THEN 'Unlimited Playlist'
      when (${TABLE}.selection_source_type = 'UNLIMITED_STATION') THEN 'Unlimited Station'
      when (${TABLE}.selection_source_type = 'UNLIMщTED_STATION') THEN 'Unlimited Station'
      when (${TABLE}.selection_source_type = 'UNL_STATION_SEED') THEN 'Unlimited Station Seed'
      when (${TABLE}.selection_source_type = 'USER_PLAYLIST') THEN 'User Playlist'
      when (${TABLE}.selection_source_type = 'Unknown (Video)') THEN 'Unknown (Video)'
      ELSE 'Other'
      END
    ;;
  }

  dimension: source_of_stream_group {
    type: string
    view_label: "Source of Stream"
    label: "Source Name (Grouped)"
    description: "Groupings made based on from where was a track streamed on a store"
    sql: CASE
      when (${TABLE}.selection_source_type = 'ALBUM') THEN 'Album'
      when (${TABLE}.selection_source_type = 'AMF_STATION') THEN 'Station'
      when (${TABLE}.selection_source_type = 'AMF_STATION_SEED') THEN 'Station'
      when (${TABLE}.selection_source_type = 'ARTIST') THEN 'Artist'
      when (${TABLE}.selection_source_type = 'AUTO_PLAYLIST') THEN 'Auto Playlist'
      when (${TABLE}.selection_source_type = 'GENRE') THEN 'Genre'
      when (${TABLE}.selection_source_type = 'GOLDEN_PLAYLIST') THEN 'Playlist'
      when (${TABLE}.selection_source_type = 'PERSONALIZE_PLAYLIST') THEN 'Personalized Playlist'
      when (${TABLE}.selection_source_type = 'PRIME_PLAYLIST') THEN 'Playlist'
      when (${TABLE}.selection_source_type = 'PRIME_STATION') THEN 'Station'
      when (${TABLE}.selection_source_type = 'PRIME_STATION_SEED') THEN 'Station'
      when (${TABLE}.selection_source_type = 'RECENTLY_ADDED') THEN 'Recently Added'
      when (${TABLE}.selection_source_type = 'RECENTLY_PLAYED') THEN 'Recently Played'
      when (${TABLE}.selection_source_type = 'SHARED_PLAYLIST') THEN 'Shared Playlist'
      when (${TABLE}.selection_source_type = 'SIMILARITY') THEN 'Similarity'
      when (${TABLE}.selection_source_type = 'SIMILARITY_STATION') THEN 'Similarity Station'
      when (${TABLE}.selection_source_type = 'SONGS') THEN 'Collection'
      when (${TABLE}.selection_source_type = 'UNKNOWN') THEN 'Unknown'
      when (${TABLE}.selection_source_type = 'UNLIMITED_PLAYLIST') THEN 'Playlist'
      when (${TABLE}.selection_source_type = 'UNLIMITED_STATION') THEN 'Station'
      when (${TABLE}.selection_source_type = 'UNLIMщTED_STATION') THEN 'Station'
      when (${TABLE}.selection_source_type = 'UNL_STATION_SEED') THEN 'Station'
      when (${TABLE}.selection_source_type = 'USER_PLAYLIST') THEN 'User Playlist'
      when (${TABLE}.selection_source_type = 'Unknown (Video)') THEN 'Unknown (Video)'
      ELSE 'Other'
      END
    ;;
  }

  dimension: source_of_stream_detail {
    type: string
    view_label: "Source of Stream"
    label: "Source Name Detail (Original)"
    sql: ${TABLE}.selection_source_detail ;;
    description: "The unique source id provided by the store"
  }

  dimension: playlist_name {
    type: string
    view_label: "Playlist"
    label: "Playlist Name"
    sql: ${TABLE}.playlist_name ;;
    description: "Name of the playlist as displayed in the store"
  }



  dimension: playlist_genre {
    type: string
    view_label: "Playlist"
    label: "Amazon Playlist Genre"
    sql: ${TABLE}.playlist_genre ;;
    description: "Genre of a playlist as defined by Amazon"
  }

  dimension: playlist_owner_id {
    type: string
    view_label: "Playlist"
    label: "Playlist Owner ID"
    sql: ${TABLE}.playlist_owner_id ;;
    description: "Playlist Owner Store ID"
  }

  dimension: number_of_tracks_in_playlist {
    type: number
    view_label: "Playlist"
    label: "Number of Tracks in Playlist"
    sql: ${TABLE}.number_of_tracks_in_playlist ;;
  }

  dimension: playlist_followers {
    type: number
    view_label: "Playlist"
    label: "Playlist Followers - Daily"
    sql: ${TABLE}.playlist_followers ;;
    description: "Number of users that follow a given playlist"
  }

  measure: max_date {
    type: date
    sql: max(${activity_date_date} ;;
    hidden: yes
  }





  dimension: country_code {
    type: string
    view_label: "Transaction Country"
    label: "Country Code"
    sql: ${TABLE}.country_code ;;
    hidden:  yes

  }

  dimension: country {
    type: string
    view_label: "Transaction Country"
    label: "Country Code 2"
    sql: ${TABLE}.country ;;
    hidden:  yes

  }


  dimension: subscription_product {
    type: string
    view_label: "Transaction Type"
    label: "Subscription Product"
    description: "Name of the subscription product the user holds"
    sql: ${TABLE}.subscription_product ;;
  }

  dimension: subscription_type {
    type: string
    view_label: "Transaction Type"
    label: "Subscription Type"
    description: "Type of subscription the user holds. (Paid, trial etc)"
    sql: ${TABLE}.subscription_type ;;
  }

  dimension: subscription_detail {
    type: string
    view_label: "Transaction Type"
    label: "Subscription Detail"
    description: "Gived details on thetimeframe of subscription a user holds (Annual, Monthly etc)"
    sql: ${TABLE}.subscription_detail ;;
  }

  dimension: number_of_total_playlists {
    type: number
    view_label: "User"
    label: "Total Playlists Following"
    description: "Total number of playlists a user is following"
    sql: ${TABLE}.number_of_total_playlists ;;
  }

  dimension: number_of_personal_playlists{
    type: number
    view_label: "User"
    label: "Number of Personal Playlists"
    description: "The current number of user-created playlists available in the user's collection, both public and private (if applicable)"
    sql: ${TABLE}.number_of_personal_playlists ;;
  }

  dimension: number_of_third_party_playlists {
    type: number
    view_label: "User"
    label: "Number of 3rd Party Playlists"
    description: "The current number of user-selected 3rd party playlists in the user's collection. (if applicable)"
    sql: ${TABLE}.number_of_third_party_playlists ;;
  }

  dimension:  number_of_total_tracks_in_collection {
    type: number
    view_label: "User"
    label: "Total number of tracks in collection"
    description: "The current number of user-selected tracks saved to their collection across all providers"
    sql: ${TABLE}.number_of_total_tracks_in_collection ;;
  }

  dimension: sub_store {
    type: string
    view_label: "Store"
    label: "Amazon Sub Store"
    description: "Sub store categorisation: Amazon Music, Amazon Unlimited, Amazon Prime or Amazon Ad Supported"
    sql: ${TABLE}.sub_store ;;
  }

  measure: total_listen_duration {
    type: sum
    view_label: "User"
    label: "Total Listen Duration"
    description: "Total Listen Duration in Seconds"
    sql: ${TABLE}.total_listening_time;;
  }

  measure: streams {
    type: sum
    view_label: "Streams"
    label: "Streams"
    description: "Total number of times a track was played by a user"
    sql: ${TABLE}.streams ;;
  }

  measure: skips {
    type: sum
    view_label: "Streams"
    label: "Skips"
    description: "Total number of times a track was skipped by a user"
    sql: ${TABLE}.skips ;;
  }

  measure: voice_streams {
    type: sum
    view_label: "Streams"
    label: "Voice Streams"
    description: "Total number of times a track was played by a user using voice command."
    sql: ${TABLE}.voice_activated_streams ;;
  }

  measure: voice_skips {
    type: sum
    view_label: "Streams"
    label: "Voice Skips"
    description: "Total number of times a track was skipped by a user using voice command."
    sql: ${TABLE}.voice_activated_skips ;;
  }



  dimension: days_since_release {
    view_label: "Date"
    description: "Calculates the difference in days between an album's Release Date and when activity occured (Activity Date)"
    type: number
    sql: IFF(SIGN(DATEDIFF('day', ${orch_app_ar_releases.release_date}, ${TABLE}.ACTIVITY_MONTH)) = -1,
      1, (DATEDIFF('day', ${orch_app_ar_releases.release_date}, ${TABLE}.ACTIVITY_MONTH) + 1)) ;;
  }


  dimension: weeks_since_release {
    view_label: "Date"
    description: "Calculates the difference in weeks between an album's Release Date and when activity occured (Activity Date)"
    type: number
    sql: IFF(SIGN(DATEDIFF('day', ${orch_app_ar_releases.release_date}, ${TABLE}.ACTIVITY_MONTH)) = -1,
      1, CEIL((DATEDIFF('day', ${orch_app_ar_releases.release_date}, ${TABLE}.ACTIVITY_MONTH) + 1) / 7)) ;;
  }

  dimension: months_since_release {
    view_label: "Date"
    description: "Calculates the difference in months between an album's Release Date and when activity occured (Activity Date)"
    type: number
    sql: IFF(SIGN(DATEDIFF('day', ${orch_app_ar_releases.release_date}, ${TABLE}.ACTIVITY_MONTH)) = -1,
      1, CEIL((DATEDIFF('day', ${orch_app_ar_releases.release_date}, ${TABLE}.ACTIVITY_MONTH) + 1) / 28)) ;;
  }

}