SQL-Based Data Validation and Anomaly Detection Questions

WITH stats AS (
  SELECT
    sensor_id,
    COUNT(*) AS total_readings,
    SUM(CASE WHEN temperature_c < -50 OR temperature_c > 150 THEN 1 ELSE 0 END) AS out_of_range
  FROM measurements
  GROUP BY sensor_id
),
filtered AS (
  SELECT
    sensor_id,
    total_readings,
    out_of_range,
    out_of_range::float / NULLIF(total_readings,0) AS out_fraction
  FROM stats
)
SELECT
  sensor_id,
  total_readings,
  out_of_range,
  out_fraction
FROM filtered
WHERE total_readings >= 1000       -- minimum sample threshold (see notes)
  AND out_fraction > 0.001
ORDER BY out_fraction DESC;

WITH months AS (
  SELECT DATE_TRUNC(dt, MONTH) AS month, payment_method
  FROM payments
  WHERE dt BETWEEN '2025-01-01' AND '2025-02-28'  -- adjust range to the two months of interest
),
counts AS (
  SELECT month, payment_method, COUNT(*) AS cnt
  FROM months
  GROUP BY month, payment_method
),
totals AS (
  SELECT month, SUM(cnt) AS month_total
  FROM counts
  GROUP BY month
),
shares AS (
  SELECT
    c.month,
    c.payment_method,
    SAFE_DIVIDE(c.cnt, t.month_total) * 100 AS pct  -- percent
  FROM counts c
  JOIN totals t USING(month)
),
pair AS (
  -- pivot or join the two months side-by-side; replace the two month dates as needed
  SELECT
    COALESCE(a.payment_method, b.payment_method) AS payment_method,
    a.pct AS pct_month_a,
    b.pct AS pct_month_b
  FROM shares a
  FULL JOIN shares b
    ON a.payment_method = b.payment_method
   AND a.month = DATE '2025-01-01'
   AND b.month = DATE '2025-02-01'
)
SELECT
  payment_method,
  COALESCE(pct_month_a, 0) AS pct_month_a,
  COALESCE(pct_month_b, 0) AS pct_month_b,
  (COALESCE(pct_month_b,0) - COALESCE(pct_month_a,0)) AS pct_point_change,
  ABS(COALESCE(pct_month_b,0) - COALESCE(pct_month_a,0)) > 10 AS flagged
FROM pair
ORDER BY ABS(pct_point_change) DESC;

-- PostgreSQL example
SELECT
  natural_key,
  COUNT(*) AS row_count,
  array_agg(DISTINCT email)   AS emails,
  array_agg(DISTINCT phone)   AS phones,
  array_agg(DISTINCT address) AS addresses
FROM customers
GROUP BY natural_key
HAVING COUNT(*) > 1
   AND (
       cardinality(array_agg(DISTINCT email))   > 1
    OR cardinality(array_agg(DISTINCT phone))   > 1
    OR cardinality(array_agg(DISTINCT address)) > 1
   );

-- Parameters to set:
-- :window_start    TIMESTAMP
-- :window_end      TIMESTAMP
-- :grace_seconds   INTEGER

WITH input_events AS (
  SELECT e.event_id, e.user_id, e.ts
  FROM events e
  WHERE e.ts >= :window_start
    AND e.ts <  :window_end
),
missing_users AS (
  -- events whose user_id is not present in current users table
  SELECT ie.event_id, ie.user_id, ie.ts
  FROM input_events ie
  LEFT JOIN users u ON ie.user_id = u.user_id
  WHERE u.user_id IS NULL
)
SELECT
  mu.event_id,
  mu.user_id,
  mu.ts,
  CASE
    WHEN mu.ts >= (CURRENT_TIMESTAMP - make_interval(secs => :grace_seconds))
      THEN 'probable-orphan'
    ELSE 'definitive-orphan'
  END AS orphan_classification
FROM missing_users mu
ORDER BY mu.ts DESC;