Identify Top Users with Declined Transactions in SQL
Company: Robinhood
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
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.
Overview: 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.
You are working with a payments transactions table used by the fraud analytics team. Analyze the inclusive period from 2025-05-26 through 2025-06-01.
Return one combined result set with columns result_set, user_id, total_declined_amount, window_start, risky_flag, transaction_id, amount, status, timestamp, and risk_level:
1. result_set = 'top_declined_users': the top 3 users by total declined transaction amount in the period.
2. result_set = 'daily_risky_windows': for every user and calendar day present in the data, flag whether that user had more than two risky transactions (amount IS NULL OR amount > 500) within any 24-hour rolling window that starts on that calendar day.
3. result_set = 'transaction_risk_levels': all transactions enriched with risk_level using high for amount > 800, medium for amount BETWEEN 500 AND 800, and low otherwise, ordered by timestamp descending.
Fields that do not apply to a row should be NULL.
Tables
transactions(transaction_id INTEGER, user_id INTEGER, amount DECIMAL(10,2), status VARCHAR(20), timestamp TIMESTAMP)
Hints
- Use a half-open timestamp filter for the inclusive date period.
- For rolling windows, compare each risky transaction to later risky transactions from the same user within 24 hours.