Investigate Fraud Patterns with SQL

Write fraud investigation SQL queries using window functions in Snowflake.

Introduction

30 Second Summary

Every time your card gets declined at a store for "suspicious activity," a fraud detection system made that call. Behind those systems are analysts writing queries that separate real attacks from normal spending patterns.

In this project, you will build a fraud investigation SQL workbook in Snowflake. You will write queries that detect velocity bursts, flag account takeovers, and validate signals against actual fraud outcomes.

What You'll Build

You run your final composite query against transaction data and get back a ranked list of suspicious accounts with risk scores, precision estimates, and dollar exposure.

By the end of this project, you'll have:

  • A velocity signal query using sliding time windows to surface burst activity that GROUP BY cannot detect.
  • An event-sequencing query linking login events to transactions, detecting account-takeover patterns like new-device logins followed by high-value transfers.
  • A cohort comparison with TP/FP analysis that validates each signal against actual fraud outcomes and sizes risk in dollars.
  • Secret Mission: Build a lift-ratio analysis comparing signal hit rates across fraud vs legitimate populations, then combine signals into a composite risk score with BLOCK/REVIEW/ALLOW recommendations.

Are there any prerequisites?

You should be comfortable with basic SQL (SELECT, WHERE, GROUP BY, JOIN). Window functions and CTEs are taught from scratch in this project.

You will also need a Snowflake account and DataGrip installed. No additional tools or sign-ups are required.

Before We Start

Before we dive in, let's lock in what you're about to build and why the order of your investigation matters.

Set Up Schema and Sample Data

Every fraud investigation starts with the same problem: your queries are tied to one specific database schema. When you move between teams or datasets, you rewrite everything from scratch.

In this step, you will create a portable schema contract with four tables and load sample data containing known fraud patterns. These patterns give you verifiable ground truth so you can confirm your queries return correct results before pointing them at production data.

In this step, get ready to:
  • Create four fraud investigation tables in Snowflake with explicit column definitions.
  • Load sample data covering four distinct fraud patterns for verification.
  • Verify the schema by confirming row counts match expected values.
Create the schema tables

You need four tables to model a fraud investigation: transactions themselves, login events for session context, confirmed fraud outcomes for labelling, and user profiles for account metadata.

  • In DataGrip, create a new SQL file by right-clicking your project in the Files panel and selecting New, then File.
  • Name the file 01_setup.sql.
  • Add the first two table definitions by pasting this code into the file:
-- Schema contract: portable fraud investigation tables
-- Swap these table names for your actual tables when running against production

CREATE OR REPLACE TABLE TRANSACTIONS (
    transaction_id VARCHAR(36),
    account_id VARCHAR(20),
    transaction_ts TIMESTAMP_NTZ,
    amount DECIMAL(12,2),
    transaction_type VARCHAR(20),
    merchant_id VARCHAR(20),
    merchant_category VARCHAR(30),
    recipient_id VARCHAR(20),
    status VARCHAR(15)
);

CREATE OR REPLACE TABLE LOGIN_EVENTS (
    event_id VARCHAR(36),
    user_id VARCHAR(20),
    event_ts TIMESTAMP_NTZ,
    event_type VARCHAR(20),
    device_id VARCHAR(30),
    ip_address VARCHAR(45),
    geo_country VARCHAR(5),
    success BOOLEAN
);

What does this code do?

The comments at the top are your column mapping documentation. When you point these queries at a real dataset, you swap only the table names and column names in these CREATE statements.

  • TRANSACTIONS holds the core event: who transacted, when, how much, and where.
  • LOGIN_EVENTS captures session activity: device, IP, country, and whether the login succeeded.
  • CREATE OR REPLACE means you can re-run this file safely without "table already exists" errors.
  • Select the code you just pasted, then execute it by pressing Cmd+Enter (macOS) or Ctrl+Enter (Windows).
  • You should see two success messages in the output panel confirming each table was created.

Seeing a permission error?

  • Make sure your DataGrip connection is pointed at a database and schema where you have CREATE TABLE privileges.
  • Check the database dropdown at the top of the query console. If it says no database, select a database and schema from the dropdown.

Help me troubleshoot my Snowflake permissions in DataGrip.

  • Add the remaining two table definitions below the code you already have:
CREATE OR REPLACE TABLE FRAUD_OUTCOMES (
    outcome_id VARCHAR(36),
    transaction_id VARCHAR(36),
    account_id VARCHAR(20),
    outcome_ts TIMESTAMP_NTZ,
    outcome_type VARCHAR(20),
    fraud_amount DECIMAL(12,2)
);

CREATE OR REPLACE TABLE USER_PROFILES (
    account_id VARCHAR(20),
    registration_ts TIMESTAMP_NTZ,
    account_type VARCHAR(15),
    kyc_level VARCHAR(10),
    country VARCHAR(5)
);

What do these tables hold?

  • FRAUD_OUTCOMES stores confirmed fraud labels (chargebacks, disputes). This is your ground truth for validating signals later.
  • USER_PROFILES holds account metadata like registration date, account type, and KYC level.
  • Select these two new CREATE statements and execute them.
  • You should see two more success messages. All four tables now exist in your Snowflake schema.
Load sample fraud patterns

Empty tables are useless for testing queries. You need sample data with known fraud patterns so you can verify your signals return the right results before pointing at production.

  • Add the first fraud pattern below your CREATE statements. This is a velocity burst where 6 transactions hit in under 3 minutes:
-- Sample data: known fraud patterns for verification
-- Pattern 1: Velocity burst (card testing - 6 transactions in 3 minutes)
INSERT INTO USER_PROFILES VALUES ('ACCT_001', '2024-01-15 10:00:00', 'personal', 'basic', 'US');
INSERT INTO TRANSACTIONS VALUES
    ('TXN_001', 'ACCT_001', '2025-03-10 14:00:00', 1.00, 'purchase', 'MERCH_A', 'retail', NULL, 'approved'),
    ('TXN_002', 'ACCT_001', '2025-03-10 14:00:30', 1.50, 'purchase', 'MERCH_B', 'grocery', NULL, 'approved'),
    ('TXN_003', 'ACCT_001', '2025-03-10 14:01:00', 2.00, 'purchase', 'MERCH_C', 'electronics', NULL, 'approved'),
    ('TXN_004', 'ACCT_001', '2025-03-10 14:01:30', 1.00, 'purchase', 'MERCH_D', 'retail', NULL, 'approved'),
    ('TXN_005', 'ACCT_001', '2025-03-10 14:02:00', 3.00, 'purchase', 'MERCH_E', 'food', NULL, 'approved'),
    ('TXN_006', 'ACCT_001', '2025-03-10 14:02:30', 500.00, 'purchase', 'MERCH_F', 'electronics', NULL, 'approved');
INSERT INTO FRAUD_OUTCOMES VALUES
    ('OUT_001', 'TXN_006', 'ACCT_001', '2025-04-10 09:00:00', 'chargeback', 500.00);

What is a velocity burst?

Fraudsters test stolen card numbers by firing many small transactions at different merchants in rapid succession. If the small ones go through, they follow up with a large purchase. Notice how TXN_001 through TXN_005 are tiny amounts at different merchants, then TXN_006 is $500.

