DEV Community: Fixel Smith

Detecting fraud rings: the social-graph problem in disguise

Fixel Smith — Thu, 28 May 2026 13:29:46 +0000

Fraud at scale usually gives itself away through repetition. One phone number behind twelve identities. Same device fingerprint reused across signups. A shipping address that keeps showing up on accounts that supposedly have nothing to do with each other.

At some point the accounts stop looking independent. They're a ring.

Finding rings is a graph problem. Nodes are accounts, edges are shared attributes, and what you want is connected components.

The usual recommendation is to throw the data into Neo4j and solve it there with Cypher queries. That absolutely works. It's also a lot of infrastructure for something your warehouse can often handle directly.

This post is how to do it with regular SQL.

If you work on AML or transaction monitoring, the same shape applies. KYC profile overlaps are graph problems too, just with different attribute labels on the nodes.

What a fraud ring looks like

Simple example:

Account A and account B share a phone number at signup.
Account B and account C share a shipping address.
Account C and account D share a device fingerprint.
Account E and account A share an IP at first transaction.

A, B, C, D, and E are connected. Looked at individually they look like five separate customers. Together they're almost always one operator running cards or laundering accounts.

Single shared attributes catch the obvious cases. The harder problem is following those connections outward and figuring out which accounts belong to the same cluster.

The data

Assume you have an accounts table and a shared_attributes table:

-- accounts
account_id | signup_date | status
-----------|-------------|--------
1          | 2026-01-01  | active
2          | 2026-01-02  | active
...

-- shared_attributes
account_a | account_b | attribute_type | attribute_value
----------|-----------|----------------|----------------
1         | 7         | phone          | +1-555-0100
2         | 14        | shipping_address | 123 Main St
...

The shared_attributes table has one row per pair of accounts that share at least one identifying attribute. The exact build step depends on what identifiers you have available. The recursive part below assumes it exists.

If you don't have it pre-built, you can build it inline:

SELECT
  a1.account_id AS account_a,
  a2.account_id AS account_b,
  'phone' AS attribute_type,
  a1.phone AS attribute_value
FROM accounts a1
JOIN accounts a2
  ON a1.phone = a2.phone
 AND a1.account_id < a2.account_id;

The account_id < account_id predicate dedupes so you don't get both A-B and B-A. Repeat the shape for each attribute and UNION ALL the results. Materialize it as a table if it gets large.

Finding pairs (the easy part)

Pairs are just a SELECT against shared_attributes.

SELECT
  account_a,
  account_b,
  count(*) AS shared_attribute_count
FROM shared_attributes
GROUP BY 1, 2
HAVING count(*) >= 2
ORDER BY shared_attribute_count DESC;

Two shared attributes are usually a much stronger signal than one. Shared phone by itself might just be roommates or family. Shared phone plus device plus address is where things start getting interesting fast.

Finding clusters (the recursive part)

For clusters of arbitrary size you need a recursive CTE. Start from an account, follow every edge to find accounts connected to it, then follow every edge from those, and so on, until no new accounts get added.

WITH RECURSIVE ring AS (
  -- Seed: pick a starting account
  SELECT
    account_a AS member,
    1 AS depth
  FROM shared_attributes
  WHERE account_a = 1

  UNION

  -- Step: add accounts connected to any current ring member
  SELECT
    CASE
      WHEN s.account_a = r.member THEN s.account_b
      ELSE s.account_a
    END AS member,
    r.depth + 1
  FROM ring r
  JOIN shared_attributes s
    ON r.member IN (s.account_a, s.account_b)
  WHERE r.depth < 10
)
SELECT DISTINCT member FROM ring;

One easy mistake here is using UNION ALL instead of UNION.

If A connects to B and B connects back to A, the recursion keeps walking the same loop forever unless duplicates get removed during each pass. Regular UNION handles that automatically.

In practice most of the clusters I've seen are relatively small, so a depth cap like 10 is usually enough to keep runaway recursion under control. The cap is also there for the rare malformed shared_attributes table that would otherwise blow up the query.

A note on platform support

Recursive CTEs work in Postgres, Snowflake, BigQuery, SQL Server, MySQL 8+, and SQLite. The syntax above is close to ANSI standard SQL and runs in all of them with minor adjustments. Postgres tends to be the cleanest. Snowflake and BigQuery both handle it but you'll see different optimizer behavior.

