-- Optimized (illustrative pattern; adjust to your warehouse/db)
-- Reduce scan, filter early, join on keys, and optionally query a summary model.

WITH scoped_orders AS (
  SELECT
    DATE_TRUNC('day', o.event_time) AS event_date,
    o.customer_email,
    o.amount
  FROM orders o
  WHERE 1=1
    AND o.event_time >= NOW() - INTERVAL '90 days'
)
SELECT
  s.event_date AS day,
  c.segment,
  SUM(s.amount) AS revenue
FROM scoped_orders s
JOIN customers c
  ON s.customer_email = c.email
GROUP BY 1, 2
ORDER BY 1 DESC;

-- Naive (anti-patterns)
SELECT
  DATE_TRUNC('day', o.event_time) AS day,
  c.segment,
  SUM(o.amount) AS revenue
FROM orders o
JOIN customers c
  ON LOWER(o.customer_email) = LOWER(c.email)
WHERE DATE_TRUNC('day', o.event_time) >= DATE_TRUNC('day', NOW()) - INTERVAL '90 days'
GROUP BY 1, 2
ORDER BY 1 DESC;