The FRAUD_OUTCOMES row confirms this was real fraud. The chargeback arrived about a month later, which is typical.

  • Select the Pattern 1 INSERT statements and execute them.
  • You should see success messages confirming rows were inserted into each table.
  • Add Pattern 2 below. This is an account takeover sequence with failed logins from a new device, then a high-value transfer:
-- Pattern 2: Account takeover (new device login then high-value transfer)
INSERT INTO USER_PROFILES VALUES ('ACCT_002', '2023-06-01 08:00:00', 'personal', 'full', 'US');
INSERT INTO LOGIN_EVENTS VALUES
    ('EVT_001', 'ACCT_002', '2025-03-12 09:00:00', 'login_failed', 'DEVICE_NEW', '203.0.113.50', 'RU', FALSE),
    ('EVT_002', 'ACCT_002', '2025-03-12 09:01:00', 'login_failed', 'DEVICE_NEW', '203.0.113.50', 'RU', FALSE),
    ('EVT_003', 'ACCT_002', '2025-03-12 09:02:00', 'login_success', 'DEVICE_NEW', '203.0.113.50', 'RU', TRUE),
    ('EVT_004', 'ACCT_002', '2025-03-12 09:05:00', 'password_change', 'DEVICE_NEW', '203.0.113.50', 'RU', TRUE);
INSERT INTO TRANSACTIONS VALUES
    ('TXN_007', 'ACCT_002', '2025-03-12 09:10:00', 4800.00, 'transfer', NULL, NULL, 'EXTERNAL_99', 'approved');
INSERT INTO FRAUD_OUTCOMES VALUES
    ('OUT_002', 'TXN_007', 'ACCT_002', '2025-04-05 11:00:00', 'chargeback', 4800.00);

What is an account takeover?

The attacker brute-forces the password from a never-seen-before device in a different country. Two failed attempts, then success, then an immediate password change to lock out the real owner. Within 10 minutes, $4,800 goes to an external account.

This pattern spans two tables (LOGIN_EVENTS and TRANSACTIONS), which is why you will need time-bounded JOINs later to connect them.

  • Select and execute the Pattern 2 INSERT statements.
  • Confirm you see success messages for each INSERT.
  • Add Patterns 3 and 4 below. These represent legitimate behavior that your fraud signals must not accidentally flag:
-- Pattern 3: Legitimate high-volume user (power user, no fraud)
INSERT INTO USER_PROFILES VALUES ('ACCT_003', '2022-02-20 12:00:00', 'business', 'full', 'US');
INSERT INTO LOGIN_EVENTS VALUES
    ('EVT_005', 'ACCT_003', '2025-03-10 08:00:00', 'login_success', 'DEVICE_BIZ', '198.51.100.10', 'US', TRUE);
INSERT INTO TRANSACTIONS VALUES
    ('TXN_008', 'ACCT_003', '2025-03-10 09:00:00', 150.00, 'purchase', 'MERCH_G', 'office', NULL, 'approved'),
    ('TXN_009', 'ACCT_003', '2025-03-10 10:30:00', 200.00, 'purchase', 'MERCH_H', 'supplies', NULL, 'approved'),
    ('TXN_010', 'ACCT_003', '2025-03-10 12:00:00', 75.00, 'purchase', 'MERCH_I', 'food', NULL, 'approved'),
    ('TXN_011', 'ACCT_003', '2025-03-10 14:00:00', 300.00, 'purchase', 'MERCH_J', 'office', NULL, 'approved'),
    ('TXN_012', 'ACCT_003', '2025-03-10 15:30:00', 180.00, 'purchase', 'MERCH_K', 'supplies', NULL, 'approved'),
    ('TXN_013', 'ACCT_003', '2025-03-10 17:00:00', 90.00, 'purchase', 'MERCH_L', 'food', NULL, 'approved');

-- Pattern 4: Normal single-transaction user
INSERT INTO USER_PROFILES VALUES ('ACCT_004', '2024-08-01 15:00:00', 'personal', 'basic', 'GB');
INSERT INTO LOGIN_EVENTS VALUES
    ('EVT_006', 'ACCT_004', '2025-03-11 19:00:00', 'login_success', 'DEVICE_PHONE', '192.0.2.100', 'GB', TRUE);
INSERT INTO TRANSACTIONS VALUES
    ('TXN_014', 'ACCT_004', '2025-03-11 19:05:00', 45.00, 'purchase', 'MERCH_M', 'retail', NULL, 'approved');

Why include legitimate accounts?

ACCT_003 is a business account with 6 transactions in one day spread across 8 hours. ACCT_004 is a normal user with a single purchase. These accounts have no entry in FRAUD_OUTCOMES.

If a signal flags ACCT_003 as fraud, that is a false positive. Your analysis later will measure how often each signal makes this mistake.

  • Select and execute the Pattern 3 and 4 INSERT statements.
  • Save 01_setup.sql by pressing Cmd+S (macOS) or Ctrl+S (Windows).

Seeing a duplicate row error?

If you accidentally ran the INSERT statements twice, the tables now have duplicate rows. Re-run the full file from the top. The CREATE OR REPLACE statements will drop and recreate each table, clearing out duplicates before the INSERTs run again.

Help me fix duplicate rows in my Snowflake sample data.

Verify the dataset

Before you build any investigation queries, you need to confirm the data actually loaded. A quick row count tells you whether everything landed.

Before you run the next query, predict: how many rows should the TRANSACTIONS table contain based on the four patterns you just inserted?

  • Run this query in a new console tab or at the bottom of your file:
SELECT COUNT(*) FROM TRANSACTIONS;

You should see 14. That is 6 from ACCT_001, 1 from ACCT_002, 6 from ACCT_003, and 1 from ACCT_004.

  • Verify the other tables by running:
SELECT
    'TRANSACTIONS' AS table_name, COUNT(*) AS row_count FROM TRANSACTIONS
UNION ALL SELECT
    'LOGIN_EVENTS', COUNT(*) FROM LOGIN_EVENTS
UNION ALL SELECT
    'FRAUD_OUTCOMES', COUNT(*) FROM FRAUD_OUTCOMES
UNION ALL SELECT
    'USER_PROFILES', COUNT(*) FROM USER_PROFILES;

You should see: TRANSACTIONS = 14, LOGIN_EVENTS = 6, FRAUD_OUTCOMES = 2, USER_PROFILES = 4.

What do these numbers tell you?

Only 2 out of 14 transactions have a confirmed fraud outcome. That is a 14% fraud rate in this sample. In production, fraud rates are typically under 1%. The sample is deliberately concentrated so you can see patterns clearly in small result sets.

Row counts don't match?

  • If any count is 0, you may have executed the CREATE statements without the INSERT statements. Select and run just the INSERT blocks.
  • If counts are doubled (28, 12, 4, 8), you ran the INSERTs twice. Re-run the entire 01_setup.sql file from top to bottom to reset.

Help me debug my row counts in the fraud sample data.

✔️ Awesome, I've got everything!

Great. Double check you have saved 01_setup.sql and that your row counts match (14, 6, 2, 4).

ⓧ I'd like to double check the full code

