Approach: generate the past 30 dates, left-join transactions per day, compute per-column fraction of NULLs, and flag days where missing_rate > 0.05. Below are two implementations (Postgres and BigQuery). Assumptions: occurred_at is indexed/partitioned by date; dates use UTC truncation to day; treat empty strings as missing for varchar if needed. For large tables, use partition pruning (partition by date), filter by occurred_at range, and pre-aggregate with date-partitioned materialized views.Postgres:sql
WITH days AS (
SELECT generate_series(current_date - interval '29 day', current_date, interval '1 day')::date AS dt
),
txn_by_day AS (
SELECT (occurred_at::date) AS dt,
COUNT(*) AS total,
COUNT(transaction_id) FILTER (WHERE transaction_id IS NULL) AS miss_transaction_id,
COUNT(user_id) FILTER (WHERE user_id IS NULL) AS miss_user_id,
COUNT(amount) FILTER (WHERE amount IS NULL) AS miss_amount,
COUNT(currency) FILTER (WHERE currency IS NULL OR currency = '') AS miss_currency
FROM transactions
WHERE occurred_at >= current_date - interval '29 day' AND occurred_at < current_date + interval '1 day'
GROUP BY dt
)
SELECT
d.dt AS date,
col.column_name,
COALESCE(m.missing_count::numeric / NULLIF(COALESCE(t.total,0),0), 0) AS missing_rate,
(COALESCE(m.missing_count::numeric / NULLIF(COALESCE(t.total,0),0), 0) > 0.05) AS is_flagged
FROM days d
LEFT JOIN txn_by_day t ON t.dt = d.dt
JOIN LATERAL (
VALUES
('transaction_id', COALESCE(t.miss_transaction_id,0)),
('user_id', COALESCE(t.miss_user_id,0)),
('amount', COALESCE(t.miss_amount,0)),
('currency', COALESCE(t.miss_currency,0))
) AS m(column_name, missing_count) ON true
ORDER BY date, column_name;
sql
WITH days AS (
SELECT day
FROM UNNEST(GENERATE_DATE_ARRAY(CURRENT_DATE() - 29, CURRENT_DATE())) AS day
),
txn_by_day AS (
SELECT DATE(occurred_at) AS day,
COUNT(*) AS total,
SUM(IF(transaction_id IS NULL,1,0)) AS miss_transaction_id,
SUM(IF(user_id IS NULL,1,0)) AS miss_user_id,
SUM(IF(amount IS NULL,1,0)) AS miss_amount,
SUM(IF(currency IS NULL OR currency = '',1,0)) AS miss_currency
FROM `project.dataset.transactions`
WHERE DATE(occurred_at) BETWEEN CURRENT_DATE() - 29 AND CURRENT_DATE()
GROUP BY day
)
SELECT
d.day AS date,
col.column_name,
IFNULL(missing_count / NULLIF(t.total,0), 0) AS missing_rate,
IF(IFNULL(missing_count / NULLIF(t.total,0), 0) > 0.05, TRUE, FALSE) AS is_flagged
FROM days d
LEFT JOIN txn_by_day t ON t.day = d.day
CROSS JOIN UNNEST([
STRUCT('transaction_id' AS column_name, IFNULL(t.miss_transaction_id,0) AS missing_count),
('user_id', IFNULL(t.miss_user_id,0)),
('amount', IFNULL(t.miss_amount,0)),
('currency', IFNULL(t.miss_currency,0))
]) AS col
ORDER BY date, column_name;
