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;