BigQuery's GoogleSQL graph queries went GA in 2025. If you're already on BigQuery and your rings are large enough that the recursive CTE feels slow, the native graph syntax is the cleaner path before you reach for an external graph database.

Finding all rings (not just one seed)

The query above starts from one account. To enumerate every distinct ring across your whole population you need a different approach: assign every account to a connected component.

WITH RECURSIVE component AS (
  SELECT
    account_a AS member,
    least(account_a, account_b) AS component_id
  FROM shared_attributes

  UNION

  SELECT
    s.account_b,
    c.component_id
  FROM component c
  JOIN shared_attributes s
    ON s.account_a = c.member
  WHERE NOT EXISTS (
    SELECT 1
    FROM component c2
    WHERE c2.member = s.account_b
      AND c2.component_id <= c.component_id
  )
)
SELECT component_id, count(DISTINCT member) AS ring_size
FROM component
GROUP BY 1
HAVING count(DISTINCT member) >= 3
ORDER BY ring_size DESC;

least(account_a, account_b) AS component_id picks the lowest account_id in any pair as the canonical name for the component. The NOT EXISTS check stops members from getting re-added under a higher component_id.

This version gets expensive a lot faster than the seeded query, so it's usually something you'd run as a scheduled batch process instead of inline with analyst queries.

Where this hits false positives

Not every shared attribute means fraud. The signals that get noisy in practice:

Apartment complexes and dorms. Lots of unrelated accounts share an address.
Household phone plans. Family members share the primary line.
Employer-provided devices, IPs, and mailing addresses. Corporate accounts cluster.
Mailbox stores and PO box services. Legitimate small businesses use these too.

The fix isn't to drop those signals entirely. It's to weight them. A shared apartment address by itself is weak. Shared apartment address plus shared device plus shared payment method is still strong even if any one signal is noisy on its own. The component rollup naturally surfaces the dense clusters where multiple weak signals coincide.

When SQL stops being enough

The recursive CTE handles components up to a few thousand members fine on a modern warehouse. Past that, performance degrades because every iteration is a full self-join.

You've outgrown the SQL approach when your largest component hits the tens of thousands of accounts, or when the recursion depth caps you've set are getting hit on the regular. Either of those is your cue to move to a purpose-built graph database (Neo4j, Memgraph, TigerGraph) or a graph extension on your warehouse. The SQL pattern still has a job to do as the feature-engineering layer feeding the graph engine.

Worth flagging since the question comes up: ring detection is inherently batch. It's a complement to your real-time transaction checks, not a replacement. The realtime layer catches the immediate suspicious activity. The ring layer flags accounts to watch even when nothing's currently tripping a realtime rule.

Until you hit that scale, the recursive CTE does the job without making you stand up new infrastructure.

What I'd actually do in a new system

In order:

Build the shared_attributes table from the obvious signals: phone, address, device, IP, payment method.
Run the pair-detection query weekly. Triage the strongest pairs (3+ shared attributes).
Once the pair workflow has a confirmed-fraud feedback loop running, build the recursive CTE for ring detection. Run monthly.
Save the rings as features that feed per-transaction scoring. A transaction from an account in a known ring is automatically high-risk.

The mistake I see new teams make is jumping straight to step 3 and skipping step 2. The recursive query is more fun to write but it's useless without the pair workflow underneath it. No feedback loop means no way to tell which detected rings are real and which are coincidence.

Next post: why your fraud alerts are noisy, and what to actually do about it. Most teams reach for more rules. The real fix is in the feedback loop.

A note on process: AI helps with editing, not engineering. The SQL and patterns are from years of program-integrity work; the model just cuts the rambling.

Fixel Smith is an experienced Program Integrity Analyst working in public-sector data.

Eight window-function tricks beyond LAG and ROW_NUMBER

Fixel Smith — Mon, 18 May 2026 17:19:00 +0000

The first post in this series got more reader response than I expected. The comments were full of practical follow-up questions and a few good corrections, and one thing that kept coming up was a request to go deeper on window functions specifically. Pattern 6 in the previous post leaned heavily on them. People wanted to see what else lives in that toolbox.

This is that.