Your 01_setup.sql file should contain these sections in order. Scroll up and confirm each one is present:

  • The schema contract comment header (2 lines).
  • CREATE OR REPLACE TABLE TRANSACTIONS (9 columns).
  • CREATE OR REPLACE TABLE LOGIN_EVENTS (8 columns).
  • CREATE OR REPLACE TABLE FRAUD_OUTCOMES (6 columns).
  • CREATE OR REPLACE TABLE USER_PROFILES (5 columns).
  • Pattern 1 INSERTs: ACCT_001, TXN_001 through TXN_006, OUT_001.
  • Pattern 2 INSERTs: ACCT_002, EVT_001 through EVT_004, TXN_007, OUT_002.
  • Pattern 3 INSERTs: ACCT_003, EVT_005, TXN_008 through TXN_013.
  • Pattern 4 INSERTs: ACCT_004, EVT_006, TXN_014.

Your schema contract is in place and verified. Next up, you will explore this data with GROUP BY queries to orient yourself, and hit the exact limitation that makes window functions necessary.

Explore Data and Hit the Aggregation Wall

Your tables are loaded with sample data representing four distinct patterns. The natural first move in any fraud investigation is to reach for GROUP BY and start counting. Which transaction types carry the most fraud? Which accounts have suspicious volume?

In this step, you will write those aggregation queries and watch them work well for big-picture orientation. Then you will push them to their limit and discover a concrete gap: aggregates collapse individual rows into summaries, hiding the timing information that distinguishes a 2-minute card-testing burst from a legitimate power user who transacts all day.

In this step, get ready to:
  • Calculate fraud rates by transaction type and amount band to find elevated segments.
  • Identify top accounts by volume and compare them against confirmed fraud outcomes.
  • Hit the aggregation wall: observe that GROUP BY cannot distinguish burst timing from spread timing.
Write basic fraud rate queries

Before you can hunt for specific patterns, you need to orient yourself. What is the overall fraud rate? Which segments carry elevated risk?

  • In DataGrip, create a new SQL file called 02_explore.sql in the same location as your setup file.
  • Write the overall fraud rate query by pasting this into your new file:
-- Step 2: Exploration queries - orient yourself in the data

-- 2.1 Basic fraud rates
SELECT
    COUNT(*) AS total_transactions,
    COUNT(fo.outcome_id) AS fraud_count,
    ROUND(COUNT(fo.outcome_id) / COUNT(*) * 100, 2) AS fraud_rate_pct,
    SUM(t.amount) AS total_volume,
    SUM(COALESCE(fo.fraud_amount, 0)) AS total_fraud_dollars
FROM TRANSACTIONS t
LEFT JOIN FRAUD_OUTCOMES fo ON t.transaction_id = fo.transaction_id;

What does this query do?

This LEFT JOIN connects every transaction to its fraud outcome (if one exists). COUNT(fo.outcome_id) only counts non-NULL values, so it gives you the number of transactions that resulted in a confirmed fraud outcome.

COALESCE(fo.fraud_amount, 0) treats missing fraud amounts as zero, so the SUM gives total fraud dollars without NULL errors.

  • Highlight the SELECT statement and execute it in DataGrip (select the query text, then press the green play button or use your keyboard shortcut to run the selection).
  • You should see a single row showing 14 total transactions, 2 fraud cases, and fraud dollars totalling $5,300.

Now break that rate down by transaction type. Add this query below the first one:

-- Fraud rate by transaction type
SELECT
    t.transaction_type,
    COUNT(*) AS txn_count,
    COUNT(fo.outcome_id) AS fraud_count,
    ROUND(COUNT(fo.outcome_id) / COUNT(*) * 100, 2) AS fraud_rate_pct,
    SUM(COALESCE(fo.fraud_amount, 0)) AS fraud_dollars
FROM TRANSACTIONS t
LEFT JOIN FRAUD_OUTCOMES fo ON t.transaction_id = fo.transaction_id
GROUP BY t.transaction_type
ORDER BY fraud_rate_pct DESC;
  • Highlight and run this query.
  • You should see that transfer has a 100% fraud rate (1 out of 1), while purchase shows a lower rate across more transactions.

Next, add a query that groups by amount band using CASE WHEN bucketing:

-- Fraud rate by amount band
SELECT
    CASE
        WHEN amount < 10 THEN 'micro (<$10)'
        WHEN amount < 100 THEN 'small ($10-$100)'
        WHEN amount < 1000 THEN 'medium ($100-$1K)'
        ELSE 'large ($1K+)'
    END AS amount_band,
    COUNT(*) AS txn_count,
    COUNT(fo.outcome_id) AS fraud_count,
    ROUND(COUNT(fo.outcome_id) / COUNT(*) * 100, 2) AS fraud_rate_pct
FROM TRANSACTIONS t
LEFT JOIN FRAUD_OUTCOMES fo ON t.transaction_id = fo.transaction_id
GROUP BY amount_band
ORDER BY fraud_rate_pct DESC;

What does CASE WHEN bucketing do?

CASE WHEN creates derived categories that do not exist as columns in the raw data. Each transaction gets assigned to a bucket (micro, small, medium, large) based on its amount. You can then GROUP BY the bucket to see fraud concentration by dollar size.

  • Highlight and run the amount band query.
  • You should see that the large ($1K+) band has the highest fraud rate. Both confirmed fraud transactions ($500 and $4,800) fall into the medium and large bands.

Query returning zero rows or an error?

  • Make sure you ran all the INSERT statements from 01_setup.sql first. Without sample data, these queries return empty results.
  • Check that your DataGrip session is connected to the same Snowflake database and schema where you created the tables.
  • If you see a syntax error, confirm you highlighted only one complete SELECT statement before running.

Why are my exploration queries returning empty or erroring?

Aggregate by account

You know which segments carry elevated fraud. Now find which individual accounts have the highest transaction volume and check whether they overlap with confirmed fraud.

  • Add this query to 02_explore.sql below your amount band query:
-- 2.2 Top accounts by volume
SELECT
    t.account_id,
    COUNT(*) AS txn_count,
    SUM(t.amount) AS total_amount,
    MAX(CASE WHEN fo.outcome_id IS NOT NULL THEN 1 ELSE 0 END) AS has_fraud
FROM TRANSACTIONS t
LEFT JOIN FRAUD_OUTCOMES fo ON t.account_id = fo.account_id
GROUP BY t.account_id
ORDER BY txn_count DESC;

What does has_fraud tell you?

MAX(CASE WHEN fo.outcome_id IS NOT NULL THEN 1 ELSE 0 END) produces a 1 if the account has ANY fraud outcome, and 0 otherwise. This is a quick way to flag fraud-linked accounts without counting individual chargebacks.

  • Highlight and run the query.
  • You should see ACCT_001 and ACCT_003 tied at 6 transactions each at the top. Both show high volume, but only ACCT_001 has has_fraud = 1.

This is useful. You can see overlap between high volume and fraud. But can GROUP BY tell you WHY one high-volume account is fraudulent and the other is not?

Attempt to identify burst timing with GROUP BY

You know ACCT_001 is fraudulent and ACCT_003 is legitimate. Both have 6 transactions on the same day. The fraud pattern here is a velocity burst: 6 transactions crammed into 2.5 minutes (card testing). Can GROUP BY reveal that?

  • Add this daily aggregation query to your file:
-- 2.3 The aggregation wall: daily counts cannot distinguish burst from spread
SELECT
    account_id,
    DATE(transaction_ts) AS txn_date,
    COUNT(*) AS daily_count,
    SUM(amount) AS daily_total,
    MIN(transaction_ts) AS first_txn,
    MAX(transaction_ts) AS last_txn
