view: anr_missing_days {
  derived_table: {
    sql: with missing_dates as (with update_dates as (select distinct a.artist,
               i.LAST_PROCESSING_DATE as "Instagram",
               s.LAST_PROCESSING_DATE as "Spotify",
               y.LAST_PROCESSING_DATE as "YouTube",
              ss.LAST_PROCESSING_DATE as "SoundCloud",
              sh.LAST_PROCESSING_DATE as "Shazam"
              //t.LAST_PROCESSING_DATE as "TikTok"
from TABLE({% parameter artist_table_name %}) A
left outer join TABLE({% parameter instagram_table_name %}) I on a.artist = i.artist
left outer join TABLE({% parameter spotify_table_name %}) S on a.artist = s.artist
left outer join TABLE({% parameter youtube_table_name %}) Y on a.artist = y.artist
left outer join TABLE({% parameter soundcloud_table_name %}) SS on a.artist = ss.artist
left outer join TABLE({% parameter shazam_table_name %}) SH on a.artist = sh.artist),
//left outer join TABLE({% parameter tiktok_table_name %}) T on a.artist = t.artist),
instagram as (select artist, "Instagram"
from update_dates where "Instagram" < DATEADD('day' ,-1, current_date())),
spotify as (select artist, "Spotify"
from update_dates where "Spotify" < DATEADD('day' ,-1, current_date())),
youtube as (select artist, "YouTube"
from update_dates where "YouTube" < DATEADD('day' ,-1, current_date())),
soundcloud as (select artist, "SoundCloud"
from update_dates where "SoundCloud" < DATEADD('day' ,-1, current_date())),
shazam as (select artist, "Shazam"
from update_dates where "Shazam" < DATEADD('day' ,-1, current_date()))
//tiktok as (select artist, "TikTok"
//from update_dates where "TikTok" < DATEADD('day' ,-1, current_date()))
select distinct a.artist,
               i."Instagram",
               s."Spotify",
               y."YouTube",
              sc."SoundCloud",
              sh."Shazam"
              //t."TikTok"
from TABLE({% parameter artist_table_name %}) A
left outer join instagram i on a.artist = i.artist
left outer join spotify s on a.artist = s.artist
left outer join youtube y on a.artist = y.artist
left outer join soundcloud sc on a.artist = sc.artist
left outer join shazam sh on a.artist = sh.artist
//left outer join tiktok t on a.artist = t.artist
where "Instagram" is not null
  or "Spotify" is not null
  or "YouTube" is not null
  or "SoundCloud" is not null
  or "Shazam" is not null)
//or "TikTok" is not null)
select artist, RTRIM(concat(
  CASE WHEN "Instagram" is not null THEN concat('Instagram: ', "Instagram", ', ') ELSE ''  END,
  CASE WHEN "Spotify" is not null THEN concat('Spotify: ', "Spotify", ', ') ELSE ''  END,
  CASE WHEN "YouTube" is not null THEN concat('YouTube: ', "YouTube", ', ') ELSE ''  END,
  CASE WHEN "SoundCloud" is not null THEN concat('SoundCloud: ', "SoundCloud", ', ') ELSE ''  END,
  CASE WHEN "Shazam" is not null THEN concat('Shazam: ', "Shazam", ', ') ELSE ''  END
//CASE WHEN "TikTok" is not null THEN concat('TikTok: ', "TikTok", ', ') ELSE ''  END
),', ' ) dates
                      from missing_dates;;
  }

  dimension: artist {
    type: string
    sql: ${TABLE}.artist;;
  }

  dimension: dates {
    type: string
    sql: ${TABLE}.dates;;
  }

  parameter: instagram_table_name {
    type: string
    view_label: "Instagram Table"
    default_value: "FACTS.PROD.INSTAGRAM_STATISTICS"
  }

  parameter: spotify_table_name {
    type: string
    view_label: "Spotify Table"
    default_value: "FACTS.PROD.SPOTIFY_STATISTICS"
  }

  parameter: soundcloud_table_name {
    type: string
    view_label: "SoundCloud Table"
    default_value: "FACTS.PROD.SOUNDCLOUD_STATISTICS"
  }

  parameter: youtube_table_name {
    type: string
    view_label: "YouTube Table"
    default_value: "FACTS.PROD.YOUTUBE_STATISTICS"
  }

  parameter: shazam_table_name {
    type: string
    view_label: "Shazam Table"
    default_value: "FACTS.PROD.SHAZAM_STATISTICS"
  }

  parameter: tiktok_table_name {
    type: string
    view_label: "TikTok Table"
    default_value: "FACTS.PROD.TIKTOK_STATISTICS"
  }

  parameter: artist_table_name {
    type: string
    view_label: "Artist Table Name"
    default_value: "FACTS.PROD.ARTIST_TO_TRACK"
  }
}

