Risk

If you work in risk, it's crucial to stay manage riskly behavior on your platform. Let's take a look at some popular queries to track and sample SQL queries:

Average transaction amount per user

SELECT user_id, AVG(amount) AS avg_transaction_amount
FROM transactions
GROUP BY user_id;

This query calculates the average transaction amount for each user. This can be useful for a risk analyst to identify users with unusually high or low transaction amounts.

Users with a high number 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;

This query finds users with more than five declined transactions. A high number of declined transactions can be a red flag for potential fraud or high credit risk.

Total transaction amount per country

SELECT country, SUM(amount) AS total_transaction_amount
FROM transactions
GROUP BY country;

This query calculates the total transaction amount for each country. This can help a risk analyst identify countries with high transaction volumes, which may require further investigation or increased monitoring.

Average transaction amount per industry

SELECT industry, AVG(amount) AS avg_transaction_amount
FROM transactions
JOIN merchants ON transactions.merchant_id = merchants.merchant_id
GROUP BY industry;

This query calculates the average transaction amount for each industry. This can help a risk analyst identify industries with unusually high transaction amounts, which could be indicative of higher risk.

Merchants with the highest chargeback rates

SELECT merchant_id, COUNT() AS num_transactions, SUM(CASE WHEN status = 'chargeback' THEN 1 ELSE 0 END) AS num_chargebacks, (SUM(CASE WHEN status = 'chargeback' THEN 1 ELSE 0 END) / COUNT()) * 100 AS chargeback_rate
FROM transactions
GROUP BY merchant_id
ORDER BY chargeback_rate DESC
LIMIT 10;

This query identifies the top 10 merchants with the highest chargeback rates. High chargeback rates can be a sign of poor customer service, product quality issues, or fraud.

Total transaction amount for each payment method

SELECT payment_method, SUM(amount) AS total_transaction_amount
FROM transactions
GROUP BY payment_method;

This query calculates the total transaction amount for each payment method. This can help a risk analyst identify payment methods with high transaction volumes, which may require further investigation or increased monitoring.

Users with transactions in multiple countries

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

This query finds users who have made transactions in more than one country. This can be a sign of potential fraud or increased risk.

Average transaction amount per day of the week

SELECT DAYNAME(timestamp) AS day_of_week, AVG(amount) AS avg_transaction_amount
FROM transactions
GROUP BY day_of_week;

This query calculates the average transaction amount for each day of the week. This can help a risk analyst identify patterns or trends in transaction amounts by day of the week.

Merchants with the highest transaction amounts

SELECT merchant_id, SUM(amount) AS total_transaction_amount
FROM transactions
GROUP BY merchant_id
ORDER BY total_transaction_amount DESC
LIMIT 10;

This query finds the top 10 merchants with the highest transaction amounts. This can help a risk analyst identify merchants with high transaction volumes, which may require further investigation or increased monitoring.

Users with an increasing trend in transaction amounts

SELECT user_id
FROM (
SELECT user_id, LAG(amount) OVER (PARTITION BY user_id ORDER BY timestamp) AS prev_amount, amount
FROM transactions
) AS subquery
WHERE amount > prev_amount
GROUP BY user_id
HAVING COUNT(*) >= 3;

This query identifies users with an increasing trend in transaction amounts over consecutive transactions. This could be a sign of potential fraud or increased risk.

Find the total transaction amount per user for each month

SELECT user_id, EXTRACT(MONTH FROM timestamp) AS month, SUM(amount) AS total_transaction_amount
FROM transactions
GROUP BY user_id, month;

This query calculates the total transaction amount for each user for each month. This can help a risk analyst identify patterns or trends in transaction amounts by month.

Find transactions with a high amount-to-shipping cost ratio

SELECT transaction_id, amount, shipping_cost, amount / shipping_cost AS amount_to_shipping_cost_ratio
FROM transactions
WHERE amount / shipping_cost > 10;

This query identifies transactions with a high amount-to-shipping cost ratio. A high ratio could be an indicator of potential fraud or increased risk.

Users with a high number of transactions in a short time frame

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

This query finds users who have made a high number of transactions in a short time frame (e.g., 1 day). This can be a sign of potential fraud or increased risk.

Find the average transaction amount per hour of the day

SELECT EXTRACT(HOUR FROM timestamp) AS hour_of_day, AVG(amount) AS avg_transaction_amount
FROM transactions
GROUP BY hour_of_day;

This query calculates the average transaction amount for each hour of the day. This can help a risk analyst identify patterns or trends in transaction amounts by hour of the day.

Users with multiple transactions using different devices

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

This query identifies users who have made multiple transactions using different devices. This could be an indicator of potential fraud or increased risk.

Users with a high percentage of transactions using promo codes

SELECT user_id, COUNT(*) AS num_transactions, 
SUM(CASE WHEN promo_code IS NOT NULL THEN 1 ELSE 0 END) AS num_promo_transactions,
(SUM(CASE WHEN promo_code IS NOT NULL THEN 1 ELSE 0 END) / COUNT(*)) * 100 AS promo_transaction_percentage
FROM transactions
GROUP BY user_id
HAVING promo_transaction_percentage > 50;

This query identifies users who have used promo codes in more than 50% of their transactions. A high percentage of transactions using promo codes could be an indicator of potential abuse of promotional offers or increased risk.