A few people asked which SQL query I used to calculate the first-digit frequency distribution in the previous post on Benford's Law. Here it is — short and sweet.
To get a first-digit frequency distribution from a column of energy readings, take the leading character of each value's text form, group by it, and count. One line of SQL does it. Watch out for negative numbers and positive values under 1.0 — they'll add spurious "0" and "−" buckets to your results.
Following the earlier post applying Benford’s Law to energy data, several people asked what query produced the first-digit frequency distribution. It’s a single grouped count on the leading character of each value:
select substring(value::text,1,1), count(*)
from dad_data
group by 1
order by 1;Note that if your column contains negative numbers, or positive floating-point numbers less than 1.0, you’ll get frequencies for the 0 and - symbols included in the results — so filter or clean those out before reading the distribution.
Benford’s Law — the first-digit law — is a phenomenological observation about the frequency distribution of leading digits in many (but not all) real-life sets of numerical data. In many naturally occurring collections of numbers, the small digits occur disproportionately often as the leading significant digit: roughly 30% of values begin with a 1, far fewer with a 9. Departures from that expected curve are a classic flag for fabricated or tampered data — which is why the test is a staple of fraud detection.
Small digits occur disproportionately often as leading significant digits — and energy meter data is no exception.
Interval meter readings span many orders of magnitude, which is exactly the condition under which Benford’s Law holds. That makes the first-digit test a cheap, fast sanity check on a new data feed — and a quiet sentinel for anomalies. The mathematical aside covers why the distribution looks the way it does, and it’s the same instinct behind AI-powered anomaly detection at scale.
From first-digit tests to anomaly detection at scale, we help organisations trust the numbers their systems produce. Let's talk.