view: int_dbt_prod_agg_soundcloud_model {
  sql_table_name: INTELLIGENCE.DBT_PROD.AGG_SOUNDCLOUD_MODEL ;;

  dimension_group: activity_date {
    view_label: "Date"
    description: "The date when a track was consumed in a store"
    label: "Activity"
    type: time
    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: user_id {
    type: string
    view_label: "User"
    label: "User ID"
    description: "User ID provided by the store"
    sql: ${TABLE}.user_id ;;
  }

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

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

  dimension: transaction_country {
    type: string
    view_label: "Transaction Country"
    label: "Country Code"
    description: "Two letter alphabeticat code assigned to the country where a track was played"
    map_layer_name: countries
    sql: ${TABLE}.country ;;
  }


  dimension: operating_system {
    type: string
    view_label: "User"
    label: "Operating system"
    description: "OS of the device the track was consumed on"
    sql: ${TABLE}.operating_system ;;
  }


  dimension: artist_name {
    type: string
    view_label: "Artist"
    label: "Artist Name"
    description: "Artist name as reported by the store"
    sql: ${TABLE}.artist_name ;;
  }

  dimension: track_title {
    type: string
    view_label: "Track"
    label: "Track Title"
    description: "Track name provided by the store"
    sql: ${TABLE}.track_title ;;
  }

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

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

  dimension: upc {
    type: number
    view_label: "Release"
    description: "Stands for Unique product Code. Numeric assigned to every release"
    label: "UPC"
    sql: ${TABLE}.upc ;;
  }

  measure: total_listening_duration {
    type: sum
    view_label: "Streams"
    label: "Total Listening Duration"
    description: "Total Listening Duration (seconds)"
    sql: ${TABLE}.total_listening_time ;;
  }

  measure: avg_listening_duration {
    type: average
    view_label: "Streams"
    label: "Average Listening Duration"
    description: "Average Listening Duration (seconds)"
    sql: ${TABLE}.avg_listen_duration ;;
  }

  measure: streams {
    type: sum
    view_label: "Streams"
    label: "Units"
    description: "Number of Streams"
    sql: ${TABLE}.streams ;;
  }

  dimension: album_artist {
    type: string
    view_label: "Release"
    label: "Release Artist"
    description: "Artist name provided by store"
    sql: ${TABLE}.album_artist ;;
  }

  dimension: album_name {
    type: string
    view_label: "Release"
    label: "Release Name"
    description: "Release name provided by store"
    sql: ${TABLE}.album_name ;;
  }

  dimension: user_country {
    type: string
    view_label: "User"
    label: "User Country"
    description: "Country of the user as per account"
    map_layer_name: countries
    sql: ${TABLE}.user_country ;;
  }

  dimension: user_city {
    type: string
    view_label: "User"
    label: "User City"
    description: "Country of the user as per account"
    sql: ${TABLE}.user_city ;;
  }

  dimension: user_region {
    type: string
    view_label: "User"
    label: "User Region"
    description: "Region as per user's account"
    sql: ${TABLE}.user_region ;;
  }

  dimension: user_age {
    type: string
    view_label: "User"
    label: "Age Group"
    description: "Age group the user fits into"
    sql: ${TABLE}.user_age ;;
  }

  dimension: user_gender {
    type: string
    view_label: "User"
    label: "Gender"
    description: "Gender as per user account"
    sql: ${TABLE}.user_gender ;;
  }

  dimension: user_subscription {
    type: string
    view_label: "User"
    label: "Subscription"
    description: "Type of subscription: Mid-Tier, Premium or Ad Supported"
    sql: CASE
    when ${TABLE}.user_subscription_tier = 'high' then 'Premium'
      when ${TABLE}.user_subscription_tier = 'mid' then 'Mid-Tier'
      when ${TABLE}.user_subscription_tier = 'student' then 'Premium'
      when ${TABLE}.user_subscription_tier = 'dj' then 'Mid-Tier'
      when ${TABLE}.user_subscription_tier = 'free' then 'Ad-Supported'
      ELSE 'Unknown'
      END;;
  }

  dimension: client_application {
    type: string
    view_label: "Streams"
    label: "Application"
    description: "Application from where the track was streamed"
    sql: ${TABLE}.client_application ;;
  }

  dimension: device_type {
    type: string
    view_label: "Streams"
    description: "Device Type: Desktop, Game Console, DJ Equipment/Software, Connected Audio Device"
    label: "Device Type"
    sql: CASE
    when ${TABLE}.client_application 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}.client_application IN ('Cross DJ Android', 'Cross DJ iOS', 'SoundCloud Android', 'SoundCloud iOS', 'm.soundcloud.com'  ) THEN 'Mobile'
    when ${TABLE}.client_application 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}.client_application IN ('Facebook Partner', 'SoundCloud Embed Player', 'SoundCloud Visual Embed Player',
    'SoundCloud.com', 'resident-advisor-www access') THEN 'Desktop'
    when ${TABLE}.client_application IN ('SoundCloud on Xbox / Windows 10') THEN 'Game Console'
  else 'Unknown'
  END;;
  }

  dimension: playlist_id {
    type: string
    view_label: "Playlist"
    label: "Playlist ID"
    description: "Playlist ID reported by store"
    sql: ${TABLE}.playlist_id ;;
  }

  dimension: playlist_type {
    type: string
    description: "Type of playlist: Editorial/ 3rd Party"
    view_label: "Playlist"
    label: "Playlist Type"
    sql: CASE
    when (${TABLE}.playlist_type='3rd_party_playlist_without_rightsholder_tracks') THEN '3rd Party Playlist'
    when (${TABLE}.playlist_type='rightsholder_playlist') THEN '3rd Party Playlist'
    when (${TABLE}.playlist_type='3rd_party_playlist_with_rightsholder_tracks') THEN '3rd Party Playlist'
    when (${TABLE}.playlist_type='soundcloud_playlist_without_rightsholder_tracks') THEN 'Editorial Playlist'
    when (${TABLE}.playlist_type='soundcloud_playlist_with_rightsholder_tracks') THEN 'Editorial Playlist'
    when (${TABLE}.playlist_type IS NULL) THEN 'Non Playlist'
    when (${TABLE}.playlist_type= 'NULL') THEN 'Non Playlist'
    END;;
  }

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

  dimension: playlist_position {
    type: number
    view_label: "Playlist"
    label: "Position in Playlist"
    description: "Positon of the track in a given playlist"
    sql: ${TABLE}.position_in_playlist ;;
  }

  measure: track_favourited {
    type: sum
    view_label: "Streams"
    label: "Track in Favourites"
    description: "Number of tracks added to favorites"
    sql: ${TABLE}.track_favourited ;;
  }

  measure: track_message_posted{
    type: sum
    view_label: "Streams"
    label: "Track Message Posted"
    description: "Number of tracks shared as messages"
    sql: ${TABLE}.track_message_posted ;;
  }

  measure: track_reposted_shared{
    type: sum
    view_label: "Streams"
    label: "Track Repost Shared"
    description: "Number of tracks reposted"
    sql: ${TABLE}.track_reposted_shared ;;
  }

  measure: track_to_playlist_added{
    type: sum
    view_label: "Streams"
    label: "Track Added to Playlist"
    description: "Number of tracks added to playlists by users"
    sql: ${TABLE}.track_to_playlist_added ;;
  }

}