Automated Reporting & Report Development Questions

-- duplicate orders by order_id or same customer+timestamp
WITH dup_order_ids AS (
  SELECT order_id, COUNT(*) AS cnt
  FROM orders
  GROUP BY order_id
  HAVING COUNT(*) > 1
),
dup_customer_ts AS (
  SELECT customer_id, order_ts, COUNT(*) AS cnt
  FROM orders
  GROUP BY customer_id, order_ts
  HAVING COUNT(*) > 1
)
SELECT 'dup_order_ids' AS issue, order_id::text AS key, cnt
FROM dup_order_ids
UNION ALL
SELECT 'dup_customer_ts', customer_id::text || '|' || order_ts::text, cnt
FROM dup_customer_ts;

WITH stats AS (
  SELECT customer_id,
         percentile_cont(0.25) WITHIN GROUP (ORDER BY order_amount) AS q1,
         percentile_cont(0.75) WITHIN GROUP (ORDER BY order_amount) AS q3
  FROM orders
  GROUP BY customer_id
),
flagged AS (
  SELECT o.*
  FROM orders o
  JOIN stats s USING (customer_id)
  WHERE o.order_amount < (s.q1 - 3*(s.q3 - s.q1))
     OR o.order_amount > (s.q3 + 3*(s.q3 - s.q1))
)
SELECT * FROM flagged;