view: sax_applications_data {
  derived_table: {
    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;;
  }

  dimension: submission_id {
    type: string
    sql: ${TABLE}.submission_id ;;
    label: "Submission ID"
  }

  dimension: email {
    type: string
    sql: ${TABLE}.email ;;
    label: "Email"
  }

  dimension: client_id {
    type: string
    sql: ${TABLE}.client_id ;;
    label: "Client ID"
  }

  dimension: first_name {
    type: string
    sql: ${TABLE}.first_name ;;
    label: "First Name"
  }

  dimension: last_name {
    type: string
    sql: ${TABLE}.last_name ;;
    label: "Last Name"
  }

  dimension: country {
    type: string
    sql: ${TABLE}.country ;;
    label: "Country"
  }

  dimension: mixpanel_id {
    type: string
    sql: ${TABLE}.mixpanel_id ;;
    label: "Mixpanel ID"
  }

  dimension: status {
    type: string
    sql: ${TABLE}.status ;;
    label: "Status"
  }

  dimension_group: submission_date {
    type: time
    timeframes: [
      raw,
      date
    ]
    convert_tz: no
    datatype: date
    sql: ${TABLE}.submission_date ;;
    label: "Submission Date"
  }

  dimension_group: processed_date {
    type: time
    timeframes: [
      raw,
      date
    ]
    convert_tz: no
    datatype: date
    sql: ${TABLE}.processed_date ;;
    label: "Processed Date"
  }

  dimension: days_between {
    type: number
    sql: ${TABLE}.days_between ;;
    label: "Days Between"
  }

  dimension: artist_label_company {
    type: string
    sql: ${TABLE}.artist_label_company ;;
    label: "Artist / Label / Company"
  }

  dimension: role {
    type: string
    sql: ${TABLE}.role ;;
    label: "Role"
  }

  dimension: previous_release {
    type: string
    sql: ${TABLE}.previous_release ;;
    label: "Previous Release"
  }

  dimension: website_url {
    type: string
    sql: ${TABLE}.website_url ;;
    label: "Website URL"
  }

  dimension: promotional_activty {
    type: string
    sql: ${TABLE}.promotional_activty ;;
    label: "Promotional Activty"
  }

  dimension: spotify_artist {
    type: string
    sql: ${TABLE}.spotify_artist ;;
    label: "Spotify Artist"
  }

  dimension: spotify_total_followers {
    type: number
    sql: ${TABLE}.spotify_total_followers ;;
    label: "Spotify Total Followers"
  }

  dimension: spotify_artist_popularity {
    type: number
    sql: ${TABLE}.spotify_artist_popularity ;;
    label: "Spotify Artist Popularity"
  }

  dimension: soundcloud_user {
    type: string
    sql: ${TABLE}.soundcloud_user ;;
    label: "SoundCloud User"
  }

  dimension: soundcloud_average_streams {
    type: number
    sql: ${TABLE}.soundcloud_average_streams ;;
    label: "SoundCloud Average Streams"
  }

  dimension: soundcloud_total_followers {
    type: number
    sql: ${TABLE}.soundcloud_total_followers ;;
    label: "SoundCloud Total Followers"
  }

  dimension: youtube_url {
    type: string
    sql: ${TABLE}.youtube_url ;;
    label: "YouTube URL"
  }

  dimension: youtube_channel_average_views {
    type: number
    sql: ${TABLE}.youtube_channel_average_views ;;
    label: "YouTube Channel Average Views"
  }

  dimension: youtube_video_views {
    type: number
    sql: ${TABLE}.youtube_video_views ;;
    label: "YouTube Video Views"
  }

  dimension: facebook_page {
    type: string
    sql: ${TABLE}.facebook_page ;;
    label: "Facebook Page"
  }

  dimension: facebook_likes {
    type: number
    sql: ${TABLE}.facebook_likes ;;
    label: "Facebook Likes"
  }

  dimension: instagram_user {
    type: string
    sql: ${TABLE}.instagram_user ;;
    label: "Instagram User"
  }

  dimension: instagram_total_followers {
    type: string
    sql: ${TABLE}.instagram_total_followers ;;
    label: "Instagram Total Followers"
  }

  dimension_group: first_upload_date {
    type: time
    timeframes: [
      raw,
      date
    ]
    convert_tz: no
    datatype: date
    sql: ${TABLE}.first_upload_date ;;
    label: "First Upload Date"
  }

  dimension: first_upload_days_between {
    type: number
    sql: ${TABLE}.first_upload_days_between ;;
    label: "First Upload Days Between"
  }

  dimension: upload_count {
    type: string
    sql: ${TABLE}.upload_count ;;
    label: "Upload Count"
  }
}