Senior and Staff Readiness Questions

WITH params AS (
  SELECT 'step_start' AS start_event, 'step_finish' AS end_event -- replace with actual funnel_events[0] and last
),
daily_counts AS (
  SELECT
    CAST(e.occurred_at AS DATE) AS day,
    p.start_event,
    p.end_event,
    COUNT(DISTINCT CASE WHEN e.event_name = p.start_event THEN e.user_id END) AS start_users,
    COUNT(DISTINCT CASE WHEN e.event_name = p.end_event THEN e.user_id END) AS end_users
  FROM events e
  CROSS JOIN params p
  WHERE e.event_name IN (p.start_event, p.end_event)
  GROUP BY CAST(e.occurred_at AS DATE), p.start_event, p.end_event
),
rates AS (
  SELECT
    day,
    start_users,
    end_users,
    CASE WHEN start_users = 0 THEN NULL ELSE end_users::DECIMAL / start_users END AS conversion_rate
  FROM daily_counts
),
rolling AS (
  SELECT
    day,
    conversion_rate,
    AVG(conversion_rate) OVER (
      ORDER BY day
      ROWS BETWEEN 8 PRECEDING AND 1 PRECEDING
    ) AS prior_7d_avg -- average of the 7 days before current (adjust window size to 7)
  FROM rates
)
SELECT
  day,
  conversion_rate,
  prior_7d_avg,
  CASE
    WHEN prior_7d_avg IS NULL OR conversion_rate IS NULL THEN NULL
    ELSE (conversion_rate - prior_7d_avg) / NULLIF(prior_7d_avg,0)
  END AS relative_change,
  CASE
    WHEN prior_7d_avg IS NOT NULL
      AND conversion_rate IS NOT NULL
      AND ABS((conversion_rate - prior_7d_avg) / NULLIF(prior_7d_avg,0)) > 0.20
    THEN TRUE ELSE FALSE END AS drift_alert
FROM rolling
ORDER BY day DESC
LIMIT 30;