view: dt_daily_spikes_v2 {
  derived_table: {
    sql: WITH countries AS (SELECT countryid FROM facts.prod.dim_country WHERE {% condition country_filter %} country_code {% endcondition %}),

      today_streams AS (
          SELECT
              fa.labelid,
              zeroifnull(fa.subaccountid) AS subaccountid,
              fa.artistid,
              fa.releaseid,
              fa.trackid,
              SUM(fa.units) AS today_streams
          FROM facts.prod.fact_analytics fa
          LEFT JOIN facts.prod.dim_user u ON fa.userid= u.userid
          WHERE {% condition store_filter %} fa.storeid {% endcondition %}
          AND fa.transactiontypeid IN (1,10)
          AND fa.download_activity_date = CURRENT_DATE()-2
          AND fa.countryid IN (SELECT DISTINCT countryid FROM countries)
          AND YEAR(fa.download_activity_date) - u.birthyear BETWEEN {% parameter lower_age %} AND {% parameter upper_age %}
          GROUP BY 1,2,3,4,5
          HAVING today_streams > 5000
      ),

      previous_streams AS (
          SELECT
              fa.labelid,
              zeroifnull(fa.subaccountid) AS subaccountid,
              fa.artistid,
              fa.releaseid,
              fa.trackid,
              fa.download_activity_date,
              SUM(fa.units) AS previous_streams
          FROM facts.prod.fact_analytics fa
          LEFT JOIN facts.prod.dim_user u ON fa.userid= u.userid
          WHERE {% condition store_filter %} fa.storeid {% endcondition %}
          AND fa.transactiontypeid IN (1,10)
          AND fa.countryid IN (SELECT DISTINCT countryid FROM countries)
          AND DAYOFWEEK(fa.download_activity_date) = DAYOFWEEK(CURRENT_DATE()-2)
          AND fa.download_activity_date > DATEADD(year,-1, CURRENT_DATE()-2)
          AND YEAR(fa.download_activity_date) - u.birthyear BETWEEN {% parameter lower_age %} AND {% parameter upper_age %}
          GROUP BY 1,2,3,4,5,6
      ),

      stats AS (
          SELECT
              labelid,
              subaccountid,
              artistid,
              releaseid,
              trackid,
              AVG(previous_streams) AS avrg,
              STDDEV(previous_streams) AS std
          FROM previous_streams
          GROUP BY 1,2,3,4,5
      )

      SELECT
          ts.labelid,
          ts.subaccountid,
          ts.artistid,
          ts.releaseid,
          ts.trackid,
          ts.today_streams,
          s.avrg AS average_streams,
          ((today_streams - average_streams) / (nullif(average_streams,0)) * 100) AS percent_above_average,
          s.std AS standard_deviation,
          (today_streams - average_streams) / (nullif(standard_deviation,0)) AS spike_score
      FROM today_streams ts
      INNER JOIN stats s
      ON ts.labelid=s.labelid
      AND ts.subaccountid=s.subaccountid
      AND ts.artistid=s.artistid
      AND ts.releaseid=s.releaseid
      AND ts.trackid=s.trackid
      WHERE spike_score > 1
            ;;
  }

  dimension: labelid {
    view_label: "Metrics"
    type: number
    hidden: yes
    sql: ${TABLE}.labelid;;
  }

  dimension: subaccountid {
    view_label: "Metrics"
    type: number
    hidden: yes
    sql: ${TABLE}.subaccountid;;
  }

  dimension: artistid {
    view_label: "Metrics"
    type: number
    hidden: yes
    sql: ${TABLE}.artistid;;
  }

  dimension: releaseid {
    view_label: "Metrics"
    type: number
    hidden: yes
    sql: ${TABLE}.labelid;;
  }

  dimension: trackid {
    view_label: "Metrics"
    type: number
    hidden: yes
    sql: ${TABLE}.trackid;;
  }

  dimension: today_streams {
    view_label: "Metrics"
    type: number
    hidden: no
    sql: ${TABLE}.today_streams;;
  }

  dimension: average_streams {
    view_label: "Metrics"
    type: number
    hidden: no
    sql: ${TABLE}.average_streams;;
    value_format: "#,##0"
  }

  dimension: percent_above_average {
    view_label: "Metrics"
    type: number
    hidden: no
    sql: ${TABLE}.percent_above_average;;
    value_format: "0.00\%"
  }

  dimension: spike_score {
    view_label: "Metrics"
    type: number
    hidden: no
    sql: ${TABLE}.spike_score;;
    value_format: "0.##"
  }

  dimension: days_since_release {
    view_label: "Release"
    type: number
    sql: DATEDIFF(day, ${orch_app_ar_releases.release_date}, CURRENT_DATE()-2) ;;
  }

  filter: country_filter {
    view_label: "Metrics"
    type: string
    label: "Country Code"
    description: "Input 2 Letter Country Code to Filter Spikes"
  }

  filter: store_filter {
    type: number
    view_label: "Metrics"
  }

  parameter: lower_age {
    type: number
    view_label: "Metrics"
  }

  parameter: upper_age {
    type: number
    view_label: "Metrics"
  }

}