# View: sony_applications_data

**View Name:** sony_applications_data
**Table Source:** `Derived from: Art_Relations_Prod_Art_Relations.Vendor, Awal_Sax, Awal_Sax.Sax, Orchard_App_Reporting_V2.Art_Relations_Prod_Art_Relations, Prod.Audit_Event_Gda_Signup + 3 more`
**File Path:** `DOSC/Community_Support/applications_data/views/sony_applications_data.view.lkml`

## Overview

- **File Size:** 18875 bytes
- **Lines of Code:** 441
- **Dimensions:** 32
- **Measures:** 0
- **Dimension Groups:** 2
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `vendor_name` | string |
| `vendor_id` | number |
| `ApplicantId` | string |
| `FirstName` | string |
| `LastName` | string |
| `Email` | string |
| `Reviewer_FirstName` | string |
| `Reviewer_LastName` | string |
| `Reviewer_Email` | string |
| `is_referred` | string |
| `Voucher_Code` | string |
| `Voucher_Code_Owner` | string |
| `ArtistName` | string |
| `Country` | string |
| `type` | string |
| `DeezerFans` | number |
| `DeezerUrl` | string |
| `FacebookFollowers` | number |
| `FacebookUrl` | string |
| `InstagramFollowers` | number |
| `InstagramUrl` | string |
| `SoundcloudFollowers` | number |
| `SoundcloudUrl` | string |
| `SpotifyFollowers` | number |
| `SpotifyMonthlyListeners` | number |
| `SpotifyUrl` | string |
| `TiktokFollowers` | number |
| `TiktokUrl` | string |
| `TwitterFollowers` | number |
| `TwitterUrl` | string |
| `YoutubeSubscribers` | number |
| `YoutubeUrl` | string |

## Dimension Groups

| Name | Type |
|------|------|
| `LastModifiedTimestamp` | time |
| `CreatedTimestamp` | time |

## SQL Comments

- phatvend.vendor_id as labelid, phatvend.name as Artist, phatvend.company,gda_leads.artist_name,
- length(trim(gda_leads.artist_name)) > 2 and-- gda_leads.id = '00Q4W00001cmOeDUAU' and

## Derived Table

```sql
sql:
  Select a.* from (
Select distinct --phatvend.vendor_id as labelid, phatvend.name as Artist, phatvend.company,gda_leads.artist_name,
          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 --length(trim(gda_leads.artist_name)) > 2 and-- gda_leads.id = '00Q4W00001cmOeDUAU' and
    (   (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%'

       order by gda_leads.id
) a;;
```