Katkat
Back

Identify Top Users with Declined Transactions in SQL

RobinhoodData Scientist · Technical Screen
MediumData ScientistTechnical ScreenData Manipulation (SQL/Python)

Last updated

This question evaluates proficiency in SQL data manipulation, specifically aggregation, window functions, rolling 24-hour time-window analytics, NULL handling, and conditional classification via CASE WHEN within a fraud/risk detection scenario.

Transactions

+----------------+---------+--------+----------+---------------------+ | transaction_id | user_id | amount | status | timestamp | +----------------+---------+--------+----------+---------------------+ | 101 | 10 | 120.50 | success | 2023-06-01 10:00:00 | | 102 | 10 | 250.00 | declined | 2023-06-02 12:30:00 | | 103 | 12 | 900.00 | success | 2023-06-01 11:15:00 | | 104 | 10 | 80.00 | success | 2023-06-03 09:40:00 | | 105 | 13 | 500.00 | success | 2023-06-02 14:55:00 | +----------------+---------+--------+----------+---------------------+

Scenario

Risk/Fraud analytics team needs SQL analysts to detect suspicious payment behaviors from a transactions table.

Question

Write a query to return the top-3 users with the highest total amount of declined transactions in the last 7 days (relative to CURRENT_DATE). 2. For every user and day, flag if they had more than two transactions whose amount IS NULL OR amount > 500 within any 24-hour rolling window; output user_id, window_start, risky_flag. 3. Add a column risk_level using CASE WHEN: 'high' if amount > 800, 'medium' if amount BETWEEN 500 AND 800, else 'low'. Return 10 sample rows ordered by timestamp DESC.

Hints

Pay attention to NULL handling with CASE WHEN; use window functions and DATE arithmetic.