view: int_dbt_prod_summary_streams {
  sql_table_name: INTELLIGENCE.DBT_PROD.SUMMARY_STREAMS ;;
  suggestions: no

  dimension_group: activity_date {
    view_label: "Date"
    label: "Activity"
    type: time
    description: "Date of recorded activity"
    sql: ${TABLE}.download_activity_date ;;
    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]
  }

  dimension: primary_key {
    primary_key: yes
    hidden: yes
    description: "Primary Key"
    sql: CONCAT(${TABLE}.download_activity_date, ${TABLE}.source_uri,${TABLE}.device_type, ${TABLE}.subscription_type,${TABLE}.source_of_stream_orginal,
          ${TABLE}.ISRC, ${TABLE}.UPC, ${TABLE}.label_id, ${TABLE}.transaction_country_code, ${TABLE}.streams,
           ${TABLE}.user_age_group, ${TABLE}.store
          )
          ;;
  }

  dimension_group: Current {
    view_label: "Xtra Date (Do NOT use)"
    type: time
    sql: current_date()  ;;
#     timeframes: [
#       day_of_year
#     ]
    hidden:  no
  }


  measure: count_days {
    view_label: "Date"
    label: "Count Days"
    description: "N. of distinct days"
    type: count_distinct
    sql: ${TABLE}.download_activity_date ;;
  }

  dimension: transaction_country_code {
    type: string
    view_label: "Transaction Country"
    label: "Country Code"
    description: "Country of transaction"
    sql: ${TABLE}.transaction_country_code ;;
    hidden: yes
  }

  dimension: store {
    type: string
    view_label: "Store"
    label: "Store Name"
    description: "Store Name"
    sql: ${TABLE}.store ;;
  }

  dimension: sub_store {
    type: string
    view_label: "Store"
    label: "Sub-Store Name"
    description: "Sub Store Name"
    sql: ${TABLE}.sub_store ;;
  }

  dimension: subscription_type_original {
    type: string
    view_label: "Transaction Type"
    label: "Subscription Type (Original Values)"
    description: "Type of subscription"
    sql: ${TABLE}.subscription_type ;;
  }

  dimension: subscription_type_group {
    type: string
    view_label: "Transaction Type"
    label: "Subscription Type (Grouped)"
    description: "Type of subscription grouped by broader type"
    sql: CASE

            when ${TABLE}.subscription_type = 'free' and ${TABLE}.store = 'Spotify' then 'Ad-Supported'
            when ${TABLE}.subscription_type = 'premium' and ${TABLE}.store = 'Spotify' then 'Premium'

      when ${TABLE}.sub_store = 'Amazon Ad Supported' and ${TABLE}.store = 'Amazon' then 'Ad-Supported'
      when ${TABLE}.sub_store = 'Amazon Prime' and ${TABLE}.store = 'Amazon' then 'Mid-Tier'
      when ${TABLE}.sub_store = 'Amazon Unlimited' and ${TABLE}.store = 'Amazon' then 'Premium'


      when ${TABLE}.subscription_type = 'Ad-Supported Tier' and ${TABLE}.store = 'UMA' then 'Ad-Supported'
      when ${TABLE}.subscription_type = 'Paid On-Demand' and ${TABLE}.store = 'UMA' then 'Premium'
      when ${TABLE}.subscription_type = 'Paid Mid-Tier' and ${TABLE}.store = 'UMA' then 'Mid-Tier'

      when ${TABLE}.subscription_type = 'high' and ${TABLE}.store = 'SoundCloud' then 'Premium'
      when ${TABLE}.subscription_type = 'mid' and ${TABLE}.store = 'SoundCloud' then 'Mid-Tier'
      when ${TABLE}.subscription_type = 'student' and ${TABLE}.store = 'SoundCloud' then 'Premium'
      when ${TABLE}.subscription_type = 'dj' and ${TABLE}.store = 'SoundCloud' then 'Mid-Tier'
      when ${TABLE}.subscription_type = 'free' and ${TABLE}.store = 'SoundCloud' then 'Ad-Supported'

      when ${TABLE}.subscription_type = 'Ad-Supported' and ${TABLE}.store = 'Pandora' then 'Ad-Supported'
      when ${TABLE}.subscription_type = 'Premium' and ${TABLE}.store = 'Pandora' then 'Premium'
      when ${TABLE}.subscription_type = 'Plus' and ${TABLE}.store = 'Pandora' then 'Mid-Tier'

      when ${TABLE}.subscription_type = 'Ad-Supported Audio Streams' and ${TABLE}.store = 'Deezer' then 'Ad-Supported'
      when ${TABLE}.subscription_type = 'Subscription Audio Streams' and ${TABLE}.store = 'Deezer' then 'Premium'

      when ${TABLE}.store = 'Apple' then 'Premium'

      when ${TABLE}.store = 'LINE Japan' then 'Premium'

      when ${TABLE}.store = 'AWA' then 'Premium'



      ELSE 'Other'
      END
      ;;
  }

  dimension: source_uri {
    type: string
    description: "URI"
    hidden: yes
    sql: ${TABLE}.source_uri ;;
  }

  dimension: source_of_stream_original {
    type: string
    view_label: "Source of Stream"
    label: "Source Name (Original)"
    description: "Name of stream source (Original)"
    sql:  ${TABLE}.source_of_stream_orginal ;;
  }

  dimension: source_of_stream_normalised {
    type: string
    view_label: "Source of Stream"
    label: "Source Name (Normalised)"
    description: "Name of stream source (Normalised)"
    sql:  CASE
      when (${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'album') THEN 'Album'
      when (${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'artist') THEN 'Artist'
      when (${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'search') THEN 'Search'
      when (${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'chart') THEN 'Chart'
      when (${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'collection') THEN 'Collection'
      when (${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'other') THEN 'Other'
      when (${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'play_queue') THEN 'Play Queue'
      when (${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'radio' and ${TABLE}.source_uri = 'dailyMix') THEN 'Daily Mix'
      when (${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'radio' and ${TABLE}.source_uri != 'dailyMix') THEN 'Radio'
      when (${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'others_playlist' and ${TABLE}.source_uri = 'discoverWeekly') THEN 'Discover Weekly'
      when (${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'others_playlist' and ${TABLE}.source_uri = 'releaseRadar') THEN 'Release Radar'
      when (${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'others_playlist' and ${TABLE}.source_uri = 'timeCapsule') THEN 'Time Capsule'
      when (${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'others_playlist' and ${TABLE}.source_uri = 'onRepeat') THEN 'On Repeat'
      when (${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'others_playlist' and ${TABLE}.source_uri = 'repeatRewind') THEN 'Repeat Rewind'
      when (${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'others_playlist' and ${TABLE}.source_uri = 'yourDailyDrive') THEN 'Your Daily Drive'
      when (${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'others_playlist' and ${TABLE}.source_uri = 'homeMix') THEN 'Home Mix'
      when (${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'others_playlist' and ${TABLE}.source_uri = 'yearInMusic') THEN 'Year In Music'
      when (${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'others_playlist' and ${TABLE}.source_uri like 'yourTopSongs%') THEN 'Your Top Songs'
      when (${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'others_playlist' and ${TABLE}.source_uri like 'missedHits%') THEN 'Missed Hits'
      when (${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'others_playlist' and ${TABLE}.source_uri like 'tastebreakers%') THEN 'Taste Breakers'
      when (${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'others_playlist' and ${TABLE}.source_uri like 'onRecord%') THEN 'On Record'

      when ((${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'others_playlist' and
      (${TABLE}.source_uri NOT IN ('discoverWeekly','releaseRadar','timeCapsule','onRepeat','repeatRewind','yourDailyDrive','homeMix','yearInMusic')
      AND ${TABLE}.source_uri NOT LIKE 'yourTopSongs%'
      AND ${TABLE}.source_uri NOT LIKE 'missedHits%'
      AND ${TABLE}.source_uri NOT LIKE 'tastebreakers%'
      AND ${TABLE}.source_uri NOT LIKE 'onRecord%')) and  contains(LOWER(${facts_prod_dim_playlist.playlistauthor}), 'spotify')  )
      THEN 'Editorial Playlist'


      when ((${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'others_playlist' and
      (${TABLE}.source_uri NOT IN ('discoverWeekly','releaseRadar','timeCapsule','onRepeat','repeatRewind','yourDailyDrive','homeMix','yearInMusic')
      AND ${TABLE}.source_uri NOT LIKE 'yourTopSongs%'
      AND ${TABLE}.source_uri NOT LIKE 'missedHits%'
      AND ${TABLE}.source_uri NOT LIKE 'tastebreakers%'
      AND ${TABLE}.source_uri NOT LIKE 'onRecord%')) and not(contains(LOWER(${facts_prod_dim_playlist.playlistauthor}), 'spotify')) and ${facts_prod_dim_playlist.playlistname} is not null)
      THEN '3rd Party Playlist'


      when ((${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'others_playlist' and
      (${TABLE}.source_uri NOT IN ('discoverWeekly','releaseRadar','timeCapsule','onRepeat','repeatRewind','yourDailyDrive','homeMix','yearInMusic')
      AND ${TABLE}.source_uri NOT LIKE 'yourTopSongs%'
      AND ${TABLE}.source_uri NOT LIKE 'missedHits%'
      AND ${TABLE}.source_uri NOT LIKE 'tastebreakers%'
      AND ${TABLE}.source_uri NOT LIKE 'onRecord%')) and ${facts_prod_dim_playlist.playlistauthor} is null and (${facts_prod_dim_playlist.playlistname} is not  null))
      THEN '3rd Party Playlist'


      when ((${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'others_playlist' and
      (${TABLE}.source_uri NOT IN ('discoverWeekly','releaseRadar','timeCapsule','onRepeat','repeatRewind','yourDailyDrive','homeMix','yearInMusic')
      AND ${TABLE}.source_uri NOT LIKE 'yourTopSongs%'
      AND ${TABLE}.source_uri NOT LIKE 'missedHits%'
      AND ${TABLE}.source_uri NOT LIKE 'tastebreakers%'
      AND ${TABLE}.source_uri NOT LIKE 'onRecord%')) and not(contains(LOWER(${facts_prod_dim_playlist.playlistauthor}), 'spotify')) and (${facts_prod_dim_playlist.playlistname} is  null or ${facts_prod_dim_playlist.playlistname} =''))
      THEN 'User Playlist'

      when ((${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'others_playlist' and
      (${TABLE}.source_uri NOT IN ('discoverWeekly','releaseRadar','timeCapsule','onRepeat','repeatRewind','yourDailyDrive','homeMix','yearInMusic')
      AND ${TABLE}.source_uri NOT LIKE 'yourTopSongs%'
      AND ${TABLE}.source_uri NOT LIKE 'missedHits%'
      AND ${TABLE}.source_uri NOT LIKE 'tastebreakers%'
      AND ${TABLE}.source_uri NOT LIKE 'onRecord%')) and ${facts_prod_dim_playlist.playlistauthor} is null and (${facts_prod_dim_playlist.playlistname} is  null or ${facts_prod_dim_playlist.playlistname} =''))
      THEN 'User Playlist'


      when (${TABLE}.store = 'Amazon') then ${TABLE}.source_of_stream_orginal


      when (${TABLE}.store = 'UMA' and ${TABLE}.source_of_stream_orginal = 'Unknown') THEN 'Unknown'
      when (${TABLE}.store = 'UMA' and ${TABLE}.source_of_stream_orginal = 'Promotional Area (e.g. Recommended or Browse Area)') THEN 'Promotional Area (e.g. Recommended or Browse Area)'
      when (${TABLE}.store = 'UMA' and ${TABLE}.source_of_stream_orginal = 'Messages') THEN 'Messages'
      when (${TABLE}.store = 'UMA' and ${TABLE}.source_of_stream_orginal = 'Artist Page') THEN 'Artist'
      when (${TABLE}.store = 'UMA' and ${TABLE}.source_of_stream_orginal = 'Other') THEN 'Other'
      when (${TABLE}.store = 'UMA' and ${TABLE}.source_of_stream_orginal = 'Recent Activity') THEN 'Recent Activity'
      when (${TABLE}.store = 'UMA' and ${TABLE}.source_of_stream_orginal = 'Communities') THEN 'Communities'
      when (${TABLE}.store = 'UMA' and ${TABLE}.source_of_stream_orginal = 'Search') THEN 'Search'
      when (${TABLE}.store = 'UMA' and ${TABLE}.source_of_stream_orginal = 'User Created Playlist/Area') THEN 'User Created Playlist/Area'
      when (${TABLE}.store = 'UMA' and ${TABLE}.source_of_stream_orginal = 'Charts & Algorithmic Sections') THEN 'Charts & Algorithmic Sections'
      when (${TABLE}.store = 'UMA' and ${TABLE}.source_of_stream_orginal = 'News Feed') THEN 'News Feed'
      when (${TABLE}.store = 'UMA' and ${TABLE}.source_of_stream_orginal = 'Followed/Saved 3rd Party Artists or Playlists in the Users Collection') THEN 'Collection'
      when (${TABLE}.store = 'UMA' and ${TABLE}.source_of_stream_orginal = 'Content Page') THEN 'Content Page'

      when (${TABLE}.store = 'SoundCloud' and ${TABLE}.source_of_stream_orginal = '3rd_party_playlist_with_rightsholder_tracks') THEN '3rd Party Playlist With Rightsholder Tracks'
      when (${TABLE}.store = 'SoundCloud' and ${TABLE}.source_of_stream_orginal = '3rd_party_playlist_without_rightsholder_tracks') THEN '3rd Party Playlist Without Rightsholder Tracks'
      when (${TABLE}.store = 'SoundCloud' and ${TABLE}.source_of_stream_orginal = 'soundcloud_playlist_with_rightsholder_tracks') THEN 'SoundCloud Playlist With Rightsholder Tracks'
      when (${TABLE}.store = 'SoundCloud' and ${TABLE}.source_of_stream_orginal = 'soundcloud_playlist_without_rightsholder_tracks') THEN 'SoundCloud Playlist Without Rightsholder Tracks'
      when (${TABLE}.store = 'SoundCloud' and ${TABLE}.source_of_stream_orginal = 'rightsholder_playlist') THEN 'Rightsholder Playlist'
      when (${TABLE}.store = 'SoundCloud' and ${TABLE}.source_of_stream_orginal is null) THEN 'Non-Playlist'
      when (${TABLE}.store = 'SoundCloud' and ${TABLE}.source_of_stream_orginal = 'NULL') THEN 'Non-Playlist'

      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Playlist Play') THEN 'Playlist Play'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Play from All Artist Tracks') THEN 'Play from All Artist Tracks'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Play from All Composer Tracks') THEN 'Play from All Composer Tracks'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Artist Top Track Play') THEN 'Artist Top Track Play'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Autoplay from Composer Top Tracks') THEN 'Autoplay from Composer Top Tracks'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Composer Top Track Play') THEN 'Composer Top Track Play'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Autoplay from Artist Top Tracks') THEN 'Autoplay from Artist Top Tracks'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Autoplay from Thumbed Up Tracks') THEN 'Autoplay from Thumbed Up Tracks'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Autoplay from Station Samples') THEN 'Autoplay from Station Samples'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Autoplay from Album') THEN 'Autoplay from Album'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Station') THEN 'Station'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Downloaded Tracks Play') THEN 'Downloaded Tracks Play'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Autoplay from Collection Tracks') THEN 'Autoplay from Collection Tracks'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Unknown Play Source') THEN 'Unknown Play Source'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Autoplay from All Composer Tracks') THEN 'Autoplay from All Composer Tracks'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Station Play') THEN 'Station Play'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Autoplay from Track') THEN 'Autoplay from Track'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Autoplay from Playlist') THEN 'Autoplay from Playlist'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Collection Tracks Play') THEN 'Collection Tracks Play'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Track play') THEN 'Track play'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Thumbed Up Track Play') THEN 'Thumbed Up Track Play'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Autoplay from Collected Album') THEN 'Autoplay from Collected Album'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Autoplay from Artist Tracks') THEN 'Autoplay from Artist Tracks'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Autoplay from Downloaded Tracks') THEN 'Autoplay from Downloaded Tracks'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Station Sample Track Play') THEN 'Station Sample Track Play'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Album Play') THEN 'Album Play'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Autoplay from All Artist Tracks') THEN 'Autoplay from All Artist Tracks'


      when (${TABLE}.store = 'Apple' and ${TABLE}.source_of_stream_orginal = 'Search') THEN 'Search'
      when (${TABLE}.store = 'Apple' and ${TABLE}.source_of_stream_orginal = 'Other') THEN 'Other'
      when (${TABLE}.store = 'Apple' and ${TABLE}.source_of_stream_orginal = 'Library') THEN 'Library'
      when (${TABLE}.store = 'Apple' and ${TABLE}.source_of_stream_orginal = 'Discovery') THEN 'Discovery'
      when (${TABLE}.store = 'Apple' and ${TABLE}.source_of_stream_orginal = 'Voice') THEN 'Voice'
      when (${TABLE}.store = 'Apple' and ${TABLE}.source_of_stream_orginal = 'External') THEN 'External'
      when (${TABLE}.store = 'Apple' and ${TABLE}.source_of_stream_orginal = 'MusicKit') THEN 'MusicKit'
      when (${TABLE}.store = 'Apple' and ${TABLE}.source_of_stream_orginal = 'Now Playling') THEN 'Now Playing'
      when (${TABLE}.store = 'Apple' and ${TABLE}.source_of_stream_orginal = 'Now Playing') THEN 'Now Playing'





      else 'Other'
      END;;
  }

  dimension: Source_type_group {
    type: string
    view_label: "Source of Stream"
    label: "Source Type Grouped (Normalized)"
    description: "Grouped to include Algorithmic, Editorial, 3rd Party, and User Playlists"
    sql: CASE
      when (${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'album') THEN 'Album'
      when (${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'artist') THEN 'Artist'
      when (${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'search') THEN 'Search'
      when (${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'chart') THEN 'Chart'
      when (${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'collection') THEN 'Collection'
      when (${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'other') THEN 'Other'
      when (${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'play_queue') THEN 'Play Queue'
      when (${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'radio' and ${TABLE}.source_uri = 'dailyMix') THEN 'Daily Mix'
      when (${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'radio' and ${TABLE}.source_uri != 'dailyMix') THEN 'Radio Station'


      when (${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'others_playlist' and
      (${TABLE}.source_uri IN ('discoverWeekly','releaseRadar','timeCapsule','onRepeat','repeatRewind','yourDailyDrive','homeMix','yearInMusic')
      OR ${TABLE}.source_uri LIKE 'yourTopSongs%'
      OR ${TABLE}.source_uri LIKE 'missedHits%'
      OR ${TABLE}.source_uri LIKE 'tastebreakers%'
      OR ${TABLE}.source_uri LIKE 'onRecord%'))
      THEN 'Algorithmic Playlist'

      when ((${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'others_playlist' and
      (${TABLE}.source_uri NOT IN ('discoverWeekly','releaseRadar','timeCapsule','onRepeat','repeatRewind','yourDailyDrive','homeMix','yearInMusic')
      AND ${TABLE}.source_uri NOT LIKE 'yourTopSongs%'
      AND ${TABLE}.source_uri NOT LIKE 'missedHits%'
      AND ${TABLE}.source_uri NOT LIKE 'tastebreakers%'
      AND ${TABLE}.source_uri NOT LIKE 'onRecord%')) and  contains(LOWER(${facts_prod_dim_playlist.playlistauthor}), 'spotify')  )
      THEN 'Editorial Playlist'


      when ((${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'others_playlist' and
      (${TABLE}.source_uri NOT IN ('discoverWeekly','releaseRadar','timeCapsule','onRepeat','repeatRewind','yourDailyDrive','homeMix','yearInMusic')
      AND ${TABLE}.source_uri NOT LIKE 'yourTopSongs%'
      AND ${TABLE}.source_uri NOT LIKE 'missedHits%'
      AND ${TABLE}.source_uri NOT LIKE 'tastebreakers%'
      AND ${TABLE}.source_uri NOT LIKE 'onRecord%')) and not(contains(LOWER(${facts_prod_dim_playlist.playlistauthor}), 'spotify')) and ${facts_prod_dim_playlist.playlistname} is not null)
      THEN '3rd Party Playlist'


      when ((${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'others_playlist' and
      (${TABLE}.source_uri NOT IN ('discoverWeekly','releaseRadar','timeCapsule','onRepeat','repeatRewind','yourDailyDrive','homeMix','yearInMusic')
      AND ${TABLE}.source_uri NOT LIKE 'yourTopSongs%'
      AND ${TABLE}.source_uri NOT LIKE 'missedHits%'
      AND ${TABLE}.source_uri NOT LIKE 'tastebreakers%'
      AND ${TABLE}.source_uri NOT LIKE 'onRecord%')) and ${facts_prod_dim_playlist.playlistauthor} is null and (${facts_prod_dim_playlist.playlistname} is not  null))
      THEN '3rd Party Playlist'


      when ((${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'others_playlist' and
      (${TABLE}.source_uri NOT IN ('discoverWeekly','releaseRadar','timeCapsule','onRepeat','repeatRewind','yourDailyDrive','homeMix','yearInMusic')
      AND ${TABLE}.source_uri NOT LIKE 'yourTopSongs%'
      AND ${TABLE}.source_uri NOT LIKE 'missedHits%'
      AND ${TABLE}.source_uri NOT LIKE 'tastebreakers%'
      AND ${TABLE}.source_uri NOT LIKE 'onRecord%')) and not(contains(LOWER(${facts_prod_dim_playlist.playlistauthor}), 'spotify')) and (${facts_prod_dim_playlist.playlistname} is  null or ${facts_prod_dim_playlist.playlistname} =''))
      THEN 'User Playlist'

      when ((${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'others_playlist' and
      (${TABLE}.source_uri NOT IN ('discoverWeekly','releaseRadar','timeCapsule','onRepeat','repeatRewind','yourDailyDrive','homeMix','yearInMusic')
      AND ${TABLE}.source_uri NOT LIKE 'yourTopSongs%'
      AND ${TABLE}.source_uri NOT LIKE 'missedHits%'
      AND ${TABLE}.source_uri NOT LIKE 'tastebreakers%'
      AND ${TABLE}.source_uri NOT LIKE 'onRecord%')) and ${facts_prod_dim_playlist.playlistauthor} is null and (${facts_prod_dim_playlist.playlistname} is  null or ${facts_prod_dim_playlist.playlistname} =''))
      THEN 'User Playlist'


      when (${TABLE}.store = 'Amazon' and ${TABLE}.source_of_stream_orginal = 'Album') THEN 'Album'
      when (${TABLE}.store = 'Amazon' and ${TABLE}.source_of_stream_orginal = 'AMF Station') THEN 'Radio Station'
      when (${TABLE}.store = 'Amazon' and ${TABLE}.source_of_stream_orginal = 'AMF Station Seed') THEN 'Radio Station'
      when (${TABLE}.store = 'Amazon' and ${TABLE}.source_of_stream_orginal = 'Artist') THEN 'Artist'
      when (${TABLE}.store = 'Amazon' and ${TABLE}.source_of_stream_orginal = 'Auto Playlist') THEN 'Auto Playlist'
      when (${TABLE}.store = 'Amazon' and ${TABLE}.source_of_stream_orginal = 'Genre') THEN 'Genre'
      when (${TABLE}.store = 'Amazon' and ${TABLE}.source_of_stream_orginal = 'Golden Playlist') THEN 'Editorial Playlist'
      when (${TABLE}.store = 'Amazon' and ${TABLE}.source_of_stream_orginal = 'Personalized Playlist') THEN 'Personalized Playlist'
      when (${TABLE}.store = 'Amazon' and ${TABLE}.source_of_stream_orginal = 'Prime Playlist') THEN 'Editorial Playlist'
      when (${TABLE}.store = 'Amazon' and ${TABLE}.source_of_stream_orginal = 'Prime Station') THEN 'Radio Station'
      when (${TABLE}.store = 'Amazon' and ${TABLE}.source_of_stream_orginal = 'Prime Station Seed') THEN 'Radio Station'
      when (${TABLE}.store = 'Amazon' and ${TABLE}.source_of_stream_orginal = 'Recently Added') THEN 'Recently Added'
      when (${TABLE}.store = 'Amazon' and ${TABLE}.source_of_stream_orginal = 'Recently Played') THEN 'Recently Played'
      when (${TABLE}.store = 'Amazon' and ${TABLE}.source_of_stream_orginal = 'Shared Playlist') THEN 'Shared Playlist'
      when (${TABLE}.store = 'Amazon' and ${TABLE}.source_of_stream_orginal = 'Similarity') THEN 'Similarity'
      when (${TABLE}.store = 'Amazon' and ${TABLE}.source_of_stream_orginal = 'Similarity Station') THEN 'Similarity Station'
      when (${TABLE}.store = 'Amazon' and ${TABLE}.source_of_stream_orginal = 'Songs') THEN 'Collection'
      when (${TABLE}.store = 'Amazon' and ${TABLE}.source_of_stream_orginal = 'Unknown') THEN 'Unknown'
      when (${TABLE}.store = 'Amazon' and ${TABLE}.source_of_stream_orginal = 'Unlimited Playlist') THEN 'Editorial Playlist'
      when (${TABLE}.store = 'Amazon' and ${TABLE}.source_of_stream_orginal = 'Unlimited Station') THEN 'Radio Station'
      when (${TABLE}.store = 'Amazon' and ${TABLE}.source_of_stream_orginal = 'Unlimщted Station') THEN 'Radio Station'
      when (${TABLE}.store = 'Amazon' and ${TABLE}.source_of_stream_orginal = 'Unlimited Station Seed') THEN 'Radio Station'
      when (${TABLE}.store = 'Amazon' and ${TABLE}.source_of_stream_orginal = 'User Playlist') THEN 'User Playlist'
      when (${TABLE}.store = 'Amazon' and ${TABLE}.source_of_stream_orginal = 'Unknown (Video)') THEN 'Unknown (Video)'


      when (${TABLE}.store = 'UMA' and ${TABLE}.source_of_stream_orginal = 'Unknown') THEN 'Unknown'
      when (${TABLE}.store = 'UMA' and ${TABLE}.source_of_stream_orginal = 'Promotional Area (e.g. Recommended or Browse Area)') THEN 'Search'
      when (${TABLE}.store = 'UMA' and ${TABLE}.source_of_stream_orginal = 'Messages') THEN 'Messages'
      when (${TABLE}.store = 'UMA' and ${TABLE}.source_of_stream_orginal = 'Artist Page') THEN 'Artist'
      when (${TABLE}.store = 'UMA' and ${TABLE}.source_of_stream_orginal = 'Other') THEN 'Other'
      when (${TABLE}.store = 'UMA' and ${TABLE}.source_of_stream_orginal = 'Recent Activity') THEN 'Recent Activity'
      when (${TABLE}.store = 'UMA' and ${TABLE}.source_of_stream_orginal = 'Communities') THEN 'Communities'
      when (${TABLE}.store = 'UMA' and ${TABLE}.source_of_stream_orginal = 'Search') THEN 'Search'
      when (${TABLE}.store = 'UMA' and ${TABLE}.source_of_stream_orginal = 'User Created Playlist/Area') THEN 'User Playlist'
      when (${TABLE}.store = 'UMA' and ${TABLE}.source_of_stream_orginal = 'Charts & Algorithmic Sections') THEN 'Charts & Algorithmic Sections'
      when (${TABLE}.store = 'UMA' and ${TABLE}.source_of_stream_orginal = 'News Feed') THEN 'News Feed'
      when (${TABLE}.store = 'UMA' and ${TABLE}.source_of_stream_orginal = 'Followed/Saved 3rd Party Artists or Playlists in the Users Collection') THEN 'Collection'
      when (${TABLE}.store = 'UMA' and ${TABLE}.source_of_stream_orginal = 'Content Page') THEN 'Search'

      when (${TABLE}.store = 'SoundCloud' and ${TABLE}.source_of_stream_orginal = '3rd_party_playlist_with_rightsholder_tracks') THEN '3rd Party Playlist'
      when (${TABLE}.store = 'SoundCloud' and ${TABLE}.source_of_stream_orginal = '3rd_party_playlist_without_rightsholder_tracks') THEN '3rd Party Playlist'
      when (${TABLE}.store = 'SoundCloud' and ${TABLE}.source_of_stream_orginal = 'soundcloud_playlist_with_rightsholder_tracks') THEN 'Editorial Playlist'
      when (${TABLE}.store = 'SoundCloud' and ${TABLE}.source_of_stream_orginal = 'soundcloud_playlist_without_rightsholder_tracks') THEN 'Editorial Playlist'
      when (${TABLE}.store = 'SoundCloud' and ${TABLE}.source_of_stream_orginal = 'rightsholder_playlist') THEN '3rd Party Playlist'
      when (${TABLE}.store = 'SoundCloud' and ${TABLE}.source_of_stream_orginal is null) THEN 'Non-Playlist'
      when (${TABLE}.store = 'SoundCloud' and ${TABLE}.source_of_stream_orginal = 'NULL') THEN 'Non-Playlist'

      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Playlist Play') THEN 'Playlist'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Play from All Artist Tracks') THEN 'Artist'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Play from All Composer Tracks') THEN 'Composer'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Artist Top Track Play') THEN 'Artist'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Autoplay from Composer Top Tracks') THEN 'Autoplay'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Composer Top Track Play') THEN 'Composer'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Autoplay from Artist Top Tracks') THEN 'Autoplay'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Autoplay from Thumbed Up Tracks') THEN 'Autoplay'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Autoplay from Station Samples') THEN 'Autoplay'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Autoplay from Album') THEN 'Autoplay'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Station') THEN 'Radio Station'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Downloaded Tracks Play') THEN 'Downloads'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Autoplay from Collection Tracks') THEN 'Collection'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Unknown Play Source') THEN 'Unknown'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Autoplay from All Composer Tracks') THEN 'Autoplay'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Station Play') THEN 'Radio Station'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Autoplay from Track') THEN 'Autoplay'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Autoplay from Playlist') THEN 'Playlist'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Collection Tracks Play') THEN 'Collection'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Track play') THEN 'Track'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Thumbed Up Track Play') THEN 'Track'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Autoplay from Collected Album') THEN 'Autoplay'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Autoplay from Artist Tracks') THEN 'Autoplay'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Autoplay from Downloaded Tracks') THEN 'Autoplay'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Station Sample Track Play') THEN 'Radio Station'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Album Play') THEN 'Album'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Autoplay from All Artist Tracks') THEN 'Autoplay'


      when (${TABLE}.store = 'Apple' and ${TABLE}.source_of_stream_orginal = 'Search') THEN 'Search'
      when (${TABLE}.store = 'Apple' and ${TABLE}.source_of_stream_orginal = 'Other') THEN 'Other'
      when (${TABLE}.store = 'Apple' and ${TABLE}.source_of_stream_orginal = 'Library') THEN 'Library'
      when (${TABLE}.store = 'Apple' and ${TABLE}.source_of_stream_orginal = 'Discovery') THEN 'Discovery'
      when (${TABLE}.store = 'Apple' and ${TABLE}.source_of_stream_orginal = 'Voice') THEN 'Voice'
      when (${TABLE}.store = 'Apple' and ${TABLE}.source_of_stream_orginal = 'External') THEN 'External'
      when (${TABLE}.store = 'Apple' and ${TABLE}.source_of_stream_orginal = 'MusicKit') THEN 'MusicKit'
      when (${TABLE}.store = 'Apple' and ${TABLE}.source_of_stream_orginal = 'Now Playling') THEN 'Now Playing'
      when (${TABLE}.store = 'Apple' and ${TABLE}.source_of_stream_orginal = 'Now Playing') THEN 'Now Playing'


      else 'Other'
      END;;
  }



  dimension: active_passive_collection {
    type: string
    view_label: "Source of Stream"
    label: "Active/Passive/Collection"
    description: "Source type: Active/Passive/Collection"
    sql: CASE
      when (${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'album') THEN 'Active'
      when (${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'artist') THEN 'Active'
      when (${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'search') THEN 'Active'
      when (${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'chart') THEN 'Passive'
      when (${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'collection') THEN 'Collection'
      when (${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'other') THEN 'Other'
      when (${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'play_queue') THEN 'Passive'
      when (${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'radio' and ${TABLE}.source_uri = 'dailyMix') THEN 'Passive'
      when (${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'radio' and ${TABLE}.source_uri != 'dailyMix') THEN 'Passive'


      when (${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'others_playlist' and
      (${TABLE}.source_uri IN ('discoverWeekly','releaseRadar','timeCapsule','onRepeat','repeatRewind','yourDailyDrive','homeMix','yearInMusic')
      OR ${TABLE}.source_uri LIKE 'yourTopSongs%'
      OR ${TABLE}.source_uri LIKE 'missedHits%'
      OR ${TABLE}.source_uri LIKE 'tastebreakers%'
      OR ${TABLE}.source_uri LIKE 'onRecord%'))
      THEN 'Passive'

      when ((${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'others_playlist' and
      (${TABLE}.source_uri NOT IN ('discoverWeekly','releaseRadar','timeCapsule','onRepeat','repeatRewind','yourDailyDrive','homeMix','yearInMusic')
      AND ${TABLE}.source_uri NOT LIKE 'yourTopSongs%'
      AND ${TABLE}.source_uri NOT LIKE 'missedHits%'
      AND ${TABLE}.source_uri NOT LIKE 'tastebreakers%'
      AND ${TABLE}.source_uri NOT LIKE 'onRecord%')) and  contains(LOWER(${facts_prod_dim_playlist.playlistauthor}), 'spotify')  )
      THEN 'Passive'


      when ((${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'others_playlist' and
      (${TABLE}.source_uri NOT IN ('discoverWeekly','releaseRadar','timeCapsule','onRepeat','repeatRewind','yourDailyDrive','homeMix','yearInMusic')
      AND ${TABLE}.source_uri NOT LIKE 'yourTopSongs%'
      AND ${TABLE}.source_uri NOT LIKE 'missedHits%'
      AND ${TABLE}.source_uri NOT LIKE 'tastebreakers%'
      AND ${TABLE}.source_uri NOT LIKE 'onRecord%')) and not(contains(LOWER(${facts_prod_dim_playlist.playlistauthor}), 'spotify')) and ${facts_prod_dim_playlist.playlistname} is not null)
      THEN 'Passive'


      when ((${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'others_playlist' and
      (${TABLE}.source_uri NOT IN ('discoverWeekly','releaseRadar','timeCapsule','onRepeat','repeatRewind','yourDailyDrive','homeMix','yearInMusic')
      AND ${TABLE}.source_uri NOT LIKE 'yourTopSongs%'
      AND ${TABLE}.source_uri NOT LIKE 'missedHits%'
      AND ${TABLE}.source_uri NOT LIKE 'tastebreakers%'
      AND ${TABLE}.source_uri NOT LIKE 'onRecord%')) and ${facts_prod_dim_playlist.playlistauthor} is null and (${facts_prod_dim_playlist.playlistname} is not  null))
      THEN 'Passive'


      when ((${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'others_playlist' and
      (${TABLE}.source_uri NOT IN ('discoverWeekly','releaseRadar','timeCapsule','onRepeat','repeatRewind','yourDailyDrive','homeMix','yearInMusic')
      AND ${TABLE}.source_uri NOT LIKE 'yourTopSongs%'
      AND ${TABLE}.source_uri NOT LIKE 'missedHits%'
      AND ${TABLE}.source_uri NOT LIKE 'tastebreakers%'
      AND ${TABLE}.source_uri NOT LIKE 'onRecord%')) and not(contains(LOWER(${facts_prod_dim_playlist.playlistauthor}), 'spotify')) and (${facts_prod_dim_playlist.playlistname} is  null or ${facts_prod_dim_playlist.playlistname} =''))
      THEN 'Passive'

      when ((${TABLE}.store = 'Spotify' and ${TABLE}.source_of_stream_orginal = 'others_playlist' and
      (${TABLE}.source_uri NOT IN ('discoverWeekly','releaseRadar','timeCapsule','onRepeat','repeatRewind','yourDailyDrive','homeMix','yearInMusic')
      AND ${TABLE}.source_uri NOT LIKE 'yourTopSongs%'
      AND ${TABLE}.source_uri NOT LIKE 'missedHits%'
      AND ${TABLE}.source_uri NOT LIKE 'tastebreakers%'
      AND ${TABLE}.source_uri NOT LIKE 'onRecord%')) and ${facts_prod_dim_playlist.playlistauthor} is null and (${facts_prod_dim_playlist.playlistname} is  null or ${facts_prod_dim_playlist.playlistname} =''))
      THEN 'Passive'







      when (${TABLE}.store = 'Amazon' and ${TABLE}.source_of_stream_orginal = 'Album') THEN 'Active'
      when (${TABLE}.store = 'Amazon' and ${TABLE}.source_of_stream_orginal = 'AMF Station') THEN 'Passive'
      when (${TABLE}.store = 'Amazon' and ${TABLE}.source_of_stream_orginal = 'AMF Station Seed') THEN 'Passive'
      when (${TABLE}.store = 'Amazon' and ${TABLE}.source_of_stream_orginal = 'Artist') THEN 'Active'
      when (${TABLE}.store = 'Amazon' and ${TABLE}.source_of_stream_orginal = 'Auto Playlist') THEN 'Passive'
      when (${TABLE}.store = 'Amazon' and ${TABLE}.source_of_stream_orginal = 'Genre') THEN 'Passive'
      when (${TABLE}.store = 'Amazon' and ${TABLE}.source_of_stream_orginal = 'Golden Playlist') THEN 'Passive'
      when (${TABLE}.store = 'Amazon' and ${TABLE}.source_of_stream_orginal = 'Personalized Playlist') THEN 'Passive'
      when (${TABLE}.store = 'Amazon' and ${TABLE}.source_of_stream_orginal = 'Prime Playlist') THEN 'Passive'
      when (${TABLE}.store = 'Amazon' and ${TABLE}.source_of_stream_orginal = 'Prime Station') THEN 'Passive'
      when (${TABLE}.store = 'Amazon' and ${TABLE}.source_of_stream_orginal = 'Prime Station Seed') THEN 'Passive'
      when (${TABLE}.store = 'Amazon' and ${TABLE}.source_of_stream_orginal = 'Recently Added') THEN 'Collection'
      when (${TABLE}.store = 'Amazon' and ${TABLE}.source_of_stream_orginal = 'Recently Played') THEN 'Collection'
      when (${TABLE}.store = 'Amazon' and ${TABLE}.source_of_stream_orginal = 'Shared Playlist') THEN 'Passive'
      when (${TABLE}.store = 'Amazon' and ${TABLE}.source_of_stream_orginal = 'Similarity') THEN 'Passive'
      when (${TABLE}.store = 'Amazon' and ${TABLE}.source_of_stream_orginal = 'Similarity Station') THEN 'Passive'
      when (${TABLE}.store = 'Amazon' and ${TABLE}.source_of_stream_orginal = 'Songs') THEN 'Collection'
      when (${TABLE}.store = 'Amazon' and ${TABLE}.source_of_stream_orginal = 'Unknown') THEN 'Unknown'
      when (${TABLE}.store = 'Amazon' and ${TABLE}.source_of_stream_orginal = 'Unlimited Playlist') THEN 'Passive'
      when (${TABLE}.store = 'Amazon' and ${TABLE}.source_of_stream_orginal = 'Unlimited Station') THEN 'Passive'
      when (${TABLE}.store = 'Amazon' and ${TABLE}.source_of_stream_orginal = 'Unlimщted Station') THEN 'Passive'
      when (${TABLE}.store = 'Amazon' and ${TABLE}.source_of_stream_orginal = 'Unlimited Station Seed') THEN 'Passive'
      when (${TABLE}.store = 'Amazon' and ${TABLE}.source_of_stream_orginal = 'User Playlist') THEN 'Passive'
      when (${TABLE}.store = 'Amazon' and ${TABLE}.source_of_stream_orginal = 'Unknown (Video)') THEN 'Unknown (Video)'



      when (${TABLE}.store = 'UMA' and ${TABLE}.source_of_stream_orginal = 'Unknown') THEN 'Unknown'
      when (${TABLE}.store = 'UMA' and ${TABLE}.source_of_stream_orginal = 'Promotional Area (e.g. Recommended or Browse Area)') THEN 'Active'
      when (${TABLE}.store = 'UMA' and ${TABLE}.source_of_stream_orginal = 'Messages') THEN 'Active'
      when (${TABLE}.store = 'UMA' and ${TABLE}.source_of_stream_orginal = 'Artist Page') THEN 'Active'
      when (${TABLE}.store = 'UMA' and ${TABLE}.source_of_stream_orginal = 'Other') THEN 'Other'
      when (${TABLE}.store = 'UMA' and ${TABLE}.source_of_stream_orginal = 'Recent Activity') THEN 'Collection'
      when (${TABLE}.store = 'UMA' and ${TABLE}.source_of_stream_orginal = 'Communities') THEN 'Passive'
      when (${TABLE}.store = 'UMA' and ${TABLE}.source_of_stream_orginal = 'Search') THEN 'Active'
      when (${TABLE}.store = 'UMA' and ${TABLE}.source_of_stream_orginal = 'User Created Playlist/Area') THEN 'Passive'
      when (${TABLE}.store = 'UMA' and ${TABLE}.source_of_stream_orginal = 'Charts & Algorithmic Sections') THEN 'Passive'
      when (${TABLE}.store = 'UMA' and ${TABLE}.source_of_stream_orginal = 'News Feed') THEN 'Active'
      when (${TABLE}.store = 'UMA' and ${TABLE}.source_of_stream_orginal = 'Followed/Saved 3rd Party Artists or Playlists in the Users Collection') THEN 'Collection'
      when (${TABLE}.store = 'UMA' and ${TABLE}.source_of_stream_orginal = 'Content Page') THEN 'Active'

      when (${TABLE}.store = 'SoundCloud' and ${TABLE}.source_of_stream_orginal = '3rd_party_playlist_with_rightsholder_tracks') THEN 'Passive'
      when (${TABLE}.store = 'SoundCloud' and ${TABLE}.source_of_stream_orginal = '3rd_party_playlist_without_rightsholder_tracks') THEN 'Passive'
      when (${TABLE}.store = 'SoundCloud' and ${TABLE}.source_of_stream_orginal = 'soundcloud_playlist_with_rightsholder_tracks') THEN 'Passive'
      when (${TABLE}.store = 'SoundCloud' and ${TABLE}.source_of_stream_orginal = 'soundcloud_playlist_without_rightsholder_tracks') THEN 'Passive'
      when (${TABLE}.store = 'SoundCloud' and ${TABLE}.source_of_stream_orginal = 'rightsholder_playlist') THEN 'Passive'
      when (${TABLE}.store = 'SoundCloud' and ${TABLE}.source_of_stream_orginal is null) THEN 'Active'
      when (${TABLE}.store = 'SoundCloud' and ${TABLE}.source_of_stream_orginal = 'NULL') THEN 'Active'

      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Playlist Play') THEN 'Passive'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Play from All Artist Tracks') THEN 'Active'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Play from All Composer Tracks') THEN 'Active'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Artist Top Track Play') THEN 'Active'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Autoplay from Composer Top Tracks') THEN 'Passive'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Composer Top Track Play') THEN 'Active'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Autoplay from Artist Top Tracks') THEN 'Passive'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Autoplay from Thumbed Up Tracks') THEN 'Passive'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Autoplay from Station Samples') THEN 'Passive'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Autoplay from Album') THEN 'Passive'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Station') THEN 'Passive'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Downloaded Tracks Play') THEN 'Collection'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Autoplay from Collection Tracks') THEN 'Collection'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Unknown Play Source') THEN 'Unknown'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Autoplay from All Composer Tracks') THEN 'Passive'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Station Play') THEN 'Passive'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Autoplay from Track') THEN 'Passive'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Autoplay from Playlist') THEN 'Passive'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Collection Tracks Play') THEN 'Collection'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Track play') THEN 'Active'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Thumbed Up Track Play') THEN 'Active'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Autoplay from Collected Album') THEN 'Passive'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Autoplay from Artist Tracks') THEN 'Passive'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Autoplay from Downloaded Tracks') THEN 'Passive'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Station Sample Track Play') THEN 'Passive'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Album Play') THEN 'Active'
      when (${TABLE}.store = 'Pandora' and ${TABLE}.source_of_stream_orginal = 'Autoplay from All Artist Tracks') THEN 'Passive'


      when (${TABLE}.store = 'Apple' and ${TABLE}.source_of_stream_orginal = 'Search') THEN 'Active'
      when (${TABLE}.store = 'Apple' and ${TABLE}.source_of_stream_orginal = 'Other') THEN 'Other'
      when (${TABLE}.store = 'Apple' and ${TABLE}.source_of_stream_orginal = 'Library') THEN 'Collection'
      when (${TABLE}.store = 'Apple' and ${TABLE}.source_of_stream_orginal = 'Discovery') THEN 'Passive'
      when (${TABLE}.store = 'Apple' and ${TABLE}.source_of_stream_orginal = 'Voice') THEN 'Passive'
      when (${TABLE}.store = 'Apple' and ${TABLE}.source_of_stream_orginal = 'External') THEN 'Passive'
      when (${TABLE}.store = 'Apple' and ${TABLE}.source_of_stream_orginal = 'MusicKit') THEN 'Active'
      when (${TABLE}.store = 'Apple' and ${TABLE}.source_of_stream_orginal = 'Now Playling') THEN 'Passive'
      when (${TABLE}.store = 'Apple' and ${TABLE}.source_of_stream_orginal = 'Now Playing') THEN 'Passive'


      else 'Other'
      END;;
  }



  dimension: device_type {
    type: string
    view_label: "Source of Stream"
    label: "Device Type"
    description: "Device Type"
    sql: case
          when ${TABLE}.store = 'UMA' THEN ${TABLE}.device_type

      when ${TABLE}.store = 'Spotify' and ${TABLE}.device_type = 'Mobile phone' THEN 'Mobile'
      when ${TABLE}.store = 'Spotify' and ${TABLE}.device_type = 'Connected Audio Device' THEN 'Connected Audio Device'
      when ${TABLE}.store = 'Spotify' and ${TABLE}.device_type = 'Unknown' THEN 'Unknown'
      when ${TABLE}.store = 'Spotify' and ${TABLE}.device_type = 'Tablet' THEN 'Tablet'
      when ${TABLE}.store = 'Spotify' and ${TABLE}.device_type = 'Computer' THEN 'Desktop'
      when ${TABLE}.store = 'Spotify' and ${TABLE}.device_type = 'Car' THEN 'Car'
      when ${TABLE}.store = 'Spotify' and ${TABLE}.device_type = 'Game Console' THEN 'Game Console'
      when ${TABLE}.store = 'Spotify' and ${TABLE}.device_type = 'TV' THEN 'TV'

      when ${TABLE}.store = 'SoundCloud' and ${TABLE}.device_type IN ('Apple HomePod', 'Cinemo', 'Google Cast Receiver', 'HEOS by Denon',
      'HEOS by Denon (Beta)', 'HEOS by Denon (Dev)', 'HEOS by Denon (Merge)', 'HEOS by Denon (create by SC from email request)', 'Hype Machine',
      'Linkplay', 'Lobby', 'Raumfeld', 'Sonos', 'Sonos Dev - SC GO', 'SoundCloud on Sonos'  ) THEN 'Connected Audio Device'

      when ${TABLE}.store = 'SoundCloud' and ${TABLE}.device_type IN ('Cross DJ Android', 'Cross DJ iOS', 'SoundCloud Android', 'SoundCloud iOS', 'm.soundcloud.com'  ) THEN 'Mobile'

      when ${TABLE}.store = 'SoundCloud' and ${TABLE}.device_type IN ('Denon DJ', 'DJUCED', 'Denon DJ Desktop', 'Denon DJ Embedded',
      'Numark', 'PCDJ Integration', 'Rekordbox DJ', 'Serato DJ Lite', 'Serato DJ Pro', 'Traktor DJ', 'TribeXR', 'Virtual DJ', 'WeDJ', '[MAC/PC] Cross DJ',
      '[MAC/PC] Cross DJ Pro', 'djay Pro for Mac', 'djay Pro for Android', 'djay Pro for iOS', 'djay Pro for iOS Ad-Hoc', 'edjing Mix', 'edjing Pro',
      'rekordbox 5 - 6.5', 'rekordbox 6.6', 'rekordbox for Android', 'rekordbox for iOS' ) THEN 'DJ Equipment/Software'

      when ${TABLE}.store = 'SoundCloud' and ${TABLE}.device_type IN ('Facebook Partner', 'SoundCloud Embed Player', 'SoundCloud Visual Embed Player',
      'SoundCloud.com', 'resident-advisor-www access') THEN 'Desktop'
      when ${TABLE}.store = 'SoundCloud' and ${TABLE}.device_type IN ('SoundCloud on Xbox / Windows 10') THEN 'Game Console'



      when ${TABLE}.store = 'Pandora' and ${TABLE}.device_type IN ('ANDROID', 'Android', 'BLACKBERRY', 'Blackberry', 'IOS', 'Mobile phone', 'WINDOWS_MOBILE',
      'Windows Mobile', 'iOS') THEN 'Mobile'

      when ${TABLE}.store = 'Pandora' and ${TABLE}.device_type IN ('APPLIANCE', 'AUTO_AFTERMARKET', 'AUTO_DEFAULT', 'AUTO_OEM', 'AV_RECEIVERS',
      'Connected Audio Device', 'DMX', 'OPERATOR_PROVISIONED_STB', 'OVER_THE_TOP_STB', 'PALM', 'PORTABLE', 'STEREO_CONNECTED', 'TABLE_TOP_RADIO',
      'VOICE_ACTIVATED', 'Voice Activated','Wearable' ) THEN 'Connected Audio Device'

      when ${TABLE}.store = 'Pandora' and ${TABLE}.device_type IN ('BLU_RAY', 'BluRay', 'HOME_THEATRE', 'TV') THEN 'TV'

      when ${TABLE}.store = 'Pandora' and ${TABLE}.device_type IN ('Browser', 'Computer', 'E_READER', 'HTML5', 'WEB') THEN 'Desktop'

      when ${TABLE}.store = 'Pandora' and ${TABLE}.device_type IN ('Car') THEN 'Car'

      when ${TABLE}.store = 'Pandora' and ${TABLE}.device_type IN ('Tablet') THEN 'Tablet'

      when ${TABLE}.store = 'Pandora' and ${TABLE}.device_type IN ('GAME_CONSOLE') THEN 'Game Console'



      when ${TABLE}.store = 'Apple' and ${TABLE}.device_type = 'Mobile' THEN 'Mobile'
      when ${TABLE}.store = 'Apple' and ${TABLE}.device_type = 'Desktop' THEN 'Desktop'
      when ${TABLE}.store = 'Apple' and ${TABLE}.device_type = 'Voice' THEN 'Connected Audio Device'


      when ${TABLE}.store = 'Amazon' and ${TABLE}.device_type IN ('Mobile phone', 'iOS', 'Windows', 'Android', '1') THEN 'Mobile'


      when ${TABLE}.store = 'Amazon' and ${TABLE}.device_type IN ('Tablet', '2') THEN 'Tablet'
      when ${TABLE}.store = 'Amazon' and ${TABLE}.device_type IN ('TV', 'Smart TV', '5') THEN 'TV'
      when ${TABLE}.store = 'Amazon' and ${TABLE}.device_type IN ('6') then 'Connected Audio Device'
      when ${TABLE}.store = 'Amazon' and ${TABLE}.device_type IN ('Smart Speaker', '8') THEN 'Smart Speaker'
      when ${TABLE}.store = 'Amazon' and ${TABLE}.device_type IN ('Macintosh', 'Computer','0') THEN 'Desktop'
      when ${TABLE}.store = 'Amazon' and ${TABLE}.device_type IN ('Game Console', '7') THEN 'Game Console'
      when ${TABLE}.store = 'Amazon' and ${TABLE}.device_type IN ('Car', '3') THEN 'Car'


      else 'Other'
      END;;
  }

  dimension: device_type_original {
    type: string
    view_label: "Source of Stream"
    label: "Device Type (Original Value)"
    description: "Device Type (Original Value)"
    sql:  ${TABLE}.device_type
      ;;
  }

  dimension: label_id {
    type: number
    view_label: "Label"
    label: "Label ID"
    description: "Label ID"
    sql: ${TABLE}.label_id ;;
    value_format: "0"
  }

  dimension: upc {
    type: string
    view_label: "Release"
    label: "UPC"
    description: "UPC"
    sql: ltrim(${TABLE}.upc,0) ;;
  }

  dimension: isrc {
    type: string
    view_label: "Track"
    label: "ISRC"
    description: "Track Code"
    sql: ${TABLE}.isrc ;;
  }

  measure: count_isrc {
    type: count_distinct
    view_label: "Track"
    label: "Number of Tracks"
    description: "N. of distinct tracks"
    sql: ${TABLE}.ISRC ;;
  }


  dimension: user_age_group {
    type: string
    view_label: "User"
    label: "User Age Group"
    description: "user age group"
    sql: ${TABLE}.user_age_group ;;
  }

  measure: number_of_listeners {
    type: sum
    view_label: "User"
    label: "Number of Listeners"
    description: "n. of listeners"
    sql: ${TABLE}.number_of_listeners;;
  }

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

  measure: avg_listen_duration {
    type: average
    view_label: "User"
    label: "Average Listen Duration"
    description: "Average Listen Duration in Seconds"
    sql: ${TABLE}.avg_listening_duration;;
  }

  measure: voice_streams {
    type: sum
    view_label: "Streams"
    label: "Voice Streams"
    description: "Sum of voice streams"
    sql: ${TABLE}.voice_streams ;;
    value_format_name: decimal_0
  }

  measure: streams {
    type: sum
    view_label: "Streams"
    label: "Streams"
    description: "Total n. of streams per recorded activity"
    sql: ${TABLE}.streams ;;
  }



  dimension: catalog_type {
    type: string
    label: "Catalog Type"
    description: "Frontline (0-3 Months), Midline (4-17 Months), or Catalog (18+ Months)"
    view_label: "Release"
    sql: CASE
            WHEN DATEDIFF(month, ${orch_app_ar_releases.release_date}, ${TABLE}.download_activity_date) < 4 THEN 'Frontline'
            WHEN DATEDIFF(month, ${orch_app_ar_releases.release_date}, ${TABLE}.download_activity_date) BETWEEN 4 AND 17 THEN 'Midline'
            WHEN DATEDIFF(month, ${orch_app_ar_releases.release_date}, ${TABLE}.download_activity_date) > 17 THEN 'Catalog'
            WHEN ${orch_app_ar_releases.release_date} IS NULL THEN 'Catalog'
          END ;;
  }

  dimension: days_since_release {
    type: number
    label: "Days since release"
    description: "No. of days since the original release date"
    view_label: "Date"
    sql: datediff(day, ${orch_app_ar_releases.release_date}, ${TABLE}.download_activity_date);;
  }

  dimension: weeks_since_release {
    type: number
    label: "Weeks since release"
    description: "No. of weeks since the original release date"
    view_label: "Date"
    sql: datediff(week, ${orch_app_ar_releases.release_date}, ${TABLE}.download_activity_date);;
  }






}