# View: sax_applications_data

**View Name:** sax_applications_data
**Table Source:** `Derived from: Sax.Awal_Api_Services, Sax.Awal_App_Srv_History, Sax.Awal_Application_Api_Srvs, Sax.Awal_Applications, Sax.Awal_Soundcloud_Data + 3 more`
**File Path:** `DOSC/Community_Support/applications_data/views/sax_applications_data.view.lkml`

## Overview

- **File Size:** 8151 bytes
- **Lines of Code:** 286
- **Dimensions:** 29
- **Measures:** 0
- **Dimension Groups:** 3
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `submission_id` | string |
| `email` | string |
| `client_id` | string |
| `first_name` | string |
| `last_name` | string |
| `country` | string |
| `mixpanel_id` | string |
| `status` | string |
| `days_between` | number |
| `artist_label_company` | string |
| `role` | string |
| `previous_release` | string |
| `website_url` | string |
| `promotional_activty` | string |
| `spotify_artist` | string |
| `spotify_total_followers` | number |
| `spotify_artist_popularity` | number |
| `soundcloud_user` | string |
| `soundcloud_average_streams` | number |
| `soundcloud_total_followers` | number |
| `youtube_url` | string |
| `youtube_channel_average_views` | number |
| `youtube_video_views` | number |
| `facebook_page` | string |
| `facebook_likes` | number |
| `instagram_user` | string |
| `instagram_total_followers` | string |
| `first_upload_days_between` | number |
| `upload_count` | string |

## Dimension Groups

| Name | Type |
|------|------|
| `submission_date` | time |
| `processed_date` | time |
| `first_upload_date` | time |

## Derived Table

```sql
sql:
      SELECT app.id as submission_id
        , app.email as email
        , app.interested_party_id as client_id
        , app.first_name
        , app.last_name
        , territories.name as country
        , app.mixpanel_id as mixpanel_id
        , app.status as status
        , CAST(app.submission_date as date) as submission_date
        , CAST(app.processed_date as date) as processed_date
        , DATEDIFF(day, DATE_TRUNC('day',app.processed_date), DATE_TRUNC('day',app.submission_date)) as days_between
        , COALESCE(app.professional_name, app.company) artist_label_company
        , app.role as role
        , app.previous_release as previous_release
        , CASE WHEN app.website is null THEN 0 ELSE 1 END as website_url
        , CASE WHEN app.promotional_activity is null THEN 0 ELSE 1 END as promotional_activty
        , app.spotify_artist
        , MAX(spotify.total_followers) spotify_total_followers
        , MAX(spotify.artist_popularity) spotify_artist_popularity
        , app.soundcloud_user
        , MAX(soundcloud.average_streams) soundcloud_average_streams
        , MAX(soundcloud.total_followers) soundcloud_total_followers
        , app.youtube_url
        , MAX(yt_channels.average_views) youtube_channel_average_views
        , MAX(yt_videos.video_views) youtube_video_views
        , app.facebook_page
        , MAX(facebook.total_likes) facebook_likes
        , app.instagram_user
        , MAX(instagram.follower_count) instagram_total_followers
        , CAST(min(portal_rr_products.created_date) as date) first_upload_date
        , DATEDIFF(day, DATE_TRUNC('day',MIN(portal_rr_products.created_date)), DATE_TRUNC('day',app.processed_date)) first_upload_days_between
        , (
          SELECT count(portal_rr_products.id)
          FROM awal_sax.sax.portal_rr_products
          WHERE portal_rr_products.client_id = app.interested_party_id
          ) upload_count
      FROM   AWAL_SAX.SAX.awal_applications app
        INNER JOIN AWAL_SAX.SAX.awal_application_api_srvs asas ON asas.awal_application_id = app.id
        INNER JOIN AWAL_SAX.SAX.awal_api_services srv ON srv.id = asas.awal_api_service_id
        INNER JOIN AWAL_SAX.SAX.awal_app_srv_history h ON h.id = asas.last_history_id
        INNER JOIN AWAL_SAX.SAX.TERRITORIES ON TERRITORIES.ID = TERRITORY_ID
        LEFT JOIN AWAL_SAX.SAX.awal_spotify_data_sets spotify ON spotify.awal_app_srv_history_id = h.id
        LEFT JOIN AWAL_SAX.SAX.awal_spotify_tracks spotify_tracks ON spotify_tracks.awal_spotify_data_set_id = spotify.id
        LEFT JOIN AWAL_SAX.SAX.awal_soundcloud_data soundcloud ON soundcloud.awal_app_srv_history_id = h.id
        LEFT JOIN AWAL_SAX.SAX.awal_facebook_data_sets facebook ON facebook.awal_app_srv_history_id = h.id
        LEFT JOIN AWAL_SAX.SAX.awal_youtube_channels yt_channels ON yt_channels.awal_app_srv_history_id = h.id
        LEFT JOIN AWAL_SAX.SAX.awal_youtube_videos yt_videos ON yt_videos.awal_app_srv_history_id = h.id
        LEFT JOIN AWAL_SAX.SAX.portal_rr_products ON portal_rr_products.client_id = app.interested_party_id
        LEFT JOIN AWAL_SAX.DEV.AWAL_INSTAGRAM_DATA_SETS instagram ON instagram.awal_app_srv_history_id = h.id
      GROUP BY app.id
        , app.email
        , app.interested_party_id
        , app.first_name
        , app.last_name
        , TERRITORIES.NAME
        , app.mixpanel_id
        , app.status
        , CAST(app.submission_date AS DATE)
        , CAST(app.processed_date AS DATE)
        , datediff(day, date_trunc('day',app.processed_date), date_trunc('day',app.submission_date))
        , COALESCE(app.professional_name, app.company)
        , app.role
        , app.previous_release
        , CASE WHEN app.website IS NULL THEN 0 ELSE 1 END
        , CASE WHEN app.promotional_activity IS NULL THEN 0 ELSE 1 END
        , app.SPOTIFY_ARTIST
        , app.SOUNDCLOUD_USER
        , app.YOUTUBE_URL
        , app.FACEBOOK_PAGE
        , app.INSTAGRAM_USER
        , app.processed_date;;
```