Fraud

If you work in fraud, it's crucial to keep monitor suspicious behavior on your platform. Let's take a look at some popular queries to track and sample SQL queries:

Users with multiple accounts using the same email

SELECT email, COUNT(*) AS num_accounts 
FROM users 
GROUP BY email 
HAVING num_accounts > 1;

Users with abnormally high transaction amounts

SELECT user_id, COUNT(*) AS num_large_transactions
FROM transactions
WHERE amount > (SELECT AVG(amount) + 3 * STDDEV(amount) FROM transactions)
GROUP BY user_id;

Users with rapid succession of transactions

SELECT user_id, COUNT(*) AS num_fast_transactions
FROM transactions
WHERE TIMESTAMPDIFF(MINUTE, previous_timestamp, timestamp) < 1
GROUP BY user_id;

Transactions with mismatched billing and shipping addresses

SELECT transaction_id, billing_address, shipping_address 
FROM transactions 
WHERE billing_address <> shipping_address;

Users with transactions from multiple countries

SELECT user_id, COUNT(DISTINCT country) AS num_countries 
FROM transactions 
GROUP BY user_id 
HAVING num_countries > 1;

Transactions with suspiciously high chargeback rates

SELECT product_id, COUNT() AS num_chargebacks
FROM transactions
WHERE status = 'chargeback'
GROUP BY product_id
HAVING num_chargebacks > (SELECT AVG(num_chargebacks) + 3 * STDDEV(num_chargebacks) 
FROM (
    SELECT product_id, COUNT() AS num_chargebacks 
    FROM transactions 
    WHERE status = 'chargeback' 
    GROUP BY product_id
) AS subquery);

Users with multiple failed login attempts

SELECT user_id, COUNT(*) AS num_failed_logins
FROM login_attempts
WHERE status = 'failed'
GROUP BY user_id
HAVING num_failed_logins > 5;

Users with abnormally high transaction frequency

SELECT user_id, COUNT() AS num_transactions
FROM transactions
GROUP BY user_id
HAVING num_transactions > (
    SELECT AVG(num_transactions) + 3 * STDDEV(num_transactions) 
    FROM (
        SELECT user_id, COUNT() AS num_transactions 
        FROM transactions 
        GROUP BY user_id) 
    AS subquery);

Users with high count of declined transactions

SELECT user_id, COUNT(*) AS num_declined_transactions
FROM transactions
WHERE status = 'declined'
GROUP BY user_id
HAVING num_declined_transactions > 5;

Users with multiple credit cards

SELECT user_id, COUNT(DISTINCT card_number) AS num_cards
FROM transactions
GROUP BY user_id
HAVING num_cards > 3;

Transactions with unusually high shipping costs

SELECT transaction_id, shipping_cost
FROM transactions
WHERE shipping_cost > (
    SELECT AVG(shipping_cost) + 3 * STDDEV(shipping_cost) 
    FROM transactions
);

Users who have used multiple IP addresses

SELECT user_id, COUNT(DISTINCT ip_address) AS num_ip_addresses
FROM login_attempts
GROUP BY user_id
HAVING num_ip_addresses > 2;

User with multiple accounts using the same phone number

SELECT phone, COUNT(*) AS num_accounts
FROM users
GROUP BY phone
HAVING num_accounts > 1;

Transactions with perfectly round amounts

SELECT transaction_id, amount
FROM transactions
WHERE amount = ROUND(amount);

Users with a high rate of coupon usage

SELECT user_id, COUNT() AS num_coupons_used
FROM transactions
WHERE coupon_code IS NOT NULL
GROUP BY user_id
HAVING num_coupons_used > (
    SELECT AVG(num_coupons_used) + 3 * STDDEV(num_coupons_used) 
    FROM (
        SELECT user_id, COUNT() AS num_coupons_used 
        FROM transactions 
        WHERE coupon_code IS NOT NULL 
        GROUP BY user_id) 
    AS subquery);

Transactions with suspiciously low selling price

SELECT product_id, MIN(price) AS min_price
FROM transactions
GROUP BY product_id
HAVING min_price < (SELECT AVG(price) - 3 * STDDEV(price) FROM transactions);

Users who have made transactions in a short time frame

SELECT user_id, COUNT(*) AS num_transactions
FROM transactions
WHERE TIMESTAMPDIFF(HOUR, previous_timestamp, timestamp) < 1
GROUP BY user_id;

Transactions with unusually high quantities

SELECT transaction_id, product_id, quantity
FROM transactions
WHERE quantity > (SELECT AVG(quantity) + 3 * STDDEV(quantity) FROM transactions);

Users with multiple transactions using different billing names

SELECT user_id, COUNT(DISTINCT billing_name) AS num_billing_names
FROM transactions
GROUP BY user_id
HAVING num_billing_names > 1;