view:  anr_followers_statistics
{
  derived_table: {
    sql: WITH INSTAGRAM AS (
        WITH tmp AS (
          SELECT
            *,
            ROW_NUMBER() OVER (
              ORDER BY
                LAST_PROCESSING_DATE
            ) AS rn
          FROM
            TABLE({% parameter instagram_table_name %})
          WHERE
            ARTIST = {% parameter artist_variable %}
        )
        SELECT
          (
            mp.CURRENT_FOLLOWERS_COUNT - mc.CURRENT_FOLLOWERS_COUNT
          ) as CURRENT_FOLLOWERS_COUNT_diff,
          mp.LAST_PROCESSING_DATE,
          mp.ARTIST
        FROM
          tmp mc
          JOIN tmp mp ON mc.rn = mp.rn - 1
      ),
      SPOTIFY AS (
        WITH tmp AS (
          SELECT
            *,
            ROW_NUMBER() OVER (
              ORDER BY
                LAST_PROCESSING_DATE
            ) AS rn
          FROM
            TABLE({% parameter spotify_table_name %})
          WHERE
            ARTIST = {% parameter artist_variable %}
        )
        SELECT
          (
            mp.CURRENT_FOLLOWERS_COUNT - mc.CURRENT_FOLLOWERS_COUNT
          ) AS CURRENT_FOLLOWERS_COUNT_diff,
          mp.LAST_PROCESSING_DATE,
          mp.ARTIST
        FROM
          tmp mc
          JOIN tmp mp ON mc.rn = mp.rn - 1
      ),
      YOUTUBE AS (
        WITH tmp AS (
          SELECT
            *,
            ROW_NUMBER() OVER (
              ORDER BY
                LAST_PROCESSING_DATE
            ) AS rn
          FROM
            TABLE({% parameter youtube_table_name %})
          WHERE
            ARTIST = {% parameter artist_variable %}
        )
        SELECT
          (
            mp.CURRENT_SUBSCRIBERS_COUNT - mc.CURRENT_SUBSCRIBERS_COUNT
          ) AS CURRENT_SUBSCRIBERS_COUNT_diff,
          mp.LAST_PROCESSING_DATE,
          mp.ARTIST
        FROM
          tmp mc
          JOIN tmp mp ON mc.rn = mp.rn - 1
      ),
      SOUNDCLOUD AS (
        WITH tmp AS (
          SELECT
            *,
            ROW_NUMBER() OVER (
              ORDER BY
                LAST_PROCESSING_DATE
            ) AS rn
          FROM
            TABLE({% parameter soundcloud_table_name %})
          WHERE
            ARTIST = {% parameter artist_variable %}
        )
        SELECT
          (
            mp.CURRENT_FOLLOWERS_COUNT - mc.CURRENT_FOLLOWERS_COUNT
          ) AS CURRENT_FOLLOWERS_COUNT_diff,
          mp.LAST_PROCESSING_DATE,
          mp.ARTIST
        FROM
          tmp mc
          JOIN tmp mp ON mc.rn = mp.rn - 1
      ),
      DATES AS (
        SELECT
          SS.ARTIST,
          SS.LAST_PROCESSING_DATE
        FROM
          TABLE({% parameter spotify_table_name %}) SS
        UNION
        SELECT
          IS_.ARTIST,
          IS_.LAST_PROCESSING_DATE
        FROM
          TABLE({% parameter instagram_table_name %}) IS_
        UNION
        SELECT
          SCS.ARTIST,
          SCS.LAST_PROCESSING_DATE
        FROM
          TABLE({% parameter soundcloud_table_name %}) SCS
        UNION
        SELECT
          YS.ARTIST,
          YS.LAST_PROCESSING_DATE
        FROM
          TABLE({% parameter youtube_table_name %}) YS
      )
      SELECT
        DISTINCT DATES.ARTIST,
        DATES.LAST_PROCESSING_DATE,
        SPOTIFY.CURRENT_FOLLOWERS_COUNT_diff AS "Spotify Followers",
        SS.THE_LATEST_RELEASE,
        INSTAGRAM.CURRENT_FOLLOWERS_COUNT_diff AS "Instagram Followers",
        IS_.CURRENT_POSTS_COUNT,
        IS_.WEEKLY_CHANGE_IN_POSTS,
        SOUNDCLOUD.CURRENT_FOLLOWERS_COUNT_diff AS "SoundCloud Followers",
        SCS.CURRENT_TRACKS_COUNT,
        SCS.WEEKLY_CHANGE_IN_TRACKS_COUNT,
        YOUTUBE.CURRENT_SUBSCRIBERS_COUNT_diff AS "YouTube Subscribers",
        YS.VIDEOS_COUNT
      FROM
        DATES
        LEFT OUTER JOIN SPOTIFY ON SPOTIFY.LAST_PROCESSING_DATE = DATES.LAST_PROCESSING_DATE
        AND SPOTIFY.ARTIST = DATES.ARTIST
        LEFT OUTER JOIN INSTAGRAM ON INSTAGRAM.LAST_PROCESSING_DATE = DATES.LAST_PROCESSING_DATE
        AND INSTAGRAM.ARTIST = DATES.ARTIST
        LEFT OUTER JOIN YOUTUBE ON YOUTUBE.LAST_PROCESSING_DATE = DATES.LAST_PROCESSING_DATE
        AND YOUTUBE.ARTIST = DATES.ARTIST
        LEFT OUTER JOIN SOUNDCLOUD ON SOUNDCLOUD.LAST_PROCESSING_DATE = DATES.LAST_PROCESSING_DATE
        AND SOUNDCLOUD.ARTIST = DATES.ARTIST
        LEFT OUTER JOIN TABLE({% parameter spotify_table_name %}) SS ON DATES.LAST_PROCESSING_DATE = SS.LAST_PROCESSING_DATE
        AND DATES.ARTIST = SS.ARTIST
        LEFT OUTER JOIN TABLE({% parameter instagram_table_name %}) IS_ ON DATES.LAST_PROCESSING_DATE = IS_.LAST_PROCESSING_DATE
        AND DATES.ARTIST = IS_.ARTIST
        LEFT OUTER JOIN TABLE({% parameter soundcloud_table_name %}) SCS ON DATES.LAST_PROCESSING_DATE = SCS.LAST_PROCESSING_DATE
        AND DATES.ARTIST = SCS.ARTIST
        LEFT OUTER JOIN TABLE({% parameter youtube_table_name %}) YS ON DATES.LAST_PROCESSING_DATE = YS.LAST_PROCESSING_DATE
        AND DATES.ARTIST = YS.ARTIST
      WHERE
        SPOTIFY.LAST_PROCESSING_DATE >= DATEADD(
          'day',
          -29,
          CURRENT_DATE()
        )
        AND DATES.ARTIST = {% parameter artist_variable %}
      GROUP BY
        INSTAGRAM.LAST_PROCESSING_DATE,
        SPOTIFY.CURRENT_FOLLOWERS_COUNT_diff,
        INSTAGRAM.CURRENT_FOLLOWERS_COUNT_diff,
        SOUNDCLOUD.CURRENT_FOLLOWERS_COUNT_diff,
        YOUTUBE.CURRENT_SUBSCRIBERS_COUNT_diff,
        DATES.ARTIST,
        DATES.LAST_PROCESSING_DATE,
        SS.THE_LATEST_RELEASE,
        IS_.CURRENT_POSTS_COUNT,
        IS_.WEEKLY_CHANGE_IN_POSTS,
        SCS.CURRENT_TRACKS_COUNT,
        SCS.WEEKLY_CHANGE_IN_TRACKS_COUNT,
        YS.VIDEOS_COUNT;;
  }

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

  dimension: date {
    type: date
    sql: ${TABLE}.LAST_PROCESSING_DATE;;
  }

  measure: Spotify {
    type: max
    sql:  ${TABLE}."Spotify Followers";;
  }

  dimension: the_latest_Spotify_release {
    type: date
    sql: ${TABLE}.THE_LATEST_RELEASE;;
  }

  measure: Instagram {
    type: max
    sql:  ${TABLE}."Instagram Followers";;
  }

  dimension: current_Instagram_posts_count {
    type: number
    sql: ${TABLE}.CURRENT_POSTS_COUNT;;
  }

  dimension: weekly_change_in_posts {
    type: number
    sql: ${TABLE}.WEEKLY_CHANGE_IN_POSTS;;
  }

  measure: SoundCloud {
    type: max
    sql:  ${TABLE}."SoundCloud Followers";;
  }

  dimension: current_SoundCloud_tracks_count {
    type: number
    sql: ${TABLE}.CURRENT_TRACKS_COUNT;;
  }

  dimension: weekly_change_in_tracks_count {
    type: number
    sql: ${TABLE}.WEEKLY_CHANGE_IN_TRACKS_COUNT;;
  }

  measure: YouTube {
    type: max
    sql:  ${TABLE}."YouTube Subscribers";;
  }

  dimension: YouTube_videos_count {
    type: number
    sql: ${TABLE}.VIDEOS_COUNT;;
  }

  parameter: artist_variable {
    type: string
    view_label: "Artist"
  }

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

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

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

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