# View: spotify_watchlist_contributors

**View Name:** spotify_watchlist_contributors
**Table Source:** `Derived from: Art_Relations_Prod_Art_Relations.Artist_Info, Art_Relations_Prod_Art_Relations.Owner, Art_Relations_Prod_Art_Relations.Releases, Art_Relations_Prod_Art_Relations.Track_Artist, Bkao.Spotify_Watchlist + 3 more`
**File Path:** `views/spotify_watchlist_contributors.view.lkml`

## Overview

- **File Size:** 3997 bytes
- **Lines of Code:** 89
- **Dimensions:** 5
- **Measures:** 0
- **Dimension Groups:** 0
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `upc` | string |
| `product_spotify_watchlist` | yesno |
| `watchlist_contributors` | string |
| `match_by_uri` | string |
| `match_by_name` | string |

## SQL Comments

- 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,

## Derived Table

```sql
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)
    ;;
```