SQL Pattern Quick Reference for Auditors

SQL Pattern Quick Reference for Auditors

This one-pager provides the five most common SQL query patterns auditors need. Each pattern includes a code snippet, audit purpose, and typical use case in banking audits. Remember the logic, not the exact code.

1. Duplicate Detection (GROUP BY + HAVING)

SQL Pattern:

SELECT InvoiceNumber, COUNT(*) AS NumOccurrences
FROM AccountsPayable
GROUP BY InvoiceNumber
HAVING COUNT(*) > 1;

Purpose: Identify duplicate records that may indicate fraud or errors.

Typical Use Case: Detect duplicate vendor payments or wire transfers.

2. Completeness Testing (LEFT JOIN + IS NULL)

SQL Pattern:

SELECT o.OrderID
FROM Orders o
LEFT JOIN Invoices i ON o.OrderID = i.OrderID
WHERE i.OrderID IS NULL;

Purpose: Find missing records in a linked process.

Typical Use Case: Identify orders without invoices or loans not booked into GL.

3. Reconciliation (SUM Comparisons)

SQL Pattern:

SELECT SUM(Subledger.Amount) AS SubledgerTotal,
       (SELECT SUM(GL.Amount) FROM GeneralLedger GL WHERE GL.Account = 'Revenue') AS GLTotal
FROM Subledger;

Purpose: Compare totals between two systems or sources.

Typical Use Case: Validate that subledger balances tie to the general ledger.

4. Outlier Detection (WHERE Filters)

SQL Pattern:

SELECT *
FROM Payments
WHERE Amount > 100000 OR Amount < 0;

Purpose: Identify unusual transactions outside normal thresholds.

Typical Use Case: Spot large or negative amounts that may signal fraud.

5. Ghost Users (JOIN with HR Roster)

SQL Pattern:

SELECT u.UserID, u.Username
FROM Users u
LEFT JOIN Employees e ON u.UserID = e.EmployeeID
WHERE e.EmployeeID IS NULL;

Purpose: Detect accounts not tied to active employees.

Typical Use Case: Find 'ghost users' with unauthorized access in banking syst