Data Quality and Governance Questions

WITH monthly_rev AS (
  SELECT
    customer_id,
    date_trunc('month', event_date)::date AS month,
    SUM(amount) AS amount
  FROM transactions
  GROUP BY customer_id, date_trunc('month', event_date)
),
rolling AS (
  SELECT
    customer_id,
    month,
    amount,
    -- rolling 6-month mean and stddev including current month
    AVG(amount) OVER (
      PARTITION BY customer_id
      ORDER BY month
      ROWS BETWEEN 5 PRECEDING AND CURRENT ROW
    ) AS rolling_mean,
    STDDEV_SAMP(amount) OVER (
      PARTITION BY customer_id
      ORDER BY month
      ROWS BETWEEN 5 PRECEDING AND CURRENT ROW
    ) AS rolling_stddev,
    -- count of months in window to ensure at least 2 points for stddev
    COUNT(*) OVER (
      PARTITION BY customer_id
      ORDER BY month
      ROWS BETWEEN 5 PRECEDING AND CURRENT ROW
    ) AS months_in_window
  FROM monthly_rev
)
SELECT
  customer_id,
  month,
  rolling_mean,
  rolling_stddev,
  amount,
  CASE
    WHEN rolling_stddev IS NULL OR rolling_stddev = 0 THEN NULL
    ELSE (amount - rolling_mean) / rolling_stddev
  END AS z_score
FROM rolling
WHERE months_in_window >= 2 -- require at least 2 months to compute stddev
  AND rolling_stddev IS NOT NULL
  AND ABS((amount - rolling_mean) / rolling_stddev) > 3
ORDER BY customer_id, month;

WITH normalized AS (
  SELECT
    user_id,
    lower(trim(email)) AS email_norm,
    name,
    created_at
  FROM users
),
ranked AS (
  SELECT
    user_id,
    email_norm,
    created_at,
    row_number() OVER (
      PARTITION BY email_norm
      ORDER BY created_at DESC, user_id DESC
    ) AS rn
  FROM normalized
)
SELECT
  d.user_id AS duplicate_user_id,
  c.user_id AS canonical_user_id
FROM ranked d
JOIN ranked c
  ON d.email_norm = c.email_norm
  AND c.rn = 1
WHERE d.rn > 1
ORDER BY email_norm, duplicate_user_id;