--As part of a cryptocurrency trade monitoring platform, create a query to return a list of suspicious transactions.
Suspicious transactions are defined as:
• a series of two or more transactions occur at intervals of an hour or less
• they are from the same sender
• the sum of transactions in a sequence is 150 or greater
A sequence of suspicious transactions may occur over time periods greater than one hour. As an example, there are 5 transactions from one sender for 30 each. They occur at intervals of less than an hour between from 8 AM to 11 AM. These are suspicious and will all be reported as one sequence that starts at 8 AM, ends at 11 AM, with 5 transactions that sum to 150.
The result should have the following columns: sender, sequence_start, sequence_end, transactions_count, transactions_sum
• sender is the sender's address.
• sequence_start is the timestamp of the first transaction in the sequence.
• sequence_end is the timestamp of the last transaction in the sequence.
• transactions_ count is the number of transactions in the sequence.
• transactions_sum is the sum of transaction amounts in the sequence, to 6 places after the decimal.
Order the data ascending, first by sender, then by sequence_start, and finally by sequence_end.
Solution:
WITH ranked_transactions AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY sender ORDER BY transaction_timestamp) AS rn
FROM transactions
),
time_diff AS (
SELECT *,
DATEDIFF(MINUTE, LAG(transaction_timestamp) OVER (PARTITION BY sender ORDER BY transaction_timestamp), transaction_timestamp) AS time_diff
FROM ranked_transactions
)
SELECT sender,
MIN(transaction_timestamp) AS sequence_start,
MAX(transaction_timestamp) AS sequence_end,
COUNT(*) AS transactions_count,
ROUND(SUM(amount), 6) AS transactions_sum
FROM time_diff
GROUP BY sender, rn - ROW_NUMBER() OVER (PARTITION BY sender ORDER BY transaction_timestamp)
HAVING COUNT(*) >= 2 AND SUM(amount) >= 150
ORDER BY sender, sequence_start, sequence_end;
Hacker Rank Crypto Market Transaction Monitoring Problem Solution