FROM TRANSACTIONS
GROUP BY account_id, DATE(transaction_ts)
HAVING COUNT(*) >= 5
ORDER BY daily_count DESC;

Before you run this, think: will this query be able to tell you whether those 6 transactions happened in a 2-minute burst or spread across 8 hours?

  • Highlight and run the query.

You should see two rows, both showing a daily count of 6. Both ACCT_001 and ACCT_003 look identical in count.

Look at the first_txn and last_txn columns. You can see that ACCT_001's range is about 2.5 minutes while ACCT_003's range spans 8 hours. But that is all you get: two boundary timestamps.

What is the aggregation wall?

GROUP BY collapses individual rows into a single summary row per group. Once collapsed, you lose the per-transaction timing. You can see that 6 transactions happened, but you cannot identify WHICH transaction was the 5th in a rapid burst, or calculate the gap between consecutive transactions.

This is the limitation you will solve in the next step with window functions. Window functions attach per-row context (rolling counts, time gaps) to each individual transaction without collapsing the rows.

  • Add these comments at the bottom of your file to document the limitation:
-- Problem: ACCT_001 and ACCT_003 both show 6 transactions on the same day
-- but ACCT_001 did them in 2.5 minutes (card testing) while ACCT_003 spread them over 8 hours (legitimate)
-- GROUP BY cannot distinguish these patterns
  • Save 02_explore.sql.

✔️ Awesome, I've got everything!

Great. Confirm you have saved 02_explore.sql with all five queries (overall rate, by type, by band, top accounts, daily aggregation).

ⓧ I'd like to double check the full code

Scroll up through your step and confirm each of these query sections is present in 02_explore.sql, in order:

  • Section 2.1: three SELECT queries (overall rate, by transaction type, by amount band).
  • Section 2.2: one SELECT query (top accounts by volume with has_fraud flag).
  • Section 2.3: one SELECT query (daily aggregation with HAVING) plus the three comment lines documenting the limitation.

You have mapped the terrain: transfers and large amounts carry elevated fraud, and two accounts have identical daily counts. But GROUP BY hides the smoking gun. Next up, you will use window functions to attach per-row velocity context to each transaction and surface the burst pattern directly.

Build Velocity Signals with Window Functions

In the previous step, you hit the aggregation wall. GROUP BY showed you that ACCT_001 and ACCT_003 both had 6 transactions on the same day, but it could not tell you that ACCT_001's burst happened in 2.5 minutes while ACCT_003's were spread across 8 hours.

Window functions solve this. Instead of collapsing rows into groups, they attach per-row context (rolling counts, running sums, time gaps) to each individual transaction. You can see exactly which transaction was the 5th in a 5-minute burst.

In this step, get ready to:
  • Write a sliding-window COUNT using RANGE BETWEEN INTERVAL to detect velocity bursts.
  • Apply QUALIFY to filter results down to only high-velocity transactions.
  • Build a chained-signals CTE that computes multiple fraud metrics in a single pass.
Write a sliding-window COUNT with RANGE BETWEEN INTERVAL

A regular COUNT(*) ... GROUP BY account_id collapses all of an account's transactions into one row. A window function keeps every row intact and attaches the count as an extra column.

The key is the frame clause: RANGE BETWEEN INTERVAL '5 minutes' PRECEDING AND CURRENT ROW. This tells Snowflake to look backward in time from each row and count only transactions within 5 minutes of it.

  • Create a new file called 03_velocity_signals.sql in DataGrip.
  • Add your first velocity signal query by pasting this code:
-- Step 3: Window functions for velocity signals

-- 3.1 Sliding-window COUNT with RANGE BETWEEN INTERVAL
-- Count transactions per account within a 5-minute rolling window
SELECT
    transaction_id,
    account_id,
    transaction_ts,
    amount,
    COUNT(*) OVER (
        PARTITION BY account_id
        ORDER BY transaction_ts
        RANGE BETWEEN INTERVAL '5 minutes' PRECEDING AND CURRENT ROW
    ) AS txn_count_5min,
    SUM(amount) OVER (
        PARTITION BY account_id
        ORDER BY transaction_ts
        RANGE BETWEEN INTERVAL '5 minutes' PRECEDING AND CURRENT ROW
    ) AS spend_5min
FROM TRANSACTIONS
ORDER BY account_id, transaction_ts;

What does this query do?

PARTITION BY account_id groups the window per account, so each account's count is independent.

ORDER BY transaction_ts sorts rows chronologically within each partition. The RANGE BETWEEN frame then looks backward from each row's timestamp.

INTERVAL '5 minutes' PRECEDING means "include all rows whose timestamp falls within 5 minutes before the current row." Each row gets its own sliding window.

  • Select the query and run it (click the green play button or press Cmd+Enter on macOS, Ctrl+Enter on Windows).
  • Look at the txn_count_5min column. For ACCT_001, the count climbs from 1 to 6 as each transaction falls within 5 minutes of the ones before it. For ACCT_003, it stays at 1 because transactions are spaced hours apart.

Seeing an error about RANGE BETWEEN?

Make sure your ORDER BY column is a TIMESTAMP type. RANGE BETWEEN INTERVAL requires the ordering expression to be a timestamp, not a string or date.

Also confirm you have exactly one ORDER BY expression in the window. Multiple ORDER BY columns are not supported with RANGE frames.

Help me debug my RANGE BETWEEN INTERVAL query in Snowflake.

Add QUALIFY to filter high-velocity rows

QUALIFY is a Snowflake-specific clause that filters rows after window functions are computed. It works like HAVING filters after GROUP BY, but for window results.

  • Add the following query below your first one in 03_velocity_signals.sql:
-- 3.2 QUALIFY: filter to only transactions where velocity exceeds threshold
SELECT
    transaction_id,
    account_id,
    transaction_ts,
    amount,
    COUNT(*) OVER (
        PARTITION BY account_id
        ORDER BY transaction_ts
        RANGE BETWEEN INTERVAL '5 minutes' PRECEDING AND CURRENT ROW
    ) AS txn_count_5min
FROM TRANSACTIONS
QUALIFY txn_count_5min >= 5
ORDER BY account_id, transaction_ts;

What does QUALIFY do?

In Snowflake's query execution order, QUALIFY runs at step 6: after FROM, WHERE, GROUP BY, HAVING, and WINDOW. This means you can reference window function aliases directly in the filter.

QUALIFY txn_count_5min >= 5 keeps only rows where the rolling 5-minute count reached 5 or more. Every other row is discarded from the results.

Before you run this query, think about what you expect to see. Which account's transactions should survive the filter, and which should be excluded?

  • Select query 3.2 and run it.

You should see only ACCT_001's transactions. These are the ones where the 5-minute rolling count hit 5 or higher. ACCT_003 (the legitimate power user) is gone because their transactions were never close enough together to exceed the threshold.

Why does this matter?

This is the aggregation wall solved. You can now pinpoint exactly which transactions belong to a velocity burst, per row, without collapsing anything. Each row still shows its transaction details AND its velocity context.

Seeing zero results?

Check that your sample data from Step 1 is still loaded. Run SELECT COUNT(*) FROM TRANSACTIONS and confirm you have 14 rows.

If you changed the threshold, make sure it's >= 5. ACCT_001 has 6 transactions within 2.5 minutes, so the count reaches 5 on the 5th transaction and 6 on the 6th.

