view: cm_unioned_chart_2 {
  sql_table_name: INTELLIGENCE.DBT_PROD.CM_UNIONED_CHART_2 ;;
#  derived_table: {
#    sql: with ChartTypeUnion as(
#
#     Select spotify as "ID_NUMERIC", null as "ID_STRING", 'Spotify' as "STORE",'Spotify Viral (Daily)' as "CHART_TYPE",
#timestp, country, 'All Cities' as "CITY", 'All Genres' as "GENRE",
#rank, isrc
#from CHARTMETRIC.RAW_DATA.SPOTIFY_CHART
#left join CHARTMETRIC.RAW_DATA.SPOTIFY on CHARTMETRIC.RAW_DATA.SPOTIFY_CHART.spotify = CHARTMETRIC.RAW_DATA.SPOTIFY.id
#where CHARTMETRIC.RAW_DATA.SPOTIFY_CHART.chart_type = 'viral'
#and CHARTMETRIC.RAW_DATA.SPOTIFY_CHART.duration = 'daily'
#and country is not null
#
#union all
#
#Select spotify as "ID_NUMERIC", null as "ID_STRING", 'Spotify' as "STORE",'Spotify Regional (Daily)' as "CHART_TYPE",
#timestp, country, 'All Cities' as "CITY", 'All Genres' as "GENRE",
#rank, isrc
#from CHARTMETRIC.RAW_DATA.SPOTIFY_CHART
#left join CHARTMETRIC.RAW_DATA.SPOTIFY on CHARTMETRIC.RAW_DATA.SPOTIFY_CHART.spotify = CHARTMETRIC.RAW_DATA.SPOTIFY.id
#where CHARTMETRIC.RAW_DATA.SPOTIFY_CHART.chart_type = 'regional'
#and CHARTMETRIC.RAW_DATA.SPOTIFY_CHART.duration = 'daily'
#and country is not null
#
#union all
#
#Select itunes as "ID_NUMERIC", null as "ID_STRING", 'iTunes' as "STORE", 'iTunes Overall' as "CHART_TYPE",
#timestp, country, 'All Cities' as "CITY",
#genre, rank, isrc
#from CHARTMETRIC.RAW_DATA.ITUNES_CHART
#left join CHARTMETRIC.RAW_DATA.ITUNES on CHARTMETRIC.RAW_DATA.ITUNES_CHART.itunes= CHARTMETRIC.RAW_DATA.ITUNES.id
#where  CHARTMETRIC.RAW_DATA.ITUNES_CHART.genre = 'All Genres'
#and country is not null
#
#union all
#
#Select itunes as "ID_NUMERIC", null as "ID_STRING", 'iTunes' as "STORE", 'iTunes Genre' as "CHART_TYPE",
#timestp, country, 'All Cities' as "CITY",
#genre, rank, isrc
#from CHARTMETRIC.RAW_DATA.ITUNES_CHART
#left join CHARTMETRIC.RAW_DATA.ITUNES on CHARTMETRIC.RAW_DATA.ITUNES_CHART.itunes= CHARTMETRIC.RAW_DATA.ITUNES.id
#where  CHARTMETRIC.RAW_DATA.ITUNES_CHART.genre != 'All Genres' and CHARTMETRIC.RAW_DATA.ITUNES_CHART.genre is not null
#and country is not null
#
#union all
#
#Select itunes as "ID_NUMERIC", null as "ID_STRING",'Apple Music' as "STORE", 'Apple Music Daily' as "CHART_TYPE",
#timestp, country, 'All Cities' as "CITY", 'All Genres' as "GENRE", rank, isrc
#from CHARTMETRIC.RAW_DATA.APPLE_MUSIC_DAILY_TOP_CHART
#left join CHARTMETRIC.RAW_DATA.ITUNES on CHARTMETRIC.RAW_DATA.APPLE_MUSIC_DAILY_TOP_CHART.itunes = CHARTMETRIC.RAW_DATA.ITUNES.id
#where country is not null
#
#union all
#
#Select shazam_track_id as "ID_NUMERIC", null as "ID_STRING", 'Shazam' as "STORE", 'Shazam Cities' as "CHART_TYPE",
#timestp, country, ifnull(city, 'All Cities') as "CITY", 'All Genres' as "GENRE", rank, isrc
#from CHARTMETRIC.RAW_DATA.SHAZAM_CHART
#left join CHARTMETRIC.RAW_DATA.SHAZAM on CHARTMETRIC.RAW_DATA.SHAZAM_CHART.shazam_track_id = CHARTMETRIC.RAW_DATA.SHAZAM.id
#where country is not null
#
#union all
#
#Select  null as "ID_NUMERIC", amazon as "ID_STRING", 'Amazon Music' as "STORE", 'Amazon Overall' as "CHART_TYPE",
#timestp, 'GOBAL' as "COUNTRY", 'All Cities' as "CITY",
#trim(ab.name) as "GENRE", rank, isrc
#from CHARTMETRIC.RAW_DATA.AMAZON_CHART
#left join CHARTMETRIC.RAW_DATA.AMAZON on CHARTMETRIC.RAW_DATA.AMAZON_CHART.amazon = CHARTMETRIC.RAW_DATA.AMAZON.id
#full join CHARTMETRIC.RAW_DATA.AMAZON_BROWSE ab on CHARTMETRIC.RAW_DATA.AMAZON_CHART.amazon_browse = ab.id
#where ab.id is null AND rank is not null
#
#union all
#
#Select  null as "ID_NUMERIC", amazon as "ID_STRING", 'Amazon Music' as "STORE", 'Amazon Genre' as "CHART_TYPE",
#timestp, 'GLOBAL' as "COUNTRY", 'All Cities' as "CITY",
#trim(ab.name) as "GENRE", rank, isrc
#from CHARTMETRIC.RAW_DATA.AMAZON_CHART
#left join CHARTMETRIC.RAW_DATA.AMAZON on CHARTMETRIC.RAW_DATA.AMAZON_CHART.amazon = CHARTMETRIC.RAW_DATA.AMAZON.id
#full join CHARTMETRIC.RAW_DATA.AMAZON_BROWSE ab on CHARTMETRIC.RAW_DATA.AMAZON_CHART.amazon_browse = ab.id
#where ab.id is not null AND rank is not null
#
#union all
#
#
#Select deezer as "ID_NUMERIC", null as "ID_STRING",'Deezer' as "STORE", 'Deezer Track' as "CHART_TYPE",
#timestp, country, 'All Cities' as "CITY", 'All Genres' as "GENRE",
#rank, isrc
#from CHARTMETRIC.RAW_DATA.DEEZER_CHART
#left join CHARTMETRIC.RAW_DATA.DEEZER on CHARTMETRIC.RAW_DATA.DEEZER_CHART.deezer = CHARTMETRIC.RAW_DATA.DEEZER.id
#where country is not null
#
#union all
#
#Select soundcloud as "ID_NUMERIC", null as "ID_STRING",'Soundcloud' as "STORE", 'Soundcloud Top Genre' as "CHART_TYPE",
#timestp, trim(country), 'All Cities' as "CITY", trim(CHARTMETRIC.RAW_DATA.SOUNDCLOUD_CHART.genre) as "GENRE",
#rank, isrc
#from CHARTMETRIC.RAW_DATA.SOUNDCLOUD_CHART
#left join CHARTMETRIC.RAW_DATA.SOUNDCLOUD on CHARTMETRIC.RAW_DATA.SOUNDCLOUD_CHART.soundcloud = CHARTMETRIC.RAW_DATA.SOUNDCLOUD.id
#where country is not null and trim(kind) = 'top'
#
#union all
#
#Select soundcloud as "ID_NUMERIC", null as "ID_STRING",'Soundcloud' as "STORE", 'Soundcloud Trending Genre' as "CHART_TYPE",
#timestp, trim(country), 'All Cities' as "CITY", trim(CHARTMETRIC.RAW_DATA.SOUNDCLOUD_CHART.genre) as "GENRE",
#rank, isrc
#from CHARTMETRIC.RAW_DATA.SOUNDCLOUD_CHART
#left join CHARTMETRIC.RAW_DATA.SOUNDCLOUD on CHARTMETRIC.RAW_DATA.SOUNDCLOUD_CHART.soundcloud = CHARTMETRIC.RAW_DATA.SOUNDCLOUD.id
#where country is not null and trim(kind) = 'trending')
#
#      select round(ID_NUMERIC) as id_num, ID_STRING, STORE, CHART_TYPE, TIMESTP, COUNTRY,
#      CITY, GENRE, RANK, ISRC
#      from ChartTypeUnion;;
#  }

  dimension: id_num {
    type: number
    hidden: yes
    sql: ${TABLE}.id_num;;
  }

  dimension: id_string {
    type: string
    hidden: yes
    sql: ${TABLE}.id_string;;
  }

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

  dimension: chart_type {
    label: "Chart"
    type: string
    sql: ${TABLE}.chart_type;;
    view_label: "Chart"
  }

  dimension_group: chart {
    label: "Chart"
    type: time
    hidden: no
    sql: ${TABLE}.timestp ;;
    timeframes: [
      date,
      week
    ]
    view_label: "Chart"
  }

  dimension: country {
    label: "Chart Country"
    type: string
    hidden: yes
    sql: TRIM(${TABLE}.country);;
    view_label: "Chart"
  }

  dimension: city {
    label: "Shazam City"
    type: string
    hidden: no
    sql: ${TABLE}.city;;
    view_label: "Chart"
    description: "only useable for Shazam"
  }


  dimension: genre {
    label: "Genre"
    type: string
    hidden: no
    sql: ifnull(${TABLE}.genre, 'All Genres');;
    view_label: "Chart"
  }

  measure: plays {
    label: "Plays"
    type: sum
    hidden: yes
    sql: ${TABLE}.plays;;
    view_label: "Spotify"
  }

  measure: rank {
    label: "Chart Rank"
    type: number
    hidden: no
    sql: min(${TABLE}.rank) ;;
    view_label: "Chart"
  }


  dimension: isrc {
    label: "ISRC"
    type: string
    hidden: yes
    sql: ${TABLE}.isrc ;;
  }


  measure: posts {
    label: "Posts"
    hidden: yes
    type: max
    sql: ${TABLE}.posts;;
    view_label: "TikTok"
  }


}