view: dt_weekly_spikes_v2 {
  # Or, you could make this view a derived table, like this:
  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,
              fa.storeid,
              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 BETWEEN CURRENT_DATE()-8 AND 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,6
          HAVING today_streams > {% parameter last_week_streams %}
      ),

      previous_streams AS (
          SELECT
              fa.labelid,
              zeroifnull(fa.subaccountid) AS subaccountid,
              fa.artistid,
              fa.releaseid,
              fa.trackid,
              fa.storeid,
              DATE_TRUNC('week', fa.download_activity_date) AS week,
              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 fa.download_activity_date BETWEEN CURRENT_DATE()- ((9 * {% parameter lookback_weeks %}) + 9) AND CURRENT_DATE()- 9
          AND YEAR(fa.download_activity_date) - u.birthyear BETWEEN {% parameter lower_age %} AND {% parameter upper_age %}
          GROUP BY 1,2,3,4,5,6,7
      ),

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

      SELECT
          ts.labelid,
          ts.subaccountid,
          ts.artistid,
          ts.releaseid,
          ts.trackid,
          ts.storeid,
          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
      AND ts.storeid=s.storeid
      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: storeid {
    view_label: "Store"
    type: number
    hidden: no
    sql: ${TABLE}.storeid;;
  }

  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.##"
  }

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

  filter: store_filter {
    view_label: "Metrics"
    type: number
    label: "Store ID"
    description: "Input 1 for Apple Music or 286 for Spotify"
  }

  parameter: lookback_weeks {
    type: unquoted
    default_value: "9"
    view_label: "Metrics"
    label: "Trending Window (Weeks)"
    description: "How many (weeks ago, starting from one week back) to look back for trending activity"
  }

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

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

  parameter: last_week_streams {
    type: number
    view_label: "Metrics"
    label: "Weekly Stream Threshold"
    description: "Minimum # of streams to look for in the past week"
    default_value: "35000"
  }

}