Coding questions · Robinhood
Identify Top Users with Declined Transactions in SQL
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.
Fraud risk analytics on a transactions table
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:
- result_set = 'top_declined_users': the top 3 users by total declined transaction amount in the period.
- 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.
- 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.
Table transactions
| column | type | constraints |
|---|---|---|
| transaction_id | INTEGER | PRIMARY KEY, NOT NULL |
| user_id | INTEGER | NOT NULL |
| amount | DECIMAL(10,2) | |
| status | VARCHAR(20) | NOT NULL |
| timestamp | TIMESTAMP | NOT NULL |
Sample data:
| amount | status | timestamp | transaction_id | user_id |
|---|---|---|---|---|
| 120.5 | success | 2025-05-26 10:00:00 | 101 | 10 |
| 250 | declined | 2025-05-27 12:30:00 | 102 | 10 |
| 900 | success | 2025-05-26 11:15:00 | 103 | 12 |
| 80 | success | 2025-05-28 09:40:00 | 104 | 10 |
| 500 | success | 2025-05-27 14:55:00 | 105 | 13 |
Expected output
One combined result set for the three fraud analytics outputs: top declined users, daily risky-window flags, and transaction-level risk labels.
| amount | result_set | risk_level | risky_flag | status | timestamp | total_declined_amount | transaction_id | user_id | window_start |
|---|---|---|---|---|---|---|---|---|---|
| NULL | top_declined_users | NULL | NULL | NULL | NULL | 250 | NULL | 10 | NULL |
| NULL | daily_risky_windows | NULL | 0 | NULL | NULL | NULL | NULL | 10 | 2025-05-26 00:00:00 |
| NULL | daily_risky_windows | NULL | 0 | NULL | NULL | NULL | NULL | 12 | 2025-05-26 00:00:00 |
| NULL | daily_risky_windows | NULL | 0 | NULL | NULL | NULL | NULL | 10 | 2025-05-27 00:00:00 |
| NULL | daily_risky_windows | NULL | 0 | NULL | NULL | NULL | NULL | 13 | 2025-05-27 00:00:00 |
| NULL | daily_risky_windows | NULL | 0 | NULL | NULL | NULL | NULL | 10 | 2025-05-28 00:00:00 |
| 80 | transaction_risk_levels | low | NULL | success | 2025-05-28 09:40:00 | NULL | 104 | 10 | NULL |
| 500 | transaction_risk_levels | medium | NULL | success | 2025-05-27 14:55:00 | NULL | 105 | 13 | NULL |
| 250 | transaction_risk_levels | low | NULL | declined | 2025-05-27 12:30:00 | NULL | 102 | 10 | NULL |
| 900 | transaction_risk_levels | high | NULL | success | 2025-05-26 11:15:00 | NULL | 103 | 12 | NULL |
| 120.5 | transaction_risk_levels | low | NULL | success | 2025-05-26 10:00:00 | NULL | 101 | 10 | NULL |
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.
- Use TO_CHAR when the expected output needs stable timestamp strings.