Write a query to identify vendors whose invoice amounts differ by more than 15% from their historical average.

Answer

Compute each vendor's historical average invoice amount, then flag invoices that deviate from it by more than 15% in either direction.

Solution

WITH vendor_avg AS (
  SELECT vendor_id, AVG(invoice_amount) AS avg_amount
  FROM Invoices
  GROUP BY vendor_id
)
SELECT i.invoice_id, i.vendor_id, i.invoice_amount, v.avg_amount,
       ROUND(100.0 * (i.invoice_amount - v.avg_amount) / v.avg_amount, 2) AS pct_deviation
FROM Invoices i
JOIN vendor_avg v ON v.vendor_id = i.vendor_id
WHERE ABS(i.invoice_amount - v.avg_amount) / v.avg_amount > 0.15;

Step by Step

1. The vendor_avg CTE computes each vendor's average invoice amount across their full history.
2. Each individual invoice is joined back to its own vendor's average.
3. ABS(invoice_amount - avg_amount) / avg_amount > 0.15 flags any invoice - whether abnormally high or abnormally low - that differs from that vendor's typical amount by more than 15%.

Alternative: Excluding the Current Invoice from Its Own Average

SELECT i1.invoice_id, i1.vendor_id, i1.invoice_amount,
       (SELECT AVG(i2.invoice_amount) FROM Invoices i2
        WHERE i2.vendor_id = i1.vendor_id AND i2.invoice_id <> i1.invoice_id) AS avg_amount_excl_self
FROM Invoices i1
WHERE ABS(i1.invoice_amount - (
        SELECT AVG(i2.invoice_amount) FROM Invoices i2
        WHERE i2.vendor_id = i1.vendor_id AND i2.invoice_id <> i1.invoice_id
      )) / (SELECT AVG(i2.invoice_amount) FROM Invoices i2
            WHERE i2.vendor_id = i1.vendor_id AND i2.invoice_id <> i1.invoice_id) > 0.15;

The first solution includes every invoice - including the one being checked - in its own vendor's average, which means one genuinely huge outlier invoice can pull the average toward itself and make the deviation look smaller than it really is. Excluding the current invoice from its own baseline average (as shown here) gives a cleaner, less self-referential anomaly check, at the cost of a noticeably more complex query.

Key Exam Traps

• Self-inclusion bias is the sophisticated point of this question: Including the invoice being tested inside the average it's being compared against is a subtle statistical flaw - flagging this unprompted, as in the alternative method, is exactly the kind of detail that distinguishes a strong analytics candidate.
• ABS() is required for "differ by more than 15%" - without it, the condition only catches invoices above average by that much, silently missing unusually low invoices, which are often just as suspicious (e.g. potential data entry errors).
• Vendors with very few invoices produce an unstable average that any single invoice heavily influences - flag this as a caveat on the reliability of the result for low-volume vendors.