WITH 
-- Durchschnittliche Search-Werte der letzten 7 Tage
CurrentPeriod AS (
  SELECT
    AccountId,
    TargetGroupName,
    AVG(Search) AS avg_search_current
  FROM
    `dein-bq-projekt-und-dataset.Audience_Sizes_Monitoring`
  WHERE
    Date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) AND CURRENT_DATE()
  GROUP BY
    AccountId, TargetGroupName
),

-- Durchschnittliche Search-Werte des entsprechenden Vorjahreszeitraums
PreviousYearPeriod AS (
  SELECT
    AccountId,
    TargetGroupName,
    AVG(Search) AS avg_search_previous
  FROM
    `dein-bq-projekt-und-dataset.Audience_Sizes_Monitoring`
  WHERE
    Date BETWEEN DATE_SUB(DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY), INTERVAL 1 YEAR) 
               AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR)
  GROUP BY
    AccountId, TargetGroupName
)

-- Vergleich der beiden Zeiträume
SELECT
  c.AccountId,
  c.TargetGroupName,
  c.avg_search_current AS avg_search_last_7_days,
  p.avg_search_previous AS avg_search_previous_year,
  CASE
    -- Fall 1: Sowohl aktuell als auch Vorjahr hatten Wert 0
    WHEN c.avg_search_current = 0 AND p.avg_search_previous = 0 THEN 0
    -- Fall 2: Vorjahr hatte Wert 0, jetzt ist ein Wert vorhanden (unendliches Wachstum)
    WHEN p.avg_search_previous = 0 AND c.avg_search_current > 0 THEN 100
    -- Fall 3: Vorjahr hatte Wert, jetzt 0 (100% Rückgang)
    WHEN c.avg_search_current = 0 AND p.avg_search_previous > 0 THEN -100
    -- Fall 4: Normale Berechnung für alle anderen Fälle
    ELSE ROUND((c.avg_search_current - p.avg_search_previous) / p.avg_search_previous * 100, 2)
  END AS percentage_change,
  CASE
    -- Fälle, die als "mindestens 50% Rückgang" gelten sollen
    WHEN c.avg_search_current = 0 AND p.avg_search_previous > 0 THEN 'Mindestens 50% Rückgang (100% Rückgang)'
    WHEN c.avg_search_current <= p.avg_search_previous * 0.5 AND p.avg_search_previous > 0 THEN 'Mindestens 50% Rückgang'
    -- Sonderfälle
    WHEN c.avg_search_current = 0 AND p.avg_search_previous = 0 THEN 'Keine Änderung (beide 0)'
    WHEN c.avg_search_current > 0 AND p.avg_search_previous = 0 THEN 'Wachstum von 0'
    ELSE 'Weniger als 50% Rückgang'
  END AS status
FROM
  CurrentPeriod c
LEFT JOIN
  PreviousYearPeriod p
  ON c.AccountId = p.AccountId AND c.TargetGroupName = p.TargetGroupName
WHERE
  -- Filter für 50% Rückgang oder aktuell 0
  avg_search_previous is not null
  and avg_search_previous>1000
  and
  (
    (c.avg_search_current <= COALESCE(p.avg_search_previous * 0.5, 0) AND p.avg_search_previous > 0)
    OR c.avg_search_current = 0
  )
ORDER BY
  percentage_change ASC,
  c.AccountId,
  c.TargetGroupName;