My QUALIFY filter returns no rows. Help me debug.

Build a chained-signals CTE with multiple metrics

Now that you know the window function pattern, you can compute multiple fraud metrics in a single pass over the data. A CTE (Common Table Expression) lets you materialize these computed columns as a reusable intermediate result.

  • Add the first part of the chained-signals CTE below your existing queries in 03_velocity_signals.sql:
-- 3.3 Chained signals CTE: multiple metrics in one pass
WITH enriched_transactions AS (
    SELECT
        transaction_id,
        account_id,
        transaction_ts,
        amount,
        merchant_id,
        merchant_category,
        transaction_type,

        -- Velocity: rolling 5-minute count
        COUNT(*) OVER (
            PARTITION BY account_id
            ORDER BY transaction_ts
            RANGE BETWEEN INTERVAL '5 minutes' PRECEDING AND CURRENT ROW
        ) AS txn_count_5min,

        -- Velocity: rolling 5-minute spend
        SUM(amount) OVER (
            PARTITION BY account_id
            ORDER BY transaction_ts
            RANGE BETWEEN INTERVAL '5 minutes' PRECEDING AND CURRENT ROW
        ) AS spend_5min,

        -- Sequencing: time since last transaction (seconds)
        DATEDIFF('second',
            LAG(transaction_ts) OVER (PARTITION BY account_id ORDER BY transaction_ts),
            transaction_ts
        ) AS seconds_since_last,
  • Directly below that code (no blank line between), add the remaining metrics and the closing query:

        -- Sequencing: merchant change detection
        CASE
            WHEN merchant_id <> LAG(merchant_id) OVER (PARTITION BY account_id ORDER BY transaction_ts)
            THEN 1 ELSE 0
        END AS merchant_changed,

        -- Sequencing: transaction number of the day
        ROW_NUMBER() OVER (
            PARTITION BY account_id, DATE(transaction_ts)
            ORDER BY transaction_ts
        ) AS txn_of_day

    FROM TRANSACTIONS
)
SELECT *
FROM enriched_transactions
ORDER BY account_id, transaction_ts;

What does each metric detect?

  • txn_count_5min and spend_5min detect velocity bursts. You already built these in query 3.1.
  • seconds_since_last uses LAG to find the previous transaction's timestamp and compute the gap. Card testers show gaps of 30 seconds or less.
  • merchant_changed compares each row's merchant to the previous row's merchant using LAG. A burst hitting different merchants in rapid succession is a strong card-testing signal.
  • txn_of_day uses ROW_NUMBER() to number each transaction within a day per account. Useful for identifying "transaction #6 within a 3-minute window."
  • Save 03_velocity_signals.sql.
  • Select the full CTE query (from WITH enriched_transactions through the final semicolon) and run it.
  • Look at the ACCT_001 rows. You should see seconds_since_last values of 30 (seconds between each transaction), merchant_changed showing 1 for each row (every transaction hit a different merchant), and txn_count_5min climbing from 1 to 6.

Compare that to ACCT_003. Their seconds_since_last values are in the thousands (hours apart), velocity stays at 1, and the pattern looks completely different from a fraud burst.

Getting a syntax error on the CTE?

Check that there is no trailing comma after txn_of_day. The last column before FROM TRANSACTIONS must not have a comma.

Also confirm the closing parenthesis for WITH enriched_transactions AS ( matches the ) on the line after FROM TRANSACTIONS.

Help me fix a syntax error in my Snowflake CTE with window functions.

✔️ Awesome, I've got everything!

Great. Double check you've saved 03_velocity_signals.sql with all three queries (3.1, 3.2, and 3.3) before moving on.

ⓧ I'd like to double check the full code

Your 03_velocity_signals.sql should contain three queries in order. Scroll up and confirm each is present:

  • Query 3.1: the sliding-window SELECT with txn_count_5min and spend_5min.
  • Query 3.2: the QUALIFY query filtering to txn_count_5min >= 5.
  • Query 3.3: the WITH enriched_transactions AS CTE computing five metrics (txn_count_5min, spend_5min, seconds_since_last, merchant_changed, txn_of_day).

You now have per-row fraud signals that the aggregation wall made impossible. Each transaction carries its own velocity context, timing gaps, and merchant-switching behavior. Next up, you'll link these signals to login events to detect account-takeover patterns across tables.

Detect Account Takeover with Event Sequencing

Your velocity signals from the previous step catch rapid-fire card-testing attacks. Account takeover works differently. It is a sequence that spans multiple tables: a login from a new device, a password change, then a high-value transfer within minutes.

To detect this pattern you need to JOIN events to transactions within a time window. In this step, you will use LAG to detect device changes in login events, then build a time-bounded JOIN that links suspicious logins to the transactions that follow them.

In this step, get ready to:
  • Detect login anomalies using LAG and rolling window counts on LOGIN_EVENTS.
  • Build a time-bounded JOIN that links suspicious logins to transactions within a 30-minute window.
  • Combine velocity signals with login-context signals into one enriched CTE.
Detect login anomalies with LAG

Account takeover leaves a trail in the LOGIN_EVENTS table. The attacker tries multiple passwords (failed logins), succeeds from a new device, then immediately moves money. You need per-row context on each login event to see this sequence.

  • Create a new file called 04_event_sequencing.sql in DataGrip.
  • Add the login anomaly detection query by pasting this code:
-- 4.1 Login anomaly detection with LAG
WITH login_sequence AS (
    SELECT
        event_id,
        user_id,
        event_ts,
        event_type,
        device_id,
        geo_country,
        success,
        -- Previous device for this user
        LAG(device_id) OVER (PARTITION BY user_id ORDER BY event_ts) AS prev_device,
        -- Is this a new device?
        CASE
            WHEN device_id <> LAG(device_id) OVER (PARTITION BY user_id ORDER BY event_ts)
            THEN 1 ELSE 0
        END AS is_new_device,
        -- Count failed logins in last 10 minutes
        SUM(CASE WHEN success = FALSE THEN 1 ELSE 0 END) OVER (
            PARTITION BY user_id
            ORDER BY event_ts
            RANGE BETWEEN INTERVAL '10 minutes' PRECEDING AND CURRENT ROW
        ) AS failed_logins_10min
    FROM LOGIN_EVENTS
)
SELECT *
FROM login_sequence
ORDER BY user_id, event_ts;

What does this query do?

This CTE attaches three pieces of context to every login event without collapsing rows:

  • prev_device uses LAG to look at the device from the previous login for the same user.
  • is_new_device compares the current device to the previous one. If they differ, the flag is 1.
  • failed_logins_10min uses the same RANGE BETWEEN INTERVAL pattern from Step 3, but counts failed login attempts in a 10-minute sliding window.
  • Select the query and run it in DataGrip.

You should see a row for each login event with the enrichment columns populated. For ACCT_002, the successful login (EVT_003) shows is_new_device = 1 and failed_logins_10min = 2. That is your account-takeover signal.

Seeing NULL for prev_device?

The first login event for each user has no previous row, so LAG returns NULL. This is expected. The is_new_device flag will be 0 for the first row because NULL comparisons return FALSE in SQL.

If you see NULL for ALL rows (including non-first events), check that your PARTITION BY user_id clause matches the column name in your LOGIN_EVENTS table.

Help me debug my LAG query on LOGIN_EVENTS

