# 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” (http://www.dom.state.ia.us/local/county/index.html). 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.

Article Rating
Subscribe
Notify of
Inline Feedbacks
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf
Austria

+43 (0) 2622 93022-0
office@cybertec.at

Support Platform
Get the newest PostgreSQL Info & Tools

2024
CYBERTEC PostgreSQL International GmbH

You are currently viewing a placeholder content from Facebook. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.

You are currently viewing a placeholder content from X. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.