view: dt_trending_tracks {
  # Or, you could make this view a derived table, like this:
  derived_table: {
    sql:

  WITH region_codes AS (
    SELECT
      DISTINCT region_code
    FROM facts.prod.demographics_region_mapping_v2
    WHERE orchard_region_name ILIKE '%{% parameter region_filter %}%')


   , weekly_streams AS (
    SELECT
        fd.labelid,
        zeroifnull(fd.subaccountid) AS subaccountid,
        fd.artistid,
        fd.isrc,
        SUM(streams) AS weekly_streams
    FROM facts.prod.fact_demographics fd
    WHERE download_activity_date BETWEEN DATEADD(day,-8,CURRENT_DATE()) AND DATEADD(day,-2,CURRENT_DATE())
    AND {% condition gender_filter %} fd.gender {% endcondition %}
    AND {% condition store_filter %} fd.storeid {% endcondition %}
    AND {% condition country_filter %} fd.territory_code {% endcondition %}
    AND region_code IN (SELECT region_code FROM region_codes)
    GROUP BY 1,2,3,4
    HAVING weekly_streams >= {% parameter stream_baseline %}
), monthly_streams AS (
    SELECT
        labelid,
        zeroifnull(subaccountid) AS subaccountid,
        artistid,
        isrc,
        DATE_TRUNC('week',download_activity_date) AS week,
        SUM(streams) AS streams
    FROM facts.prod.fact_demographics fd
    WHERE download_activity_date BETWEEN DATEADD(day, -73, CURRENT_DATE()) AND DATEADD(day, -3, CURRENT_DATE())
    AND {% condition gender_filter %} fd.gender {% endcondition %}
    AND {% condition store_filter %} fd.storeid {% endcondition %}
    AND {% condition country_filter %} fd.territory_code {% endcondition %}
    AND region_code IN (SELECT region_code FROM region_codes)
    GROUP BY 1,2,3,4,5
), stats AS (
    SELECT
        labelid,
        subaccountid,
        artistid,
        isrc,
        AVG(streams) AS avrg,
        STDDEV(streams) AS std
    FROM monthly_streams
    GROUP BY 1,2,3,4
)

SELECT
    row_number() over (ORDER BY ws.isrc DESC) AS rowid,
    ws.labelid,
    ws.subaccountid,
    ws.artistid,
    ws.isrc,
    ws.weekly_streams,
    s.avrg AS expected_weekly_streams,
    s.std,
    ((ws.weekly_streams-s.avrg)/(nullif(s.avrg,0))*100) AS percent_above_avrg,
    (ws.weekly_streams-s.avrg) / (nullif(s.std,0)) AS spike_score
FROM weekly_streams ws
INNER JOIN stats s
    ON ws.labelid=s.labelid
    AND ws.subaccountid=s.subaccountid
    AND ws.artistid=s.artistid
    AND ws.isrc=s.isrc
WHERE spike_score > 1
ORDER BY 7 DESC
      ;;
  }

  dimension: rowid {
    type: number
    hidden: yes
    primary_key: yes
  }

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


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

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

  dimension: isrc {
    type: string
    hidden: yes
    sql: ${TABLE}.isrc ;;
  }

 dimension: weekly_streams {
    type: number
    sql: ${TABLE}.weekly_streams ;;
  }

  dimension: expected_weekly_streams {
    type: number
    sql: ${TABLE}.expected_weekly_streams ;;
    value_format: "0"
  }

  dimension: percent_above_avrg {
    type: number
    hidden: yes
    sql: ${TABLE}.percent_above_avrg;;
  }

  dimension: standard_deviation {
    type: number
    sql: ${TABLE}.std ;;
  }

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

parameter: gender_filter  {
  type: string
  allowed_value: {
    label: "Male"
    value: "male"
  }

  allowed_value: {
    label: "Female"
    value: "female"
  }

  allowed_value: {
    label: "Other"
    value: "other"
  }

}

parameter: store_filter {
  type: number
  allowed_value: {
    label: "Spotify"
    value: "286"
  }

  allowed_value: {
    label: "Apple Music"
    value: "1"
  }
}

parameter: region_filter {
  type: unquoted
  default_value: "_"
}

parameter: country_filter {
  type: string
  default_value: "US"
}

parameter: stream_baseline {
  type: number
  default_value: "1000"
  description: "Specify the minimum # of streams earned in the past week to be considered for spiking "
}


}