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

WITH date_difference AS (
    SELECT
      CASE
        WHEN DAYNAME(CURRENT_DATE()-2) = 'Fri' THEN 0
        ELSE  DATEDIFF(day,CURRENT_DATE()-2,PREVIOUS_DAY(CURRENT_DATE()-2,'Friday'))
    END AS diff
    ),

date_definitions AS (
  SELECT
    TO_CHAR(CURRENT_DATE()-2) AS most_recent_day_label,
    TO_CHAR(DATEADD(day,(SELECT diff FROM date_difference), CURRENT_DATE()-2)) || ' - ' || TO_CHAR(CURRENT_DATE()-2) AS current_week_label,
    TO_CHAR(DATEADD(day,(SELECT diff FROM date_difference) - 7, CURRENT_DATE()-2)) || ' - ' || TO_CHAR(DATEADD(day,-7,CURRENT_DATE()-2)) AS last_week_label,
    TO_CHAR(DATEADD(day,(SELECT diff FROM date_difference) -7, CURRENT_DATE()-2)) || ' - ' || TO_CHAR(DATEADD(day,(SELECT diff FROM date_difference)-1, CURRENT_DATE()-2)) AS last_full_week_label
FROM date_difference
),

countries AS (SELECT countryid FROM facts.prod.dim_country WHERE {% condition country %} country_code {% endcondition %}),

fact_analytics AS (
    SELECT
      *
    FROM facts.prod.fact_analytics_v2
    WHERE labelid != 32251
    AND feedid = {% parameter store %}
    AND countryid IN (SELECT * FROM countries)

  ),

most_recent_day AS (
    SELECT
      labelid,
      artistid,
      trackid,
      SUM(units) AS most_recent_day_streams
  FROM fact_analytics
  WHERE download_activity_date = CURRENT_DATE()-2
  GROUP BY 1,2,3
  ORDER BY 4 DESC
  LIMIT {% parameter limit %}
  ),

current_week AS (
  SELECT
    labelid,
    artistid,
    trackid,
    SUM(units) AS current_week_streams
  FROM fact_analytics
  WHERE download_activity_date BETWEEN DATEADD(day,(SELECT diff FROM date_difference), CURRENT_DATE()-2) AND CURRENT_DATE()-2
  AND trackid IN (SELECT DISTINCT trackid FROM most_recent_day)
  AND labelid IN (SELECT DISTINCT labelid FROM most_recent_day)
  GROUP BY 1,2,3
),

last_week AS (
  SELECT
    labelid,
    artistid,
    trackid,
    SUM(units) AS last_week_streams
  FROM fact_analytics
  WHERE download_activity_date BETWEEN DATEADD(day,(SELECT diff FROM date_difference) -7, CURRENT_DATE()-2) AND DATEADD(day, -7, CURRENT_DATE()-2)
  AND trackid IN (SELECT DISTINCT trackid FROM most_recent_day)
  AND labelid IN (SELECT DISTINCT labelid FROM most_recent_day)
  GROUP BY 1,2,3
),

last_full_week AS (
  SELECT
    labelid,
    artistid,
    trackid,
    SUM(units) AS last_full_week_streams
  FROM fact_analytics
  WHERE download_activity_date BETWEEN DATEADD(day, (SELECT diff FROM date_difference) -7, CURRENT_DATE()-2) AND DATEADD(day, (SELECT diff FROM date_difference ) -1, CURRENT_DATE()-2)
  AND trackid IN (SELECT DISTINCT trackid FROM most_recent_day)
  AND labelid IN (SELECT DISTINCT labelid FROM most_recent_day)
GROUP BY 1,2,3
),

last_12_months AS (
  SELECT
    labelid,
    artistid,
    trackid,
    SUM(units) AS last_12month_streams
  FROM fact_analytics
  WHERE download_activity_date BETWEEN DATEADD(year,-1, CURRENT_DATE()-2) AND CURRENT_DATE()-2
  AND trackid IN (SELECT DISTINCT trackid FROM most_recent_day)
  AND labelid IN (SELECT DISTINCT labelid FROM most_recent_day)
  GROUP BY 1,2,3
)

  SELECT
    a.labelid,
    a.artistid,
    a.trackid,
    a.most_recent_day_streams,
    b.current_week_streams,
    c.last_week_streams,
    d.last_full_week_streams,
    e.last_12month_streams,
    (b.current_week_streams - c.last_week_streams) / (c.last_week_streams) AS weekly_trend
  FROM most_recent_day a
  LEFT JOIN current_week b ON b.trackid=a.trackid
  LEFT JOIN last_week c ON c.trackid=a.trackid
  LEFT JOIN last_full_week d ON d.trackid=a.trackid
  LEFT JOIN last_12_months e ON e.trackid=a.trackid
  ORDER BY 4 DESC;;
  }

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

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

  dimension: trackid {
    type: number
    hidden: no
    sql: ${TABLE}.trackid ;;
  }

  dimension: most_recent_date {
    type: date
    hidden: no
    sql: current_date()-2  ;;
  }

  dimension: most_recent_day {
    type: number
    sql: ${TABLE}.most_recent_day_streams;;
  }

  dimension: current_week_to_date {
    type: number
    sql: ${TABLE}.current_week_streams ;;
  }

  dimension: last_week_to_date {
    type: number
    sql: ${TABLE}.last_week_streams ;;
  }

  dimension: last_full_week {
    type: number
    sql: ${TABLE}.last_full_week_streams ;;
  }

  dimension: last_12_months {
    type: number
    sql: ${TABLE}.last_12month_streams ;;
  }

  dimension: weekly_trend_to_date {
    type: number
    sql: ${TABLE}.weekly_trend * 100 ;;
    value_format: "0.00\%"
  }

  parameter: store {
    type: number
    allowed_value: {
      label: "Spotify"
      value: "1"
    }

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

    allowed_value: {
      label: "iTunes"
      value: "13"
    }

    allowed_value: {
      label: "Amazon Unlimited"
      value: "8"
    }

  }

  filter: country {
    type: string
  }

  parameter: limit {
    type: unquoted
  }





}