An application of Benford's Law using SQL

				


The law states that the frequency of the leading digit in a set of true randomly distributed numbers should occur with a probability that follows:

P(d) = log_b(d+1)-log_b(d) = log_b(1+1/d)



In base 10, this means that the number 1 should appear 30% of the time as the first digit, 2 should appear 17% of the time as the first digit, and so on up until 9, which should appear 4.5% of the time as the first digit. The full base 10 list is as follows:



1 - 30.1%
 
2 - 17.6%
 
3 - 12.5%
 
4 - 9.7%
 
5 - 7.9%

6 - 6.7%
 
7 - 5.8%
 
8 - 5.1%
 
9 - 4.6%



This is general overview of the SQL statements you will need to execute:



// Returns the number of values, not including NULL values, of the specified column

SELECT COUNT(column_name) FROM table_name



// Returns the number of values with 1 as the leading digit, which we will call 1_cnt

SELECT COUNT(column_name) FROM table_name

WHERE column_name LIKE '1%'



// Returns the number of values with 2 as the leading digit, which we will call 2_cnt

SELECT COUNT(column_name) FROM table_name

WHERE column_name LIKE '2%'

.

.

.

// Returns the number of values with 9 as the leading digit, which we will call 9_cnt

SELECT COUNT(column_name) FROM table_name

WHERE column_name LIKE '9%'



Now that we are done the counting, we can check the frequency of the leading digit against the chart. For our example, we will use a standard deviation of SQRT(2) to check if our data corresponds with the frequency of digits given by Benford Law.



Example:

if 28 < (1_cnt/total_cnt)*100 < 32 then

	
The data fits for the numbers with leading digit 1

if 15 < (2_cnt/total_cnt)*100 < 19 then

	
The data fits for the numbers with leading digit 2

.

.

.

if 2 < (9_cnt/total_cnt)*100 < 6 then

	
The data fits for the numbers with leading digit 9