view: deezer_anomaly_testing {
  derived_table: {
    sql: WITH max_week AS (
              SELECT DATEADD(WEEK, -1, MAX(DATE_TRUNC('WEEK', activity_date))) AS max_wk
              FROM intelligence.dbt_prod.agg_deezer_model
          )
          SELECT
              'Deezer' AS store_name,
              DATE_TRUNC('WEEK', activity_date) AS week_start,
              COUNT(*) AS row_count,
              SUM(streams) AS total_streams,
              ROUND(
                  (
                      (row_count - LAG(row_count) OVER (ORDER BY week_start)) /
                      NULLIF(LAG(row_count) OVER (ORDER BY week_start), 0)
                  ) * 100, 2
              ) AS row_count_pct_change,
              ROUND(
                  (
                      (SUM(streams) - LAG(SUM(streams)) OVER (ORDER BY week_start)) /
                      NULLIF(LAG(SUM(streams)) OVER (ORDER BY week_start), 0)
                  ) * 100, 2
              ) AS streams_pct_change
          FROM intelligence.dbt_prod.agg_deezer_model
          WHERE DATE_TRUNC('WEEK', activity_date) >= (
              SELECT DATEADD(WEEK, -1, max_wk) FROM max_week
          )
          GROUP BY week_start ;;
  }

  measure: count {
    type: count
    drill_fields: [detail*]
  }

  dimension: store_name {
    type: string
    sql: ${TABLE}."STORE_NAME" ;;
  }

  dimension: week_start {
    type: date
    sql: ${TABLE}."WEEK_START" ;;
  }

  dimension: row_count {
    type: number
    sql: ${TABLE}."ROW_COUNT" ;;
  }

  dimension: total_streams {
    type: number
    sql: ${TABLE}."TOTAL_STREAMS" ;;
  }

  dimension: row_count_pct_change {
    type: number
    sql: ${TABLE}."ROW_COUNT_PCT_CHANGE" ;;

    html: {% if value > 20 %}

    <span style="color: white; background-color: #ea9999; padding: 2px 6px; border-radius: 4px;">{{ value | round: 2 }}%</span>
    {% elsif value < -20 %}
    <span style="color: white; background-color: #ea9999; padding: 2px 6px; border-radius: 4px;">{{ value | round: 2 }}%</span>
    {% else %}
    <span style="color: white; background-color: #72c275; padding: 2px 6px; border-radius: 4px;">{{ value | round: 2 }}%</span>
    {% endif %};;
  }

  dimension: streams_pct_change {
    type: number
    sql: ${TABLE}."STREAMS_PCT_CHANGE" ;;

     html: {% if value > 20 %}

    <span style="color: white; background-color: #ea9999; padding: 2px 6px; border-radius: 4px;">{{ value | round: 2 }}%</span>
    {% elsif value < -20 %}
    <span style="color: white; background-color: #ea9999; padding: 2px 6px; border-radius: 4px;">{{ value | round: 2 }}%</span>
    {% else %}
    <span style="color: white; background-color: #72c275; padding: 2px 6px; border-radius: 4px;">{{ value | round: 2 }}%</span>
    {% endif %};;


  }

  set: detail {
    fields: [
        store_name,
  week_start,
  row_count,
  total_streams,
  row_count_pct_change,
  streams_pct_change
    ]
  }
}