Build a time-bounded JOIN

Knowing which logins are suspicious is only half the puzzle. You need to connect those logins to the transactions that follow them. A regular JOIN would match any login to any transaction for the same user, regardless of timing. A time-bounded JOIN restricts the match to transactions that happened within a specific window after the login.

  • Add the suspicious_logins CTE below your previous query in 04_event_sequencing.sql:
-- 4.2 Time-bounded JOIN: link login events to transactions
-- Find transactions preceded by a suspicious login within 30 minutes
WITH suspicious_logins AS (
    SELECT
        user_id,
        event_ts AS login_ts,
        device_id,
        geo_country,
        SUM(CASE WHEN success = FALSE THEN 1 ELSE 0 END) OVER (
            PARTITION BY user_id
            ORDER BY event_ts
            RANGE BETWEEN INTERVAL '10 minutes' PRECEDING AND CURRENT ROW
        ) AS failed_logins_10min
    FROM LOGIN_EVENTS
    WHERE success = TRUE
    QUALIFY failed_logins_10min >= 2
)

What does this CTE do?

This CTE filters to only successful logins that were preceded by 2 or more failed attempts within 10 minutes. The WHERE success = TRUE keeps only the successful login event. The QUALIFY filter then checks the rolling count of failures in the preceding 10 minutes.

This pattern (brute-force attempts followed by a successful login) is the classic account-takeover entry point.

  • Add the SELECT that joins suspicious logins to transactions directly below the CTE:
SELECT
    t.transaction_id,
    t.account_id,
    t.transaction_ts,
    t.amount,
    t.transaction_type,
    sl.login_ts,
    sl.device_id AS login_device,
    sl.geo_country AS login_country,
    sl.failed_logins_10min,
    DATEDIFF('minute', sl.login_ts, t.transaction_ts) AS minutes_after_login
FROM TRANSACTIONS t
INNER JOIN suspicious_logins sl
    ON t.account_id = sl.user_id
    AND t.transaction_ts BETWEEN sl.login_ts AND DATEADD('minute', 30, sl.login_ts)
ORDER BY t.transaction_ts;

How does the time-bounded JOIN work?

The JOIN condition has two parts. The first, t.account_id = sl.user_id, links the same person across tables. The second, t.transaction_ts BETWEEN sl.login_ts AND DATEADD('minute', 30, sl.login_ts), restricts matches to transactions that occurred within 30 minutes after the suspicious login.

This means a legitimate login that happened 6 hours before a transaction will not match. Only logins immediately preceding a transaction get joined.

Before you run this, what do you expect to see? Will the legitimate high-volume user (ACCT_003) appear in the results?

  • Select the entire section 4.2 query (both the CTE and the SELECT) and run it.

You should see exactly one row: TXN_007 for ACCT_002, amount $4800, with minutes_after_login showing 8. The legitimate user (ACCT_003) does not appear because their login had zero failed attempts preceding it.

Getting zero rows?

Check that you are running the full query including the WITH clause and the SELECT together. Running only the SELECT without the CTE above it will produce an error about suspicious_logins not being defined.

If you see the query run successfully but return zero rows, verify your sample data loaded correctly by running SELECT COUNT(*) FROM LOGIN_EVENTS WHERE user_id = 'ACCT_002'. You should see 4.

Help me debug my time-bounded JOIN query

Combine velocity and login signals into one CTE

You now have velocity signals from Step 3 and login-context signals from this step. The final piece is merging them so every transaction row carries both sets of metrics. This gives you a single enriched view you can filter on any combination of signals.

  • Add the combined enrichment query to 04_event_sequencing.sql. Start with the enriched_transactions CTE:
-- 4.3 Combined enrichment: velocity + login context
WITH enriched_transactions AS (
    SELECT
        transaction_id,
        account_id,
        transaction_ts,
        amount,
        transaction_type,
        merchant_category,
        COUNT(*) OVER (
            PARTITION BY account_id
            ORDER BY transaction_ts
            RANGE BETWEEN INTERVAL '5 minutes' PRECEDING AND CURRENT ROW
        ) AS txn_count_5min,
        SUM(amount) OVER (
            PARTITION BY account_id
            ORDER BY transaction_ts
            RANGE BETWEEN INTERVAL '5 minutes' PRECEDING AND CURRENT ROW
        ) AS spend_5min,
        DATEDIFF('second',
            LAG(transaction_ts) OVER (PARTITION BY account_id ORDER BY transaction_ts),
            transaction_ts
        ) AS seconds_since_last
    FROM TRANSACTIONS
),

Why a slimmer CTE than Step 3?

This enriched_transactions CTE keeps the three most useful velocity metrics (txn_count_5min, spend_5min, seconds_since_last) but drops merchant_changed and txn_of_day to keep the combined query focused. In production, you would include whichever columns matter for your signal catalog.

  • Add the suspicious_logins CTE and the final SELECT directly below (continuing the same query):
suspicious_logins AS (
    SELECT
        user_id,
        event_ts AS login_ts,
        device_id,
        geo_country
    FROM LOGIN_EVENTS
    WHERE success = TRUE
      AND device_id NOT IN (
          SELECT DISTINCT device_id
          FROM LOGIN_EVENTS
          WHERE event_ts < DATEADD('day', -7, CURRENT_TIMESTAMP())
            AND success = TRUE
      )
)
SELECT
    et.*,
    CASE WHEN sl.user_id IS NOT NULL THEN 1 ELSE 0 END AS new_device_flag,
    sl.geo_country AS login_country
FROM enriched_transactions et
LEFT JOIN suspicious_logins sl
    ON et.account_id = sl.user_id
    AND et.transaction_ts BETWEEN sl.login_ts AND DATEADD('minute', 30, sl.login_ts)
ORDER BY et.account_id, et.transaction_ts;

What changed in this suspicious_logins CTE?

This version uses a different approach to flag new devices. Instead of counting failed logins, it checks whether the device_id has appeared in any successful login in the past 7 days. A device not seen before is suspicious.

The final SELECT uses a LEFT JOIN (not INNER JOIN) so all transactions appear in the output. Transactions without a preceding suspicious login get new_device_flag = 0. This preserves the full transaction set for downstream analysis.

  • Select the entire section 4.3 query (both code blocks together form one query) and run it.

You should see all 14 transactions with their velocity columns populated. For ACCT_002's TXN_007, the new_device_flag is 1 and login_country shows RU. For legitimate users, new_device_flag is 0 and login_country is NULL.

All rows showing new_device_flag = 1?

If every transaction has a new_device_flag of 1, check the NOT IN subquery. It looks for devices seen more than 7 days ago. Since your sample data is recent relative to CURRENT_TIMESTAMP(), all devices may appear "new". This is a known edge case with sample data. The pattern works correctly against production data with historical login history.

If this affects your results, confirm that ACCT_002's row shows login_country = 'RU' while ACCT_004's row shows login_country = 'GB'. The geo-country distinction is the meaningful signal here.

Help me understand why new_device_flag is showing unexpected values

  • Save 04_event_sequencing.sql.

✔️ Awesome, I've got everything!

Confirm you have saved your file. You should have three query sections (4.1, 4.2, 4.3) all working.

ⓧ I'd like to double check the full code