LAG and ROW_NUMBER are usually the first two window functions analysts learn. They are the right starting point. But after a year or two of writing SQL you start running into shapes those two can't quite hit, and you start hitting problems where those two stop being enough.

Eight patterns below. Same disclaimer as the previous post: I work as a data analyst on a program-integrity team. Examples use generic transaction tables and synthetic scenarios. Nothing here reflects actual systems, procedures, or data from any specific employer.

1. QUALIFY

QUALIFY lets you filter directly on window-function output without wrapping the whole query in another CTE or subquery.

SELECT
  cardholder_id,
  timestamp,
  count(*) OVER (
    PARTITION BY cardholder_id ORDER BY timestamp
    RANGE BETWEEN INTERVAL '5 minutes' PRECEDING AND CURRENT ROW
  ) AS tx_5min
FROM transactions
QUALIFY tx_5min >= 5;

Without QUALIFY this usually turns into an extra query layer just to filter on the computed column. Not a big deal in tiny examples, but once fraud rules start stacking together it gets messy fast.

Support is still inconsistent depending on the engine. Snowflake, BigQuery, DuckDB, Databricks, ClickHouse and a few others have it. Postgres, MySQL, SQLite still don't.

If you're on Postgres, the fallback is usually just a CTE and an outer WHERE clause.

2. Frame specs (ROWS vs RANGE)

A lot of people write:

OVER (PARTITION BY x ORDER BY y)

and never think much about the frame underneath it. Usually fine until sliding windows start behaving strangely.

The two big ones:

ROWS BETWEEN N PRECEDING AND CURRENT ROW
RANGE BETWEEN INTERVAL '5 minutes' PRECEDING AND CURRENT ROW

ROWS counts rows. RANGE works off the ORDER BY value itself.

The difference starts mattering once duplicate timestamps show up. ROWS and RANGE stop behaving the same way at that point.

For fraud velocity checks, RANGE on timestamps is usually the right fit because you're actually asking "how many transactions happened within this time window," not "what happened in the previous 5 rows."

For rolling hourly baselines or trailing-week comparisons, ROWS is often cleaner because each row already represents a fixed bucket.

One related thing that came up in the comments on the previous post and is worth surfacing here: the timestamp you key off matters more than people realize. Authorization time and settlement time can be days apart. If you use settlement time on a velocity or impossible-travel check, charges from days ago suddenly look like they happened "now" in the warehouse, and the windows blow up with false positives. Use auth time. Most card systems have both columns; the home-grown ones don't always make the distinction obvious.

3. FILTER for conditional aggregation

Most people start with CASE WHEN inside SUM or COUNT:

sum(CASE WHEN amount > 100 THEN 1 ELSE 0 END)

FILTER does the same thing with less noise:

SELECT
  merchant_id,
  count(*) FILTER (WHERE amount > 100) AS high_value_count,
  count(*) FILTER (WHERE timestamp::time BETWEEN '00:00' AND '06:00') AS off_hours_count
FROM transactions
GROUP BY merchant_id;

Works inside window functions too:

sum(amount) FILTER (WHERE merchant_category = 'gas_station')
  OVER (
    PARTITION BY cardholder_id ORDER BY timestamp
    ROWS BETWEEN 100 PRECEDING AND CURRENT ROW
  ) AS rolling_gas_spend

Mostly just easier to read once queries get large.

Support varies though. Postgres, Snowflake, DuckDB and ClickHouse support it. MySQL still doesn't. BigQuery handles this a little differently with IF inside the aggregate instead.

4. FIRST_VALUE / LAST_VALUE / NTH_VALUE

These are useful anytime you need one row's value carried across the whole partition.

Example: tagging every transaction with the first merchant a cardholder ever used.

