view: dt_weekly_spikes {
  # 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,
        i.isrc,
        i.isrcname AS track_name,
        fa.storeid,
        SUM(fa.units) AS today_streams
    FROM facts.prod.fact_analytics fa
    INNER JOIN facts.prod.dim_isrc i ON i.isrcid=fa.isrcid
    INNER JOIN facts.prod.dim_licensor l ON l.licensorid=fa.licensorid
    WHERE fa.storeid IN (1,286,187)
    AND fa.transactiontypeid IN (1,10,48)
    AND fa.download_activity_date BETWEEN CURRENT_DATE()-8 AND CURRENT_DATE()-2
    AND fa.countryid IN (SELECT * FROM countries)
    AND l.distributor != 'sme'
    GROUP BY 1,2,3,4,5,6
    HAVING today_streams > {% parameter stream_threshold %}
),

previous_streams AS (
    SELECT
        fa.labelid,
        zeroifnull(fa.subaccountid) AS subaccountid,
        fa.artistid,
        i.isrc,
        i.isrcname AS track_name,
        fa.storeid,
        DATE_TRUNC('week', download_activity_date) AS week,
        SUM(fa.units) AS previous_streams
    FROM facts.prod.fact_analytics fa
    INNER JOIN facts.prod.dim_isrc i ON i.isrcid=fa.isrcid
    INNER JOIN facts.prod.dim_licensor l ON l.licensorid=fa.licensorid
    WHERE fa.storeid IN (1,286,187)
    AND fa.transactiontypeid IN (1,10,48)
    AND fa.countryid IN (SELECT * FROM countries)
    AND fa.download_activity_date BETWEEN CURRENT_DATE()- 72 AND CURRENT_DATE()- 9
    AND l.distributor != 'sme'
    GROUP BY 1,2,3,4,5,6,7
),

stats AS (
    SELECT
        labelid,
        subaccountid,
        artistid,
        isrc,
        track_name,
        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.isrc,
    u.upc,
    ts.track_name,
    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.isrc=s.isrc
AND ts.track_name=s.track_name
AND ts.storeid=s.storeid
INNER JOIN (SELECT
    t.upc,
    t.isrc,
    t.track_name,
    row_number() over (partition by t.isrc order by t.upc) AS row_number
FROM orchard_app_reporting_v2.art_relations_prod_art_relations.track t
INNER JOIN (SELECT * FROM orchard_app_reporting_v2.art_relations_prod_art_relations.releases WHERE distribution_format_id IN (1,57)
AND release_date > DATEADD(year,-1, CURRENT_DATE())) r ON r.upc=t.upc) u ON u.isrc=ts.isrc
WHERE spike_score > 1
AND u.row_number = 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}.upc;;
  }

  dimension: isrc {
    view_label: "Metrics"
    type: string
    hidden: no
    sql: ${TABLE}.isrc;;
  }

  dimension: track_name {
    view_label: "Metrics"
    type: string
    hidden: no
    sql: ${TABLE}.track_name;;
    link: {
      label: "Track View"
      url:
      "

      {% if _explore._name == 'dt_weekly_spikes' %}

      https://theorchard.looker.com/dashboards/1602?ISRC={{ dt_weekly_spikes.isrc._value }}&Artist%20Name={{ orch_app_ar_artist_info.name._value }}&Label%20ID={{ orch_app_ar_vendor.vendor_id._value }}&Activity%20Date=7%20days

      {% endif %}
      "
    }
  }

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

  parameter: stream_threshold {
    type: number
    default_value: "5000"
  }

}