Your 04_event_sequencing.sql file should contain three queries in order. Scroll through and confirm:

  • Section 4.1: the login_sequence CTE with prev_device, is_new_device, and failed_logins_10min.
  • Section 4.2: the suspicious_logins CTE with QUALIFY failed_logins_10min >= 2, joined to TRANSACTIONS with the BETWEEN time window.
  • Section 4.3: the combined enrichment with enriched_transactions CTE (velocity metrics) LEFT JOINed to suspicious_logins (new device detection), producing new_device_flag and login_country.

Every transaction now carries both velocity context and login-behavior context. Next up, you will validate these signals against actual fraud outcomes to measure which patterns produce real hits versus false alarms.

Validate Signals with TP/FP Cohort Analysis

You now have velocity signals and event-sequencing signals that flag suspicious transactions. But a flag is not proof. Without comparing your flags against actual fraud outcomes, you have no idea whether you are catching real fraud or harassing legitimate customers.

In this step, you will join your signals to the FRAUD_OUTCOMES table to classify every flag as a true positive (TP) or false positive (FP). Then you will size the dollar impact and compare fraud accounts against legitimate accounts side by side.

In this step, get ready to:
  • Classify flagged transactions as TP or FP and compute signal precision.
  • Calculate leakage captured to size the dollar impact of your velocity signal.
  • Build a fraud-vs-legitimate cohort comparison showing behavioral differences.
Classify flagged transactions as TP or FP

A true positive (TP) is a flagged transaction that actually turned out to be fraud. A false positive (FP) is a flag that hit a legitimate transaction. The ratio between them is your signal's precision. Precision tells you how much your team can trust each flag.

  • Create a new file called 05_cohort_analysis.sql in DataGrip.
  • Paste the following query to classify each velocity-flagged transaction as TP or FP by joining to FRAUD_OUTCOMES:
-- Step 5: TP/FP cohort analysis and risk sizing

-- 5.1 Classify flagged transactions as TP or FP
WITH velocity_flags AS (
    SELECT
        transaction_id,
        account_id,
        transaction_ts,
        amount,
        COUNT(*) OVER (
            PARTITION BY account_id
            ORDER BY transaction_ts
            RANGE BETWEEN INTERVAL '5 minutes' PRECEDING AND CURRENT ROW
        ) AS txn_count_5min
    FROM TRANSACTIONS
    QUALIFY txn_count_5min >= 5
)
SELECT
    vf.transaction_id,
    vf.account_id,
    vf.amount,
    vf.txn_count_5min,
    CASE
        WHEN fo.outcome_id IS NOT NULL THEN 'TP'
        ELSE 'FP'
    END AS classification,
    fo.fraud_amount
FROM velocity_flags vf
LEFT JOIN FRAUD_OUTCOMES fo ON vf.transaction_id = fo.transaction_id
ORDER BY vf.account_id, vf.transaction_ts;

What does this query do?

  • The velocity_flags CTE reuses your QUALIFY pattern from Step 3 to isolate transactions where the 5-minute rolling count hits 5 or more.
  • The LEFT JOIN to FRAUD_OUTCOMES checks whether each flagged transaction resulted in a chargeback. If a match exists, it is TP. If not, FP.
  • Run the query. You should see rows from ACCT_001 with most classified as FP (the small test charges) and TXN_006 classified as TP (the $500 chargeback).

Seeing zero rows?

Make sure you ran 01_setup.sql to load the sample data. The QUALIFY threshold of 5 only fires for ACCT_001 in the sample set.

Why is my TP/FP classification query returning unexpected results?

Now you can see which flags are real. The next query rolls this up into a single precision metric.

  • Add the following query below your classification query in 05_cohort_analysis.sql to compute signal precision and dollar impact:
-- Signal precision and volume
WITH velocity_flags AS (
    SELECT
        transaction_id,
        account_id,
        amount
    FROM TRANSACTIONS
    QUALIFY COUNT(*) OVER (
        PARTITION BY account_id
        ORDER BY transaction_ts
        RANGE BETWEEN INTERVAL '5 minutes' PRECEDING AND CURRENT ROW
    ) >= 5
)
SELECT
    'velocity_5min_gte5' AS signal_name,
    COUNT(*) AS total_flags,
    COUNT(fo.outcome_id) AS true_positives,
    COUNT(*) - COUNT(fo.outcome_id) AS false_positives,
    ROUND(COUNT(fo.outcome_id) / COUNT(*) * 100, 2) AS precision_pct,
    SUM(COALESCE(fo.fraud_amount, 0)) AS fraud_dollars_caught
FROM velocity_flags vf
LEFT JOIN FRAUD_OUTCOMES fo ON vf.transaction_id = fo.transaction_id;

What does this query do?

  • signal_name labels which signal you are evaluating. You will add more signals later and compare them in a single table.
  • precision_pct is the fraction of flags that turned out to be real fraud. Higher precision means fewer wasted investigations.
  • fraud_dollars_caught sizes the impact in dollars, which is what stakeholders care about.
  • Run the query. You should see a single row with signal_name = 'velocity_5min_gte5', the total flag count, true/false positive counts, a precision percentage, and the dollar amount caught.

What is a maturity window?

In production, fraud labels are delayed 30 to 60 days because chargebacks take time to arrive. You cannot evaluate a signal on transactions that are younger than the maturity window. The sample data has pre-set outcomes so you can skip this concern here, but keep it in mind for real data.

Compute leakage captured in dollars

Precision tells you how accurate a signal is. Leakage captured answers the opposite question: of all the fraud that happened, what percentage did this signal catch? This is the number that sizes whether a signal is worth building into production.

  • Add the following query below your precision query in 05_cohort_analysis.sql:
-- 5.2 Leakage captured: what % of total fraud does this signal catch?
WITH total_fraud AS (
    SELECT SUM(fraud_amount) AS total_fraud_dollars
    FROM FRAUD_OUTCOMES
),
velocity_flags AS (
    SELECT transaction_id
    FROM TRANSACTIONS
    QUALIFY COUNT(*) OVER (
        PARTITION BY account_id
        ORDER BY transaction_ts
        RANGE BETWEEN INTERVAL '5 minutes' PRECEDING AND CURRENT ROW
    ) >= 5
),
caught_fraud AS (
    SELECT SUM(fo.fraud_amount) AS caught_dollars
    FROM FRAUD_OUTCOMES fo
    INNER JOIN velocity_flags vf ON fo.transaction_id = vf.transaction_id
)
SELECT
    'velocity_5min_gte5' AS signal_name,
    cf.caught_dollars,
    tf.total_fraud_dollars,
    ROUND(cf.caught_dollars / tf.total_fraud_dollars * 100, 2) AS leakage_captured_pct
FROM caught_fraud cf
CROSS JOIN total_fraud tf;

What does this query do?

  • total_fraud CTE sums all confirmed fraud dollars in the dataset. This is your denominator.
  • caught_fraud CTE uses an INNER JOIN to sum only the fraud dollars on transactions your signal flagged. This is your numerator.
  • The CROSS JOIN brings both single-row CTEs together so you can divide them in the final SELECT.
  • Run the query. You should see caught_dollars, total_fraud_dollars, and a leakage_captured_pct showing what fraction of total fraud this signal catches.
  • Save 05_cohort_analysis.sql.

Why does leakage matter more than precision for stakeholders?

Precision answers "how much of my team's time is wasted on false positives." Leakage captured answers "how much money are we saving." A signal with 50% precision but 80% leakage captured still saves millions. Both numbers together tell the full story.

