Finding Multi-Accounters in Your Database: SQL Queries to Run First

Before you add any new signup control, find out how much multi-accounting is already in your database. The answer tells you whether this is a big problem or a small one, gives you real examples to tune against, and often finds the clusters that are costing money right now.

The queries below are PostgreSQL and assume a users table with id, email, created_at, signup_ip and optionally payment_fingerprint. Adapt the names to your schema. Each query produces leads, not verdicts. Real families, offices and classrooms will appear in every result set.

1. Normalized email

The cheapest duplicates are emails that differ only in ways that still reach the same inbox. Gmail ignores dots in the local part and everything after a +. Many other providers support plus-addressing too.

WITH normalized AS (
  SELECT
    id, email, created_at,
    CASE
      WHEN split_part(lower(email), '@', 2) IN ('gmail.com', 'googlemail.com')
        THEN replace(split_part(split_part(lower(email), '@', 1), '+', 1), '.', '')
             || '@gmail.com'
      ELSE split_part(split_part(lower(email), '@', 1), '+', 1)
             || '@' || split_part(lower(email), '@', 2)
    END AS email_norm
  FROM users
)
SELECT email_norm, count(*) AS accounts,
       min(created_at) AS first_signup, max(created_at) AS last_signup,
       array_agg(id ORDER BY created_at) AS user_ids
FROM normalized
GROUP BY email_norm
HAVING count(*) > 1
ORDER BY accounts DESC
LIMIT 200;

Persist email_norm as an indexed column going forward and check new signups against it. It’s cheap and closes the laziest version of the attack. (A unique index will fail to build while the duplicates found above still exist, so clean those up first if you want the database to enforce it.)

While you’re here, check the domains. A large share of signups from disposable-inbox domains is its own signal. Group by split_part(lower(email), '@', 2) and review the top of the list by hand.

2. Signup IP clusters with a time window

Grouping by IP alone mostly finds offices and mobile carriers. Adding a time window helps: many accounts from one IP within an hour reads very differently from many accounts over a year.

SELECT signup_ip,
       date_trunc('hour', created_at) AS hour,
       count(*) AS accounts,
       count(DISTINCT split_part(lower(email), '@', 2)) AS distinct_domains,
       array_agg(id ORDER BY created_at) AS user_ids
FROM users
WHERE signup_ip IS NOT NULL
GROUP BY signup_ip, date_trunc('hour', created_at)
HAVING count(*) >= 3
ORDER BY accounts DESC
LIMIT 200;

Interpret it with care. Mobile carriers and some ISPs put many subscribers behind one public address through carrier-grade NAT, as covered in CGNAT and shared IPs. A cluster on a hosting-provider address is more interesting than one on a mobile carrier. If you have ASN data, join it in and weight datacenter ranges higher.

3. Shared payment instruments

If any part of your funnel collects cards, the processor usually gives you a stable identifier for the card number. Stripe, for example, exposes a fingerprint on card payment methods. Several accounts paying with one card, or one card attached to many trials, is a strong link.

SELECT payment_fingerprint,
       count(DISTINCT id) AS accounts,
       array_agg(DISTINCT email) AS emails
FROM users
WHERE payment_fingerprint IS NOT NULL
GROUP BY payment_fingerprint
HAVING count(DISTINCT id) > 1
ORDER BY accounts DESC;

Card links are high precision but low coverage: most free-tier abuse never touches a card. Use them to confirm clusters found by the other queries.

4. Behavioral timing

Scripted signups have a rhythm. Look for bursts of signups seconds apart and for accounts that do the same thing at the same time after signing up.

SELECT id, email, created_at,
       created_at - lag(created_at) OVER (ORDER BY created_at) AS gap
FROM users
WHERE created_at > now() - interval '30 days'
ORDER BY created_at;

Filter for gaps under a few seconds and you get runs of near-simultaneous signups. If you log first actions such as API key creation or first credit spend, a similar query over those events finds accounts that “wake up” together.

5. Combine the clues into clusters

Each query alone is noisy. The useful step is linking accounts that share any strong attribute into connected groups. A simple approach is an edge table plus a recursive walk:

CREATE TEMP TABLE edges AS
  SELECT a.id AS src, b.id AS dst, 'email' AS via
  FROM normalized a JOIN normalized b
    ON a.email_norm = b.email_norm AND a.id < b.id
  UNION ALL
  SELECT a.id, b.id, 'card'
  FROM users a JOIN users b
    ON a.payment_fingerprint = b.payment_fingerprint AND a.id < b.id;

(normalized here is the CTE from query 1, materialized as a table.)

From there, compute connected components in your analytics stack or with a recursive CTE, and rank clusters by size and by how much free-tier usage they consumed. Leave IP edges out of the components, or give them a low weight, because they merge unrelated people too easily. This is the same idea that powers an identity graph, done in batch.

What this audit can’t see

Everything above is indirect. A careful abuser beats each query: a different email provider for every account, a residential proxy for every signup, no card, and a pause between registrations. Your database has no record of the one thing these accounts share, which is the device they were created on.

That’s the gap device identification closes. With Prynt on the signup page, each signup carries a requestId. Your server reads the event’s visitorId and attaches the new user as linkedId:

const event = await prynt.getEvent(requestId);
await db.users.update(user.id, { visitor_id: event.visitorId });
await prynt.updateEvent(requestId, { linkedId: String(user.id) });

From then on, two things change. At signup, event.accountsOnDevice lists every one of your accounts already seen on that device, so you can act before the account exists. In the database, a new query becomes the most reliable one you have:

SELECT visitor_id, count(*) AS accounts, array_agg(id ORDER BY created_at)
FROM users
WHERE visitor_id IS NOT NULL
GROUP BY visitor_id
HAVING count(*) > 1
ORDER BY accounts DESC;

The visitorId survives cleared cookies and incognito windows, so it groups accounts the email and IP queries miss. Multi-accounting detection and detecting fake account farms cover what to do with the clusters.

Make it a habit

Run the five queries once to size the problem and save the top clusters as labeled examples. Then add the visitor_id column so next quarter’s audit is built on device evidence rather than inference. The labeled examples are what you’ll tune against when you start enforcing at signup.