# View: dt_blacklist_words

**View Name:** dt_blacklist_words
**Table Source:** `Derived from: Art_Relations_Prod_Art_Relations.Artist_Info, Art_Relations_Prod_Art_Relations.Blacklist_Words, Art_Relations_Prod_Art_Relations.Release_Artist, Art_Relations_Prod_Art_Relations.Releases, Art_Relations_Prod_Art_Relations.Track + 3 more`
**File Path:** `dt_blacklist_words.view.lkml`

## Overview

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

## Dimensions

| Name | Type |
|------|------|
| `blacklist_word` | string |
| `upc` | number |
| `submission_for_approval_date` | date |
| `release_name` | string |
| `release_status` | string |

## Derived Table

```sql
sql: WITH
    blacklist_words as(SELECT DISTINCT word
                       FROM orchard_app_reporting_v2.art_relations_prod_art_relations.blacklist_words)
SELECT DISTINCT
    listagg(DISTINCT bw.word, '|') as blacklist_word,
    r.upc as UPC,
    r.release_status,
    r.release_name,
    MIN(rq.date_submitted) as submission_for_approval_date
FROM royalty_accounting_reporting.prod.vw_dim_abacus_ar_vendor v
LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.artist_info ai on ai.vendor_id = v.vendor_id
LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.releases r ON ai.artist_id = r.artist_id
LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.track t ON r.release_id = t.release_id
LEFT JOIN intelligence.dbt_prod.content_review_queue_unioned rq on rq.release_id = r.release_id
LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.release_artist ra ON r.upc = ra.upc
LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.track_artist ta ON t.id = ta.track_id
LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.subaccount s ON r.subaccount_id = s.subaccount_id and v.vendor_id = s.vendor_id
INNER JOIN blacklist_words bw ON (contains(concat('', IFNULL(r.release_name, ''), ' ', IFNULL(r.label, ''), ' ', IFNULL(r.format, ''), ' ',
                          IFNULL(r.vendor_catalog_number, ''), IFNULL(r.vendor_release_identifier, ''), ' ',
                          IFNULL(r.c_line, ''), ' ', IFNULL(ai.name, ''), ' ', IFNULL(ra.artist_name, ''), ' ', IFNULL(t.track_name, ''),' ',
                          IFNULL(t.version, ''), ' ', IFNULL(t.p_line, ''), ' ', IFNULL(t.explicit_lyrics, ''),' ',
                          IFNULL(t.vendor_track_identifier, ''), IFNULL(t.meta_language, ''), IFNULL(ta.name, ''),
                          IFNULL(v.company, ''), ' ', IFNULL(s.subaccount_name, '')), bw.word))
WHERE rq.date_submitted >= {% parameter lower_date %
```