Build a fraud-vs-legitimate cohort comparison

Individual signal metrics tell you about one rule. A cohort comparison tells you about the entire population. By splitting all accounts into a fraud cohort and a legitimate cohort, you can see which behavioral metrics actually differ between the two groups.

  • Add the following query below your leakage query in 05_cohort_analysis.sql:
-- 5.3 Fraud vs legitimate cohort comparison
WITH fraud_accounts AS (
    SELECT DISTINCT account_id
    FROM FRAUD_OUTCOMES
),
account_metrics AS (
    SELECT
        t.account_id,
        CASE WHEN fa.account_id IS NOT NULL THEN 'fraud' ELSE 'legitimate' END AS cohort,
        COUNT(*) AS txn_count,
        AVG(t.amount) AS avg_amount,
        SUM(t.amount) AS total_spend,
        AVG(DATEDIFF('second',
            LAG(t.transaction_ts) OVER (PARTITION BY t.account_id ORDER BY t.transaction_ts),
            t.transaction_ts
        )) AS avg_seconds_between_txn,
        MAX(COUNT(*) OVER (
            PARTITION BY t.account_id
            ORDER BY t.transaction_ts
            RANGE BETWEEN INTERVAL '5 minutes' PRECEDING AND CURRENT ROW
        )) AS max_velocity_5min
    FROM TRANSACTIONS t
    LEFT JOIN fraud_accounts fa ON t.account_id = fa.account_id
    GROUP BY t.account_id, cohort
)
SELECT
    cohort,
    COUNT(*) AS account_count,
    ROUND(AVG(txn_count), 1) AS avg_txn_count,
    ROUND(AVG(avg_amount), 2) AS avg_txn_amount,
    ROUND(AVG(avg_seconds_between_txn), 0) AS avg_seconds_between,
    ROUND(AVG(max_velocity_5min), 1) AS avg_max_velocity
FROM account_metrics
GROUP BY cohort
ORDER BY cohort;

What does this query do?

  • fraud_accounts CTE identifies all accounts with at least one chargeback. Everyone else goes in the legitimate cohort.
  • account_metrics CTE computes behavioral metrics per account: transaction count, average amount, time between transactions, and peak velocity.
  • The final SELECT averages those metrics by cohort so you get a side-by-side comparison of how fraud accounts behave vs legitimate accounts.

Before you run this query, what do you expect to see? Do you think the fraud cohort will have higher or lower average velocity than the legitimate cohort?

  • Run the query.

You should see a two-row result: one row for the fraud cohort and one for the legitimate cohort. The fraud cohort shows much higher avg_max_velocity and much shorter avg_seconds_between transactions. Your velocity signal catches 60%+ of fraud dollars but also flags some legitimate power users.

Cohort comparison returning NULL values?

The avg_seconds_between_txn column uses LAG which returns NULL for the first transaction in each account. This is expected. The AVG function ignores NULLs, so accounts with only one transaction will show NULL for that column.

Why is my cohort comparison query returning NULLs for time-between metrics?

  • Save 05_cohort_analysis.sql.

What does this side-by-side table prove?

The cohort comparison confirms that velocity is a genuine differentiator between fraud and legitimate accounts. It also reveals the false positive problem: legitimate high-volume accounts (like ACCT_003) can still trigger velocity flags because they transact frequently. A composite score combining multiple signals reduces this noise.

✔️ Awesome, I've got everything!

Double check you have saved 05_cohort_analysis.sql with all four queries (TP/FP classification, signal precision, leakage captured, and cohort comparison).

ⓧ I'd like to double check the full code

Your 05_cohort_analysis.sql should contain the following sections in order. Scroll up and confirm each one is present:

  • The TP/FP classification query (velocity_flags CTE with QUALIFY, LEFT JOIN to FRAUD_OUTCOMES, CASE WHEN classification).
  • The signal precision query (outputs signal_name, total_flags, precision_pct, fraud_dollars_caught).
  • The leakage captured query (total_fraud CTE, caught_fraud CTE, CROSS JOIN, outputs leakage_captured_pct).
  • The cohort comparison query (fraud_accounts CTE, account_metrics CTE, final SELECT grouped by cohort).

You now have a validated signal with precision, dollar sizing, and cohort evidence. This is exactly the format fraud analysts use to present findings to stakeholders. Next up, you will combine multiple signals into a composite risk score in the Secret Mission.

Secret mission

Composite Signal Catalog with Lift Ratios

Individual signals catch some fraud but generate noise. Can you combine multiple signals into a single risk score, using lift ratios to weight each signal by how predictive it actually is? This mission pulls together every technique from the project into one multi-layered query.

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project runs entirely within your existing Snowflake environment using tiny sample tables, so there are no ongoing costs.

Resources you used:

  • Four sample tables in Snowflake: TRANSACTIONS, LOGIN_EVENTS, FRAUD_OUTCOMES, USER_PROFILES.
  • Six local SQL files: 01_setup.sql through 06_signal_catalog.sql.

Keep everything running

No action needed. Choose this if you plan to adapt these queries for your own production data or want a sandbox to experiment with new signals.

  • The sample tables contain only 14 rows of data total. Storage cost is effectively zero.
  • Your local SQL files remain on disk as a reusable workbook you can point at any dataset.

Pause - I'll come back to this later

The sample tables cost nothing to store, so pausing looks the same as keeping. If you want to reduce clutter in your Snowflake schema browser, you can leave the tables but close your DataGrip connection.

  • In DataGrip, right-click your Snowflake data source and select Disconnect.
  • Your SQL files and Snowflake tables will be waiting when you reconnect.

Delete - I don't want to use this again

Remove all project resources and start fresh. This drops the sample tables from Snowflake and deletes your local SQL files.

  • Drop the sample tables by running this in DataGrip:
DROP TABLE IF EXISTS TRANSACTIONS;
DROP TABLE IF EXISTS LOGIN_EVENTS;
DROP TABLE IF EXISTS FRAUD_OUTCOMES;
DROP TABLE IF EXISTS USER_PROFILES;
  • Delete your local SQL files. On macOS or Linux, run:
rm 01_setup.sql 02_explore.sql 03_velocity_signals.sql 04_event_sequencing.sql 05_cohort_analysis.sql 06_signal_catalog.sql
  • On Windows, run:
Remove-Item 01_setup.sql, 02_explore.sql, 03_velocity_signals.sql, 04_event_sequencing.sql, 05_cohort_analysis.sql, 06_signal_catalog.sql

Nice Work!

Nice Work!

Nice work! You just built a complete fraud investigation SQL workbook that takes raw transaction and event data and turns it into a ranked risk queue with actionable recommendations.

You've learned how to:

  • Build reusable velocity signal queries using RANGE BETWEEN INTERVAL and QUALIFY in Snowflake SQL, and write time-bounded cross-table joins linking login events to transactions for account-takeover detection.
  • Calculate precision, leakage captured, and false positive rates to validate each signal against actual fraud outcomes, and size risk in dollars.
  • Compare fraud vs legitimate cohort behavior side by side using behavioral metrics like velocity, transaction timing, and device-change rates.
  • Secret Mission: Built a composite risk scoring system with lift-ratio-weighted signals and BLOCK/REVIEW/ALLOW classification.

Ready to quiz yourself?