Problem Framing and Data Driven Recommendations Questions

SELECT customer_id, order_ref, amount, DATE_TRUNC('minute', created_at) AS minute_bucket,
       COUNT(*) AS cnt, MIN(id) AS first_id, MIN(created_at) AS first_ts
FROM transactions
GROUP BY 1,2,3,4
HAVING COUNT(*) > 1;

SELECT t1.id, t2.id, t1.customer_id, t1.amount, t1.created_at, t2.created_at
FROM transactions t1
JOIN transactions t2
  ON t1.customer_id = t2.customer_id
 AND ABS(EXTRACT(EPOCH FROM (t1.created_at - t2.created_at))) < 120
 AND t1.amount = t2.amount
 AND t1.id < t2.id;

INSERT INTO transaction_dedup_audit (canonical_id, duplicate_id, rule, run_ts)
SELECT first_id AS canonical_id, id AS duplicate_id, 'earliest' AS rule, now()
FROM (
  SELECT id, MIN(id) OVER (PARTITION BY customer_id, order_ref, amount, minute_bucket) AS first_id
  FROM (
    SELECT id, customer_id, order_ref, amount, DATE_TRUNC('minute', created_at) AS minute_bucket
    FROM transactions
  ) s
) x
WHERE id <> first_id
ON CONFLICT (duplicate_id) DO NOTHING;

SELECT t.date, SUM(t.amount) AS orig_rev, SUM(coalesce(d.correction_amount,0)) AS adjustments
FROM transactions t
LEFT JOIN transaction_dedup_audit a ON t.id = a.duplicate_id
LEFT JOIN dedup_corrections d ON a.duplicate_id = d.duplicate_id
GROUP BY t.date;

WITH user_txns AS (
  SELECT
    t.*,
    -- windowing by time using a correlated aggregation for stability across DBs
    AVG(t2.amount) AS rolling_mean,
    STDDEV_SAMP(t2.amount) AS rolling_stddev,
    COUNT(*) AS rolling_n
  FROM transactions t
  JOIN transactions t2
    ON t.user_id = t2.user_id
    AND t2.occurred_at BETWEEN t.occurred_at - INTERVAL '365 days' AND t.occurred_at
  GROUP BY t.transaction_id, t.user_id, t.amount, t.occurred_at
)
SELECT
  transaction_id,
  user_id,
  amount,
  occurred_at,
  rolling_n,
  rolling_mean,
  rolling_stddev,
  CASE
    WHEN rolling_n < 10 THEN 'insufficient_samples'
    WHEN rolling_stddev = 0 THEN 'no_variation'
    WHEN ABS((amount - rolling_mean) / rolling_stddev) > 3 THEN 'outlier'
    ELSE 'normal'
  END AS flag,
  CASE
    WHEN rolling_n >= 10 AND rolling_stddev > 0 THEN (amount - rolling_mean) / rolling_stddev
    ELSE NULL
  END AS z_score
FROM user_txns;