Detecting fraud: Benford's law

01.2014 / Category: / Tags: |

Many people use PostgreSQL to store data and do some basic analysis. However, there is more to data than many people might think. Benford's law, for instance, is a mechanism, which is widely used in many fields. Especially accounting fraud and so on can be detected nicely using this very simple law of mathematics.

What is the idea of Benford's law? Basically the concept is simple: Not all numbers are created equally. If you look at data, which has been observed in a natural way, not all numbers are equally likely. If you look at the first digit of a number, you will see that “1” is a lot more likely than, say, “8” or “9”.

Benford's law in PostgreSQL

Implementing Benford's law in PostgreSQL is actually simple and easy to achieve. All yyou have to do is to look at the first digit of numbers you want to analyze and see, if these numbers are distributed as proposed by this simple law. If the distribution of numbers is not as expected we can safely assume that something is fishy.

To give Benford's law a try we just take some accounting data provided by the “Iowa Department of Management” ( Note, this is just some random data I have found on the web.
For our experiment we have chosen the first document in the list (“County Budget Summary FY14”).

So, let us see now, if those total expenditures listed in the report are expected to be fraudulent or not:

First of all we create ourselves a little helper table to store the data:

Then we copy the total expenditure column to a simple text file and remove those commas shown in the Excel sheet using some simple Vim magic (:1,$s/,//gi). This leaves us with a simple text file:

We can easily import this data into PostgreSQL:

Applying Benford's law

In SQL it is pretty easy to extract the first digit of a number. All we have to do now is to run a trivial aggregation and see, what we get:

Obviously smaller numbers are ways more likely than larger ones. It is exactly what we should expect from “proper” accounting data.

NOTE: If you want to check your observation, we suggest using a chi-square or a Kolmogorov-Smirnow test.

0 0 votes
Article Rating
Notify of
Inline Feedbacks
View all comments
CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf

+43 (0) 2622 93022-0

Get the newest PostgreSQL Info & Tools

    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    CYBERTEC PostgreSQL International GmbH
    Would love your thoughts, please comment.x
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram