# View: dt_account_termination

**View Name:** dt_account_termination
**Table Source:** `Derived from: Art_Relations_Prod_Art_Relations.Project, Art_Relations_Prod_Art_Relations.Releases, Art_Relations_Prod_Art_Relations.Vendor, Dbt_Prod.Active_Vendor_Contracts, Prod.Company_Brand_Has_Label_Vendor + 3 more`
**File Path:** `views/dt_account_termination.view.lkml`

## Overview

- **File Size:** 25976 bytes
- **Lines of Code:** 579
- **Dimensions:** 21
- **Measures:** 2
- **Dimension Groups:** 4
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `label_name` | string |
| `label_id` | string |
| `upc` | string |
| `brand` | string |
| `flagged_by` | string |
| `notes` | string |
| `is_active` | string |
| `voucher_code` | string |
| `applicant_id` | string |
| `account_name` | string |
| `applicant_name` | string |
| `country` | string |
| `spotify_monthly_listeners` | number |
| `spotify_url` | string |
| `youtube_subscribers` | number |
| `youtube_url` | string |
| `instagram_followers` | number |
| `instagram_url` | string |
| `tiktok_followers` | number |
| `tiktok_url` | string |
| `type` | string |

## Measures

| Name | Type |
|------|------|
| `num_live_releases` | sum |
| `spotify_followers` | sum |

## Dimension Groups

| Name | Type |
|------|------|
| `date_flagged` | time |
| `account_start_date` | time |
| `last_modified_date` | time |
| `created_date` | time |

## SQL Comments

- gda_leads.first_name AS FirstName,
- gda_leads.reviewer_first_name as Reviewer_FirstName,
- gda_leads.reviewer_last_name as Reviewer_LastName,
- gda_leads.reviewer_email as Reviewer_Email,
- CASE WHEN gda_leads.voucher_code is not null THEN 'Referred Artist' ELSE '' END as is_referred,

## Derived Table