view: anr_missing_days_count {
  derived_table: {
    sql: with missing_dates as (with update_dates as (select distinct a.artist,
               i.LAST_PROCESSING_DATE as "Instagram",
               s.LAST_PROCESSING_DATE as "Spotify",
               y.LAST_PROCESSING_DATE as "YouTube",
              ss.LAST_PROCESSING_DATE as "SoundCloud",
              sh.LAST_PROCESSING_DATE as "Shazam",
               t.LAST_PROCESSING_DATE as "TikTok"
from TABLE({% parameter artist_table_name %}) A
left outer join TABLE({% parameter instagram_table_name %}) I on a.artist = i.artist
left outer join TABLE({% parameter spotify_table_name %}) S on a.artist = s.artist
left outer join TABLE({% parameter youtube_table_name %}) Y on a.artist = y.artist
left outer join TABLE({% parameter soundcloud_table_name %}) SS on a.artist = ss.artist
left outer join TABLE({% parameter shazam_table_name %}) SH on a.artist = sh.artist
left outer join TABLE({% parameter tiktok_table_name %}) T on a.artist = t.artist),
instagram as (select artist, "Instagram"
from update_dates where "Instagram" < DATEADD('day' ,-1, current_date())),
spotify as (select artist, "Spotify"
from update_dates where "Spotify" < DATEADD('day' ,-1, current_date())),
youtube as (select artist, "YouTube"
from update_dates where "YouTube" < DATEADD('day' ,-1, current_date())),
soundcloud as (select artist, "SoundCloud"
from update_dates where "SoundCloud" < DATEADD('day' ,-1, current_date())),
shazam as (select artist, "Shazam"
from update_dates where "Shazam" < DATEADD('day' ,-1, current_date())),
tiktok as (select artist, "TikTok"
from update_dates where "TikTok" < DATEADD('day' ,-1, current_date()))
select distinct a.artist,
               i."Instagram",
               s."Spotify",
               y."YouTube",
              sc."SoundCloud",
              sh."Shazam",
               t."TikTok"
from TABLE({% parameter artist_table_name %}) A
left outer join instagram i on a.artist = i.artist
left outer join spotify s on a.artist = s.artist
left outer join youtube y on a.artist = y.artist
left outer join soundcloud sc on a.artist = sc.artist
left outer join shazam sh on a.artist = sh.artist
left outer join tiktok t on a.artist = t.artist
where "Instagram" is not null
  or "Spotify" is not null
  or "YouTube" is not null
  or "SoundCloud" is not null
  or "Shazam" is not null
  or "TikTok" is not null)

select count("Instagram") as instagram_days, count("Spotify") as spotify_days,
       count("YouTube") as youtube_days, count("SoundCloud") as soundcloud_days,
       count("Shazam") as shazam_days, count("TikTok") as tiktok_days
from missing_dates;;
  }

  dimension: instagram {
    type: string
    sql: ${TABLE}.instagram_days;;
  }

  dimension: spotify {
    type: string
    sql: ${TABLE}.spotify_days;;
  }

  dimension: youtube {
    type: string
    sql: ${TABLE}.youtube_days;;
  }

  dimension: soundcloud {
    type: string
    sql: ${TABLE}.soundcloud_days;;
  }

  dimension: shazam {
    type: string
    sql: ${TABLE}.shazam_days;;
  }

  dimension: tiktok {
    type: string
    sql: ${TABLE}.tiktok_days;;
  }

  parameter: instagram_table_name {
    type: string
    view_label: "Instagram Table"
    default_value: "FACTS.PROD.INSTAGRAM_STATISTICS"
  }

  parameter: spotify_table_name {
    type: string
    view_label: "Spotify Table"
    default_value: "FACTS.PROD.SPOTIFY_STATISTICS"
  }

  parameter: soundcloud_table_name {
    type: string
    view_label: "SoundCloud Table"
    default_value: "FACTS.PROD.SOUNDCLOUD_STATISTICS"
  }

  parameter: youtube_table_name {
    type: string
    view_label: "YouTube Table"
    default_value: "FACTS.PROD.YOUTUBE_STATISTICS"
  }

  parameter: shazam_table_name {
    type: string
    view_label: "Shazam Table"
    default_value: "FACTS.PROD.SHAZAM_STATISTICS"
  }

  parameter: tiktok_table_name {
    type: string
    view_label: "TikTok Table"
    default_value: "FACTS.PROD.TIKTOK_STATISTICS"
  }

  parameter: artist_table_name {
    type: string
    view_label: "Artist Table Name"
    default_value: "FACTS.PROD.ARTIST_TO_TRACK"
  }
}