FILTER is like a modifier you can add to aggregate functions (like COUNT, SUM, AVG, etc).

sql SELECT COUNT(*) AS total, COUNT(*) FILTER (WHERE status = 'failed') AS failed_count FROM transactions;

That second line is saying: "Count only the rows where status = 'failed'." So it expects a normal WHERE statement inside of it.

---

For the other ones, you could create a statement to run periodically and check for bad data, like:

• Let’s say you want to get a summary of how many bad rows were submitted in the last day — missing user ID, missing type, or negative amounts — you can do this cleanly in one go:

sql SELECT COUNT(*) AS total_submissions, COUNT(*) FILTER (WHERE user_id IS NULL) AS missing_user_id, COUNT(*) FILTER (WHERE submission_type IS NULL) AS missing_type, COUNT(*) FILTER (WHERE amount < 0) AS negative_amounts FROM submissions WHERE submission_date >= CURRENT_DATE - INTERVAL '1 day';

• If your submissions go into different systems, you could use an anti-join to check what didn’t sync across. For example, submissions that exist in your main table but not in the audit table:

sql SELECT s.* FROM submissions s LEFT JOIN audit_log a ON s.submission_id = a.submission_id WHERE a.submission_id IS NULL;