```sql
sql:
WITH digital_audio_products AS (
    WITH content_review AS (SELECT
        rq.CREATED_DATETIME AS REVIEW_SUBMITTED_ON,
        rq.move_note as move_note,
        IFF(rq.STATUS = 'new','Ready For Review','Completed')as REVIEW_STATUS,
        IFF(rq.QUEUE_NAME = 'initial','For Review',IFF(rq.QUEUE_NAME = 'escalation','Escalation','Under Investigation')) as QUEUE_NAME,
        v.VENDOR_ID as label_id,
        v.name as label_name,
        cb.display_name as brand,
        r.upc as upc,
        i.name as moved_by,
        (CASE WHEN IFF(active_label.vendor_id IS NULL, FALSE, TRUE)  THEN 'Yes' ELSE 'No' END) AS active_status


        FROM ORCHARD_APP_REPORTING_V2.prod_CONTENT_REVIEW_CONTENT_REVIEW.REVIEW_QUEUE rq
        LEFT JOIN (
                SELECT
                  USER_ID,
                  REVIEW_QUEUE_ID, 'approval' AS COMPLETION_TYPE,
                  CREATED_DATETIME AS REVIEW_COMPLETED_DATETIME
                FROM ORCHARD_APP_REPORTING_V2.prod_CONTENT_REVIEW_CONTENT_REVIEW.APPROVAL
                WHERE _fivetran_deleted=false
                UNION ALL
                SELECT
                  USER_ID,
                  REVIEW_QUEUE_ID, 'rejection' AS COMPLETION_TYPE,
                  CREATED_DATETIME AS REVIEW_COMPLETED_DATETIME
                FROM ORCHARD_APP_REPORTING_V2.prod_CONTENT_REVIEW_CONTENT_REVIEW.REJECTION
                WHERE _fivetran_deleted=false
                ) as cr ON rq.ID=cr.REVIEW_QUEUE_ID
        LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_prod_ART_RELATIONS.RELEASES r ON rq.PRODUCT_ID=r.RELEASE_ID
        LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_prod_ART_RELATIONS.PROJECT p ON p.PROJECT_ID = r.PROJECT_ID
        LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_prod_ART_RELATIONS.VENDOR v ON v.VENDOR_ID=p.VENDOR_ID
        LEFT JOIN intelligence.dbt_prod.active_vendor_contracts  AS active_label ON v.vendor_id = active_label.vendor_id
        left outer join FACTS.prod.COMPANY_BRAND_HAS_LABEL_VENDOR cbv on cbv.vendor_id = v.vendor_id
        left outer join FACTS.prod.COMPANY_BRAND cb on cbv.company_brand_uuid = cb.uuid
        LEFT JOIN facts.PROD.identity i
            ON rq.MOVED_TO_QUEUE_BY_USER_ID = i.id

        WHERE rq._fivetran_deleted=false
        AND v.vendor_id NOT IN (70948, 35360, 35976,16162, 16160, 25824)
        HAVING rq.STATUS='new' OR cr.COMPLETION_TYPE IS NOT NULL
    )

    SELECT DISTINCT
        (TO_CHAR(TO_DATE(REVIEW_SUBMITTED_ON ), 'YYYY-MM-DD')) AS review_submitted_date,
        label_name,
        label_id,
        upc,
        brand,
        moved_by,
        active_status,
        LISTAGG(distinct rtrim(move_note), ', ') WITHIN GROUP (ORDER BY rtrim(move_note)) AS move_notes


    FROM content_review
    WHERE (UPPER( content_review.REVIEW_STATUS ) = UPPER('Ready For Review')) AND (UPPER( content_review.QUEUE_NAME  ) = UPPER('Under Investigation'))
    GROUP BY 1,2,3,4,5,6,7
),

product_volume AS (
    SELECT DISTINCT
        orch_app_ar_vendor.vendor_id  AS label_id,
        (TO_CHAR(TO_DATE(orch_app_ar_vendor.date_created ), 'YYYY-MM-DD')) AS created_date,
        COUNT(DISTINCT orch_app_ar_releases.release_id) AS number_of_releases
    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 digital_audio_products dap
        ON dap.label_id = orch_app_ar_vendor.vendor_id
    WHERE orch_app_ar_vendor.vendor_id IN (dap.label_id)
    GROUP BY 1,2

),

no_referrals as (
    WITH sony_applications_data AS (Select a.* from (
        select distinct
            gda_leads.id as ApplicantID,
            -- gda_leads.first_name AS FirstName,
            gda_leads.last_name AS LastName,
            gda_leads.email as Email,
            -- gda_leads.reviewer_first_name as Reviewer_FirstName,
            -- gda_leads.reviewer_last_name as Reviewer_LastName,
            -- gda_leads.reviewer_email as Reviewer_Email,
            -- CASE WHEN gda_leads.voucher_code is not null THEN 'Referred Artist' ELSE '' END as is_referred,
            gda_leads.voucher_code as Voucher_Code,
            gda_leads.Artist_Name ArtistName,
            coalesce(territory.NAME, gda_leads.country) AS Country,
            CASE WHEN gda_leads.lead_score = 'Review'  and gda_leads.status = 'New'       and gda_leads.external_test = false
                 THEN 'Artist In Review'
                 WHEN gda_leads.lead_score = 'Review'  and gda_leads.status = 'Qualified' and gda_leads.external_test = false
                 THEN 'Artist Approved - Manual'
                 WHEN gda_leads.lead_score = 'Approve' and gda_leads.status = 'Qualified' and gda_leads.is_converted = True
                 THEN 'Artist Approved - Auto'
                 WHEN gda_leads.status = 'Unqualified'  and gda_leads.external_test = false THEN 'Rejected'
                 ELSE 'ForChecking' END AS Type,
            gda_leads.last_modified_timestamp AS LastModifiedTimestamp,
            gda_leads.created_timestamp AS CreatedTimestamp,
            -- gda_signup.DeezerFans,
            -- gda_signup.DeezerURL,
            -- gda_signup.FacebookFollowers,
            -- gda_signup.FacebookUrl,
            gda_signup.InstagramFollowers,
            gda_signup.InstagramUrl,
            -- gda_signup.SoundcloudFollowers,
            -- gda_signup.SoundcloudUrl,
            gda_signup.SpotifyFollowers,
            gda_signup.SpotifyMonthlyListeners,
            gda_signup.SpotifyUrl,
            gda_signup.TiktokFollowers,
            gda_signup.TiktokUrl,
            -- gda_signup.TwitterFollowers,
            -- gda_signup.TwitterUrl,
            gda_signup.YoutubeSubscribers,
            gda_signup.YoutubeUrl,
            phatvend.vendor_id as VENDOR_ID,
            phatvend.company as Vendor_Name
        from facts.prod.gda_leads gda_leads
        left join facts.prod.identity ident on ident.email  = gda_leads.email
        left join AWAL_SAX.SAX.TERRITORIES territory ON territory.ISO_ALPHA_3_CODE = gda_leads.country
        left join (select distinct phatvend.vendor_id, oavend.name, oavend.company, hp.identity_id
                    from  facts.prod.has_profile hp
                    left join   facts.prod.PROFILE_HAS_ACCESS_TO_VENDOR phatvend on phatvend.profile_uuid = hp.profile_uuid
                    left join orchard_app_reporting_v2.art_relations_prod_art_relations.vendor oavend on oavend.vendor_id::string =
                    phatvend.vendor_id)  phatvend on phatvend.identity_id = ident.id AND phatvend.company = gda_leads.artist_name
        left join (
               select TRIM(gda_signup.record_content:Email) as Email,TRIM(gda_signup.record_content:Company) as Company,
            sum(replace(gda_signup.record_content:DeezerFans__c, '"', '')) as DeezerFans,
            listagg(replace(gda_signup.record_content:DeezerUrl__c, '"', ''),',') as DeezerURL,
            sum(replace(gda_signup.record_content:FacebookFollowers__c, '"', '')) AS FacebookFollowers,
            listagg(replace(gda_signup.record_content:FacebookUrl__c, '"', ''),'|') AS FacebookUrl,
            sum(replace(gda_signup.record_content:InstagramFollowers__c, '"', ''))AS InstagramFollowers,
            listagg(replace(gda_signup.record_content:InstagramUrl__c, '"', '')) AS InstagramUrl,
            sum(replace(gda_signup.record_content:SoundcloudFollowers__c, '"', ''))AS SoundcloudFollowers,
            listagg(replace(gda_signup.record_content:SoundcloudUrl__c, '"', ''),'|') AS SoundcloudUrl,
            sum(replace(gda_signup.record_content:SpotifyFollowers__c, '"', '')) AS SpotifyFollowers,
            sum(replace(gda_signup.record_content:SpotifyMonthlyListeners__c, '"', '') )AS SpotifyMonthlyListeners,
            listagg(replace(gda_signup.record_content:SpotifyUrl__c, '"', '') ,'|') AS SpotifyUrl,
            sum(replace(gda_signup.record_content:TiktokFollowers__c, '"', '')) AS TiktokFollowers,
            listagg(replace(gda_signup.record_content:TiktokUrl__c, '"', ''),'|') AS TiktokUrl,
            sum(replace(gda_signup.record_content:TwitterFollowers__c, '"', '')) AS TwitterFollowers,
            listagg(replace(gda_signup.record_content:TwitterUrl__c, '"', ''),'|') AS TwitterUrl,
            sum(replace(gda_signup.record_content:YoutubeSubscribers__c, '"', '')) AS YoutubeSubscribers,
            listagg(replace(gda_signup.record_content:YoutubeUrl__c, '"', ''),'|') AS YoutubeUrl
            from facts.prod.audit_event_gda_signup gda_signup
            group by TRIM(gda_signup.record_content:Email) ,TRIM(gda_signup.record_content:Company)) gda_signup
                on gda_signup.email = gda_leads.email and
                      gda_signup.Company =  gda_leads.artist_name
        where
            (   (gda_leads.lead_score = 'Approve' and gda_leads.status = 'Qualified' and IS_CONVERTED = true) or
                (gda_leads.lead_score = 'Review' and gda_leads.status = 'Qualified' and gda_leads.external_test = false) or
                (gda_leads.lead_score = 'Review' and gda_leads.status = 'New' and gda_leads.external_test = false) or
                (gda_leads.status = 'Unqualified' and gda_leads.external_test = false)
             )
               and gda_leads.email not like '%deleteme%' and gda_leads.email not like '%Lambda%' and gda_leads.email not like '%test%'
        ) a)
SELECT distinct
    sony_applications_data.ApplicantId  AS applicant_id,
    sony_applications_data.VENDOR_ID  AS vendor_id,
    sony_applications_data.voucher_code as voucher_code,
    sony_applications_data.Vendor_Name  AS account_name,
    sony_applications_data.ArtistName  AS applicant_name,
    sony_applications_data.Country  AS country,
    sony_applications_data.SpotifyFollowers  AS spotify_followers,
    sony_applications_data.SpotifyMonthlyListeners  AS spotify_monthly_listeners,
    sony_applications_data.SpotifyUrl  AS spotify_url,
    sony_applications_data.YoutubeSubscribers  AS youtube_subscribers,
    sony_applications_data.YoutubeUrl  AS youtube_url,
    sony_applications_data.InstagramFollowers  AS instagram_followers,
    sony_applications_data.InstagramUrl  AS instagram_url,
    sony_applications_data.TiktokFollowers  AS tiktok_followers,
    sony_applications_data.TiktokUrl  AS tiktok_url,
    sony_applications_data.type  AS type,
    (TO_CHAR(TO_DATE(sony_applications_data.LastModifiedTimestamp ), 'YYYY-MM-DD')) AS last_modified_date,
    (TO_CHAR(TO_DATE(sony_applications_data.CreatedTimestamp ), 'YYYY-MM-DD')) AS created_date

FROM sony_applications_data
INNER JOIN digital_audio_products dap
        ON dap.label_id = sony_applications_data.VENDOR_ID
WHERE ((( sony_applications_data.LastName  ) NOT LIKE '%AWAL^_TEST%' ESCAPE '^'
    AND ( sony_applications_data.LastName  ) NOT LIKE '%TEST^_PROD%' ESCAPE '^'
    AND ( sony_applications_data.LastName  ) NOT LIKE '%TESTPROD%'
    AND ( sony_applications_data.LastName  ) NOT LIKE '%TestPROD%' OR ( sony_applications_data.LastName  ) IS NULL))
    AND sony_applications_data.VENDOR_ID IN (dap.label_id)
)


select distinct dap.review_submitted_date as date_flagged,
dap.label_name as label_name,
dap.label_id as label_id,
pv.created_date as account_start_date,
dap.upc as upc,
dap.brand as brand,
dap.moved_by as flagged_by,
dap.move_notes AS notes,
pv.number_of_releases as num_live_releases,
dap.active_status as is_active,
nr.applicant_id as applicant_id,
nr.vendor_id as vendor_id,
nr.voucher_code as voucher_code,
nr.account_name as account_name,
nr.applicant_name as applicant_name,
nr.country as country,
nr.spotify_followers as spotify_followers,
nr.spotify_monthly_listeners as spotify_monthly_listeners,
nr.spotify_url as spotify_url,
nr.youtube_subscribers as youtube_subscribers,
nr.youtube_url as youtube_url,
nr.instagram_followers as instagram_followers,
nr.instagram_url as instagram_url,
nr.tiktok_followers as tiktok_followers,
nr.tiktok_url as tiktok_url,
nr.type as type,
nr.last_modified_date as last_modified_date,
nr.created_date as created_date
FROM digital_audio_products dap
LEFT JOIN product_volume pv
    ON dap.label_id = pv.label_id
LEFT JOIN no_referrals nr
    ON dap.label_id = nr.vendor_id;;
```