SELECT
  cardholder_id,
  timestamp,
  merchant_id,
  first_value(merchant_id) OVER (
    PARTITION BY cardholder_id ORDER BY timestamp
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS first_merchant_ever,
  last_value(merchant_id) OVER (
    PARTITION BY cardholder_id ORDER BY timestamp
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS most_recent_merchant
FROM transactions;

The LAST_VALUE behavior trips people up constantly.

Without the explicit frame, LAST_VALUE usually returns the current row instead of the actual last row in the partition because the default frame stops at the current row.

NTH_VALUE works the same way conceptually. Useful for questions like "what was the third transaction this cardholder ever made?"

5. LEAD chains for sequence detection

LAG gets most of the attention because analysts usually look backwards more than forwards.

LEAD becomes useful once you're looking for sequences instead of isolated events.

SELECT
  cardholder_id,
  timestamp,
  merchant_id,
  lead(merchant_id, 1) OVER w AS next_merchant_1,
  lead(merchant_id, 2) OVER w AS next_merchant_2,
  lead(merchant_id, 3) OVER w AS next_merchant_3,
  lead(timestamp, 3) OVER w - timestamp AS time_to_4th_tx
FROM transactions
WINDOW w AS (PARTITION BY cardholder_id ORDER BY timestamp);

Then you can filter on the sequence itself:

WHERE next_merchant_1 <> merchant_id
  AND next_merchant_2 <> merchant_id
  AND next_merchant_3 <> merchant_id
  AND time_to_4th_tx < INTERVAL '90 seconds'

A lot of card-testing behavior starts showing up in shapes like this.

6. Gap-and-island

Gap-and-island is basically the standard SQL trick for finding streaks or runs of related activity.

One common version looks like this:

SELECT
  cardholder_id,
  timestamp,
  merchant_id,
  amount,
  sum(
    CASE
      WHEN merchant_id <> lag(merchant_id) OVER w
      THEN 1
      ELSE 0
    END
  ) OVER w AS island_id
FROM transactions
WINDOW w AS (PARTITION BY cardholder_id ORDER BY timestamp);

Every time the merchant changes, the island ID increments. Depending on the engine, this is also one of those patterns where query planners can suddenly get a lot more expensive than you expected.

Once you have that grouping value, streak analysis gets much easier:

WITH islands AS (
  -- query above
)
SELECT
  cardholder_id,
  merchant_id,
  island_id,
  count(*) AS streak_length
FROM islands
GROUP BY 1, 2, 3
ORDER BY streak_length DESC;

This kind of thing is useful for spotting behavior shifts. Somebody with a year-long coffee-shop routine suddenly spending every day at gas stations is at least worth a second look.

7. Conditional running totals

CASE WHEN inside SUM OVER ends up doing a huge amount of work in fraud analysis.

Simple example:

SELECT
  cardholder_id,
  timestamp,
  approved,
  sum(CASE WHEN NOT approved THEN 1 ELSE 0 END) OVER (
    PARTITION BY cardholder_id
    ORDER BY timestamp
    RANGE BETWEEN INTERVAL '24 hours' PRECEDING AND CURRENT ROW
  ) AS declines_last_24h
FROM transactions;

Decline clusters are usually worth paying attention to. A surprising amount of card testing shows up as repeated declines followed by a successful approval somewhere later in the sequence.

The reset-on-condition versions get more complicated and usually end up combining multiple windows together. Useful pattern, but also one of the easier places to write something unreadable by accident.

8. PERCENT_RANK and NTILE

These are useful anytime fixed thresholds stop making sense globally.

WITH monthly_spend AS (
  SELECT cardholder_id, sum(amount) AS month_spend
  FROM transactions
  WHERE timestamp >= current_date - INTERVAL '30 days'
  GROUP BY 1
),
buckets AS (
  SELECT *, ntile(10) OVER (ORDER BY month_spend) AS spend_decile
  FROM monthly_spend
)
SELECT * FROM buckets WHERE spend_decile = 10;

A top-spend cardholder usually needs very different fraud thresholds than somebody barely using the card.

NTILE gives you buckets. PERCENT_RANK gives you the actual percentile position instead.

Putting it together

The reason these patterns matter is that they compose well.

Most individual fraud rules are actually pretty simple. The complexity comes from layering multiple weak signals together without turning the query into procedural spaghetti.

Window functions let you keep a surprising amount of this logic in straight SQL before things spill over into procedural code.

Most of the examples here are standard SQL. QUALIFY is still warehouse-specific, and FILTER support depends on the engine. If your warehouse doesn't support QUALIFY, the fallback is usually just another CTE layer.

Next post is probably fraud-ring detection. Same general idea, just applied to graph-shaped problems instead of straight transaction streams.

If you've got a specific window-function shape that gave you trouble or a fraud pattern you'd like to see written up, the comments on the original post are still active. Happy to take requests.

Six SQL patterns I use to catch transaction fraud

Fixel Smith — Thu, 14 May 2026 20:18:26 +0000

Quick disclaimer: I do data work on a program-integrity team. Examples below use generic transaction tables and made-up scenarios. Nothing here comes from anything I've actually worked on or seen. Views are mine, not my employer's.

Fraud detection in transaction data is mostly SQL. Not machine learning, not graph databases, not whatever Gartner is hyping this year. SQL, run against the right tables, with the right joins, looking for the right shapes.

I work mostly with government-funded benefit programs, but the patterns below port over to anything with a transactions table: credit cards, healthcare claims, e-commerce, point-of-sale. If money moves and gets logged, these queries will find weird things in the log.

Six patterns. Roughly in the order I'd build them out on a new dataset.

1. Velocity

The simplest one. Someone with a stolen card wants to drain it before the holder notices. So they hit the card fast.

SELECT
  cardholder_id,
  date_trunc('hour', timestamp) AS hour_bucket,
  count(*) AS tx_count,
  min(timestamp) AS first_tx,
  max(timestamp) AS last_tx
FROM transactions
WHERE timestamp >= current_date - INTERVAL '30 days'
GROUP BY 1, 2
HAVING count(*) > 10;

Tune two knobs: the window size and the count threshold. I usually run a 1-minute, 5-minute, and 1-hour version in parallel and compare. Different fraud shows up at different scales — a card-testing ring hits a server in seconds; a benefits-trafficking ring might take an afternoon.

A few cardholders will legitimately blow past the threshold. Route operators servicing vending machines. People reloading prepaid cards in bulk. Your false positives. Worth keeping a whitelist after the first pass.

For sliding-window velocity, this is the form I use:

SELECT
  cardholder_id,
  timestamp,
  count(*) OVER (
    PARTITION BY cardholder_id
    ORDER BY timestamp
    RANGE BETWEEN INTERVAL '5 minutes' PRECEDING AND CURRENT ROW
  ) AS tx_in_last_5min
FROM transactions
QUALIFY tx_in_last_5min >= 5
ORDER BY cardholder_id, timestamp;

QUALIFY works in Snowflake, BigQuery, Databricks, Teradata. For Postgres you wrap the whole thing in a CTE and filter on the outside. Slight pain, same result.

2. Impossible travel

If a card swipes in Chicago and seven minutes later swipes in Los Angeles, one of those swipes is fake. The card is cloned. This is the most uncontroversial fraud signal you'll find — there's almost no legitimate reason a single card is in two distant places in seven minutes.

WITH ordered_tx AS (
  SELECT
    cardholder_id,
    timestamp,
    location,
    LAG(timestamp) OVER (PARTITION BY cardholder_id ORDER BY timestamp) AS prev_ts,
    LAG(location)  OVER (PARTITION BY cardholder_id ORDER BY timestamp) AS prev_loc
  FROM transactions
)
SELECT
  cardholder_id,
  prev_ts  AS first_tx,
  timestamp AS second_tx,
  prev_loc  AS first_location,
  location  AS second_location,
  EXTRACT(EPOCH FROM (timestamp - prev_ts)) / 60 AS minutes_apart,
  haversine(prev_loc, location)                  AS miles_apart
FROM ordered_tx
WHERE prev_ts IS NOT NULL
  AND prev_loc <> location
  AND haversine(prev_loc, location)
        / nullif(EXTRACT(EPOCH FROM (timestamp - prev_ts)), 0)
        * 3600 > 600;

haversine is the great-circle distance function. Most warehouses ship one. If yours doesn't, it's about ten lines to write your own.

The 600 mph threshold is rough — commercial jet cruise is around 575, so this is "faster than a plane could possibly do it." You can tighten it to 100 mph if you want to catch suspiciously-fast ground travel too, but at that threshold you start picking up real airline travelers, kids with parents driving them home from camp, that kind of thing.

A few other shapes in the same family are worth running:

Two distant cities, same state, inside 5 minutes. Local cloning rings.
Multiple ZIP codes inside an hour. Skimmer rings working a region.
Border crossings inside 10 minutes. International rings.

3. Amount anomalies

There are a couple of amounts that show up disproportionately in fraud and almost never in normal use.

SELECT cardholder_id, timestamp, amount, merchant_id
FROM transactions
WHERE
  (amount >= 99.50  AND amount < 100.00)
  OR (amount >= 499.50 AND amount < 500.00)
  OR amount IN (1.00, 5.00, 10.00)
ORDER BY cardholder_id, timestamp;

What's happening:

Round dollar amounts at small values — $1.00, $5.00, $10.00 — are almost always card tests. Someone got a card number from a dump and they're checking if it works before reselling it. Real cardholders almost never buy something for exactly $1.00. Coffee is $4.73, gas is $52.81. The roundness is the signal.

Amounts just below a threshold are different. $99.99 is interesting because at a lot of places, $100 is the line where the cashier is supposed to check ID. $499.99 is interesting because $500 is often a daily ATM cap. Whoever's doing the transaction knows the rules and is staying under them.

(For benefits transactions specifically, the round-number pattern doesn't help much. Benefits don't get card-tested the same way. There the signal is usually duplicate recipients, which is a different post.)

4. Suspicious merchants

When a skimmer compromises a card reader at, say, a gas pump, you don't get one fraud case. You get dozens. Every card swiped at that pump for the next few weeks is now in someone's database. So the symptom from the merchant side is: an unusual number of unrelated cards spending more than usual, in a short window.

SELECT
  merchant_id,
  date_trunc('hour', timestamp) AS hour_bucket,
  count(DISTINCT cardholder_id) AS unique_cards,
  count(*) AS total_tx,
  sum(amount) AS total_amount
FROM transactions
WHERE timestamp >= current_date - INTERVAL '7 days'
GROUP BY 1, 2
HAVING count(DISTINCT cardholder_id) > 20
  AND sum(amount) > 5000
ORDER BY total_amount DESC;

The problem with static thresholds (20 unique cards, $5000) is they don't account for size. A Costco does that in 90 seconds. A used bookshop, never. So the better version compares each merchant against itself:

WITH merchant_hourly AS (
  SELECT
    merchant_id,
    date_trunc('hour', timestamp) AS hour_bucket,
    count(DISTINCT cardholder_id) AS unique_cards
  FROM transactions
  WHERE timestamp >= current_date - INTERVAL '60 days'
  GROUP BY 1, 2
),
with_baseline AS (
  SELECT
    *,
    avg(unique_cards) OVER (
      PARTITION BY merchant_id
      ORDER BY hour_bucket
      ROWS BETWEEN 168 PRECEDING AND 1 PRECEDING
    ) AS rolling_avg_cards
  FROM merchant_hourly
)
SELECT *,
  unique_cards / nullif(rolling_avg_cards, 0) AS spike_ratio
FROM with_baseline
WHERE unique_cards > rolling_avg_cards * 3
ORDER BY spike_ratio DESC;

The 168 is the trailing seven days of hourly buckets. I use a week because daily and weekly seasonality matters — Tuesday 2pm at a coffee shop is not the same baseline as Saturday 9am at the same shop. A week catches both cycles.

Three times normal is where I start. It's loose enough not to drown you in alerts but tight enough to flag the actually weird hours.

5. Off-hours

Most people are creatures of habit when they spend money. A nine-to-fiver doesn't suddenly start buying gas at 3am. If their card does, it's either being used by someone else or they're traveling — and travel produces other signals you can check.

WITH cardholder_hour_pattern AS (
  SELECT
    cardholder_id,
    EXTRACT(HOUR FROM timestamp) AS hour_of_day,
    count(*) AS tx_count
  FROM transactions
  WHERE timestamp >= current_date - INTERVAL '90 days'
  GROUP BY 1, 2
),
cardholder_normal AS (
  SELECT
    cardholder_id,
    min(hour_of_day) FILTER (WHERE tx_count >= 2) AS earliest_hour,
    max(hour_of_day) FILTER (WHERE tx_count >= 2) AS latest_hour
  FROM cardholder_hour_pattern
  GROUP BY 1
)
SELECT t.cardholder_id, t.timestamp, t.amount, t.merchant_id
FROM transactions t
JOIN cardholder_normal cn USING (cardholder_id)
WHERE EXTRACT(HOUR FROM t.timestamp) NOT BETWEEN cn.earliest_hour AND cn.latest_hour
ORDER BY t.timestamp DESC;

The "two or more in that hour" filter on the inner query is doing important work. Without it, one stray late-night gas station purchase three months ago becomes part of the cardholder's "normal" hours, and you never flag them again. Requiring at least two purchases in a given hour, in 90 days, sets the bar at "actually a habit" instead of "happened once."

Drawback: this doesn't work until you have history. New accounts have no baseline. For those, you fall back to global hour patterns or just skip this pattern entirely until they've been around for a couple months.

6. Window functions for chained signals

This one isn't really a pattern. It's a setup that makes the other five patterns composable.

SELECT
  cardholder_id,
  timestamp,
  amount,
  merchant_id,

  timestamp - LAG(timestamp) OVER w AS time_since_last,

  CASE WHEN merchant_id <> LAG(merchant_id) OVER w
       THEN 'changed' ELSE 'same' END AS merchant_change,

  sum(amount) OVER (
    PARTITION BY cardholder_id
    ORDER BY timestamp
    RANGE BETWEEN INTERVAL '24 hours' PRECEDING AND CURRENT ROW
  ) AS running_24h_total,

  ROW_NUMBER() OVER (
    PARTITION BY cardholder_id, date(timestamp)
    ORDER BY timestamp
  ) AS tx_of_day

FROM transactions
WINDOW w AS (PARTITION BY cardholder_id ORDER BY timestamp)
ORDER BY cardholder_id, timestamp;

Once you've materialized those columns, fraud rules collapse to filter expressions. Say you're hunting card-testing rings, where the tell is "lots of small charges, all at different merchants, within minutes of each other." The rule becomes:

SELECT *
FROM tx_with_windows
WHERE tx_of_day >= 5
  AND time_since_last < INTERVAL '60 seconds'
  AND merchant_change = 'changed';

Three filters. That's it.

The reason this matters is that the moment your analysts can express new fraud hypotheses as SQL filters instead of engineering tickets, your iteration loop drops from weeks to hours. You catch more fraud, faster.

Putting it together

None of these alone is enough. Velocity has false positives (vending operators). Geographic impossibility misses anything inside a single metro. Amount anomalies don't apply outside of card-test contexts. The off-hours rule needs history.

What works is running them all and scoring each transaction across the signals. A transaction that fails on three or four of them is almost always fraud. A transaction that fails on one might be your grandma being weird with her debit card on vacation.

If you're brand new to fraud detection, start with pattern 1. It alone will surface a useful amount of fraud and very little legitimate activity, and it's cheap to run.

If you've already got 1 through 5, the place to invest is pattern 6 — those window-function primitives. Every analyst on your team will use them once they exist, and adding the next fraud pattern stops being a project.

Things I left out

A few things this post doesn't cover that come up constantly:

NULL handling. Real transaction tables don't use NULL the way intro SQL books do. A lot of legacy systems use sentinel values like 9999-12-31 for "no end date" or 0001-01-01 for "no start date." Filtering with IS NULL will silently miss those rows. Always check what the convention is in your specific table before writing WHERE clauses that assume NULL.

False positives. Every rule above will flag real cardholders doing weird-but-legitimate things. Your fraud workflow needs human review of flagged cases, with a feedback loop that lets you tune thresholds based on what's actually fraud and what isn't. Auto-blocking on a single rule is how you lose customers.

Privacy. If the data has PII, your queries need to comply with your applicable data-use policies. De-identified or sampled data first, production data with authorization second.

Cost. Window functions with big partitions are not cheap. Filter your date range first, then apply the window, not the other way around. I've watched a junior analyst burn through a warehouse credit budget by running a LAG() across two years of transactions on the entire dataset before adding the WHERE.

Things I want to write about next, depending on what people ask for:

Eight window-function tricks beyond LAG and ROW_NUMBER
Detecting fraud rings, which is the social-graph problem in disguise
What goes on a fraud team's dashboard, and what doesn't
Why your fraud alerts are noisy, and how to actually fix that instead of just raising thresholds

If there's something specific you want covered, message through fixelsmith.com.

Fixel Smith is an experienced Program Integrity Analyst working in public-sector data.