SQLhardCtes~22m

A major investment bank needs to identify potentially fraudulent card...

Problem

A major investment bank needs to identify potentially fraudulent card transactions by detecting accounts that have both (1) made high-value transactions (>$10,000) in multiple different branches within the same day, AND (2) had their card used for transactions totaling more than $50,000 across all branches in that same day. For each suspicious account, return the account_id, cardholder_name, suspicious_date, total_daily_amount, branch_count (number of different branches used that day), and max_single_transaction. Results should be ordered by total_daily_amount descending, then by account_id ascending for ties.

Examples

Example 1

Example 2

Constraints

›Return accounts with high-value transactions (>$10,000) in multiple branches on same day
›Total daily amount must exceed $50,000
›Include cardholder_name, suspicious_date, total_daily_amount, branch_count, max_single_transaction
›Order by total_daily_amount DESC, then account_id ASC

Reference solution

Reference solution available after you attempt the question.

Ready to solve it?

Start a session on Mockbit #64. Write your SQL, run it against real data, and get graded on correctness, efficiency, query structure, and business understanding.