view: spotify_watchlist_contributors {
  derived_table: {
    sql:
    with spotify_artist_id as (
      select
        name as artist_name,
        spotify_id as spotify_artist_id,
        vendor_id,
      from FACTS.PROD.LABEL_PARTICIPANT
      where spotify_id is not null
        and (name is not null and name != '')
    ),

    upc_tuid as (
      SELECT
        orch_app_ar_track.id  AS tuid,
        orch_app_ar_releases.upc  AS upc,
        orch_app_ar_track.isrc  AS isrc,
        orch_app_ar_vendor.vendor_id  AS label_id
      FROM orchard_app_reporting_v2.art_relations_prod_art_relations.owner  AS orch_app_ar_owner
      INNER JOIN royalty_accounting_reporting.prod.vw_dim_abacus_ar_vendor  AS orch_app_ar_vendor
        ON lower(orch_app_ar_owner.owner_abbrivation) = lower(orch_app_ar_vendor.owner)
      LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.artist_info  AS orch_app_ar_artist_info
        ON orch_app_ar_vendor.vendor_id = orch_app_ar_artist_info.vendor_id
      LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.releases  AS orch_app_ar_releases
        ON orch_app_ar_artist_info.artist_id = orch_app_ar_releases.artist_id
      INNER JOIN intelligence.dbt_prod.orch_app_ar_track_track_physical_joined  AS orch_app_ar_track
        ON orch_app_ar_track.release_id = orch_app_ar_releases.release_id
    )

    select distinct u.upc,
      -- ta.name,
      -- sw1.artist_name as uri_match,
      -- sw2.artist_name as artist_name_match,
      -- coalesce(sw1.artist_name,sw2.artist_name) as watchlist_contributor,
      -- case when coalesce(sw1.artist_name,sw2.artist_name) is not null then TRUE else FALSE end as contributor_spotify_watchlist,
    CASE WHEN (listagg(distinct case when coalesce(sw1.artist_name,sw2.artist_name) is not null then TRUE else FALSE end, ', ') over (partition by u.upc)) LIKE '%true%' then TRUE else FALSE end as product_spotify_watchlist,
    listagg(distinct case when coalesce(sw1.artist_name,sw2.artist_name) is not null then coalesce(sw1.artist_name,sw2.artist_name) end, ', ') within group (order by case when coalesce(sw1.artist_name,sw2.artist_name) is not null then coalesce(sw1.artist_name,sw2.artist_name) end) over (partition by u.upc) as watchlist_contributors,
    listagg(distinct sw1.artist_name,', ') within group (order by sw1.artist_name) over (partition by u.upc) as match_by_uri,
    listagg(distinct case when sw1.artist_name is null then sw2.artist_name end,', ') within group (order by case when sw1.artist_name is null then sw2.artist_name end) over (partition by u.upc) as match_by_name,

    from ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.TRACK_ARTIST ta
    left join upc_tuid u
        on ta.track_id = u.tuid
    left join spotify_artist_id s
        on upper(ta.name) = upper(s.artist_name) and u.label_id = s.vendor_id
    left join DEV_ENGINEERING.BKAO.SPOTIFY_WATCHLIST sw1
        on SUBSTRING(sw1.artist_uri, 16, LEN(sw1.artist_uri)) = s.spotify_artist_id
    left join DEV_ENGINEERING.BKAO.SPOTIFY_WATCHLIST sw2
        on UPPER(sw2.artist_name) = UPPER(s.artist_name)
    ;;
  }

  dimension: upc {
    hidden: yes
    label: "UPC"
    type: string
    sql: ${TABLE}.upc ;;
  }

  dimension: product_spotify_watchlist {
    description: "Spotify Watchlist as of 04/07/2026"
    label: "Product Contains Spotify Watchlist Artist"
    type: yesno
    sql: ${TABLE}.product_spotify_watchlist ;;
  }

  dimension: watchlist_contributors{
    description: "Spotify Watchlist as of 04/07/2026"
    label: "Watchlist Contributors"
    type: string
    sql: ${TABLE}.watchlist_contributors ;;
  }

  dimension: match_by_uri {
    description: "Spotify Watchlist as of 04/07/2026"
    label: "Watchlist Match by URI"
    type: string
    sql: ${TABLE}.match_by_uri ;;
  }

  dimension: match_by_name {
    description: "Spotify Watchlist as of 04/07/2026"
    label: "Watchlist Match by Name"
    type: string
    sql: ${TABLE}.match_by_name ;;
  }

  }