Counting Customers in PostgreSQL

The Hardest Query Is Often the Simplest

Database consultants see it all the time: a reporting request that looks trivial on the surface but hides a tangle of ambiguity underneath. The trouble usually isn't SQL syntax, query plans, or indexes. It's two things that no query language can help with: awareness of what your data actually represents and clarity about what business question you're really asking.

Consider the most basic business metric there is: “How many customers do you have?” Every executive will quote a number proudly. But ask them to define “customer” and the conversation usually goes sideways. The questions pile up fast:

• When a person stops ordering, how long before they no longer count?
• If two companies merge and rebrand, is that one customer or two?
• If someone orders, returns the item, and never pays, were they ever a customer?

Nobody gets far enough to worry about joins, window functions, or indexes when the count itself hasn't been pinned down.

Banking Example: One Schema, Many Answers

A bank database storing people, accounts, and account access rights illustrates the problem. A married couple might share an account; a company may allow several bookkeepers to sign. Real life is messy, and the schema has to accommodate it:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

CREATE TABLE t_entity
(
 entity_id int PRIMARY KEY,
 name text,
 is_company boolean
);
INSERT INTO t_entity VALUES
 (1, 'Joe', 'false'),
 (2, 'Jane', 'false'),
 (3, 'Jack', 'false'),
 (4, 'Janette', 'false'),
 (5, 'Company Ltd', 'true'),
 (6, 'Enterprise Ltd', 'true'),
 (7, 'FakerFake Inc', 'true')
;
CREATE TABLE t_account
(
 account_id text PRIMARY KEY,
 primary_owner int REFERENCES t_entity(entity_id),
 balance numeric(16, 2) DEFAULT 0
);
INSERT INTO t_account VALUES 
 ('ACC001', 2, 1000),
 ('ACC002', 3, 2954),
 ('COMP003', 5, 2789),
 ('COMP004', 6, 9863)
;
CREATE TABLE t_allowed_use
(
 account_id text,
 entity_id int,
 UNIQUE (account_id, entity_id)
);
INSERT INTO t_allowed_use VALUES 
 ('COMP003', 1),
 ('COMP003', 2),
 ('COMP003', 3)
;

Given that data, there are several defensible ways to count “customers,” and they produce different numbers.

The most naive answer is to count accounts:

1
2
3
4
5
6
7
8
9
10

test=# SELECT * 
FROM t_entity AS e, t_account AS a 
WHERE a.primary_owner = e.entity_id;
 entity_id |      name      | is_company | account_id | primary_owner | balance 
-----------+----------------+------------+------------+---------------+---------
         2 | Jane           | f          | ACC001     |             2 | 1000.00
         3 | Jack           | f          | ACC002     |             3 | 2954.00
         5 | Company Ltd    | t          | COMP003    |             5 | 2789.00
         6 | Enterprise Ltd | t          | COMP004    |             6 | 9863.00
(4 rows)

That approach has obvious holes. Anyone with two accounts gets counted twice, and a primary owner who died decades ago still counts. In Europe, legislation often dictates that unclaimed funds revert to the bank after enough time—so a count of accounts doesn't even match the bank's legal definition of “customer.”

A slight improvement is to count distinct primary owners instead:

1
2
3
4
5
6
7

test=# SELECT count(DISTINCT primary_owner) 
FROM t_entity AS e, t_account AS a 
WHERE a.primary_owner = e.entity_id;
 count 
-------
     4
(1 row)

In this dataset a normal and a distinct count happen to match. But building the correct query from day one avoids surprises later.

Shared accounts complicate things further. Consider an account whose primary owner is a company with three authorized signers:

1
2
3
4
5
6
7
8
9
10

test=# SELECT a.*, u.*
FROM t_entity AS e, t_account AS a, t_allowed_use AS u 
WHERE a.primary_owner = e.entity_id 
AND u.account_id = a.account_id;
 account_id | primary_owner | balance | account_id | entity_id 
------------+---------------+---------+------------+-----------
 COMP003    |             5 | 2789.00 | COMP003    |         1
 COMP003    |             5 | 2789.00 | COMP003    |         2
 COMP003    |             5 | 2789.00 | COMP003    |         3
(3 rows)

Is that one customer (the company), or four (the company plus the individuals)? Most would count one. But what if a couple shares a private account jointly, and also owns five companies between them? Now the answer depends entirely on whether you're counting legal entities, households, or revenue-generating relationships.

Knowing the Question Beats Asking the Machine

The pattern repeats in every analytics engagement. The demand is never for cleverer SQL—it's precision about what you want to know. PostgreSQL can answer any well-formed question you can express. The catch is that there is no such thing as SELECT what_I_want_to_know FROM go_and_figure_out.

Large language models don't solve this, either. They try to guess missing context from a vague prompt. Ask a language model a deceptively simple question and the limits show immediately:

>>> how many people live? (data only, short answer, just one number)

I'm unable to verify the global population.

>>> how many people are alive? (data only, short answer, just one number)

7,924,110,000 (approximate as of mid-2023)

>>> how many people are known to be alive? (data only, short answer, just one number)

I'm unable to verify an exact number.

Each rephrasing of the same business question yields a different answer, or none. Precision in language—not in the model—drives the result.

Clarity Is the Optimization

PostgreSQL will answer what you ask it accurately and quickly. The bottleneck is rarely the database; it's the unwritten assumptions in the question itself. Take the time to define your terms before you run the count, and the SQL becomes straightforward. Skip that step, and you'll get a number that looks precise but means something different than what the business thinks it means.