Fraudulent Transactions
Visa's Anti-Money Laundering (AML) department is responsible for identifying potentially suspicious financial transactions to ensure compliance with global financial regulations. A key part of their investigation process involves tracking receipts of large transactions. These receipts typically have an associated receipt number.
You're provided with two tables:
customers:customer_id(integer): A unique identifier for the customer.first_name(string): The first name of the customer.last_name(string): The last name of the customer.
transactions:transaction_id(integer): A unique identifier for the transaction.customer_id(integer): The ID of the customer associated with the transaction.receipt_number(string): The receipt number for the transaction.amount(float): The amount of money involved in the transaction.
The AML team has identified a list of potentially suspicious receipt numbers based on certain patterns. The patterns they're concerned about are '999', '1234', and 'XYZ'. Customers are flagged as potential money launderers if they have 2 or more such receipts matching these patterns.
Write an SQL query that returns a table with all suspicious receipt numbers made by potential money launderers. Ensure you display the customer's first name, last name, the associated suspicious receipt number and the number of offences the customer has committed.
Your output should contain the following columns: first_name, last_name, receipt_number, no_of_offences.
We use a Common Table Expression (CTE) named SuspiciousTransactions to pre-filter the transactions to only those with suspicious receipt numbers, using the LIKE operator for pattern matching.
Within the CTE, COUNT(*) OVER(PARTITION BY customer_id) calculates the total number of suspicious transactions for each customer, which is represented as no_of_offences.
Finally, we join it with the customers table to fetch the customer details and further filter the results to only include those customers with no_of_offences greater than or equal to 2.
WITH SuspiciousTransactions AS (
SELECT
customer_id,
receipt_number,
COUNT(*) OVER(PARTITION BY customer_id) AS no_of_offences
FROM
transactions
WHERE
receipt_number LIKE '%999%'
OR receipt_number LIKE '%1234%'
OR receipt_number LIKE '%XYZ%'
)
SELECT
c.first_name,
c.last_name,
s.receipt_number,
s.no_of_offences
FROM
customers c
JOIN
SuspiciousTransactions s ON c.customer_id = s.customer_id
WHERE
s.no_of_offences >= 2;
WITH suspicious_transactions AS ( SELECT c.first_name, c.last_name, t.receipt_number, COUNT(t.receipt_number) OVER (PARTITION BY c.customer_id) AS no_of_offences FROM customers c JOIN transactions t ON c.customer_id = t.customer_id WHERE t.receipt_number LIKE '%999%' OR t.receipt_number LIKE '%1234%' OR t.receipt_number LIKE '%XYZ%' ) SELECT first_name, last_name, receipt_number, no_of_offences FROM suspicious_transactions WHERE no_of_offences >= 2;