Suspicious Trades
ASKED IN INTERVIEWRobinhood
Problem Statement
Robinhood's compliance team monitors for unusual trading patterns. One specific pattern is "Burst Trading," where a user executes a high volume of trades for a single stock in a very short window.
Write an SQL query to find users who made 5 or more trades of the same stock within the same clock minute.
Rules:
- A "Burst" is defined as >= 5 trades for the same user_id and stock_id within the same YYYY-MM-DD HH:MM.
- Output columns: user_id, stock_id, trade_minute, trade_count.
- trade_minute should be formatted as a TIMESTAMP truncated to the minute.
- Results must be returned in descending order of trade_count.
Table Schema:
- Trades: trade_id, user_id, stock_id, trade_timestamp (TIMESTAMP), quantity, price.
Tests your understanding of
DateTime Aggregation, Filtering, Grouping and Security
Input Tables
Trades
| user_id(INTEGER) | stock_id(VARCHAR) | trade_timestamp(TIMESTAMP) |
|---|---|---|
| 101 | HOOD | 2026-02-13 10:01:05 |
| 101 | HOOD | 2026-02-13 10:01:12 |
| 101 | HOOD | 2026-02-13 10:01:25 |
| 101 | HOOD | 2026-02-13 10:01:38 |
| 101 | HOOD | 2026-02-13 10:01:55 |
| 102 | AAPL | 2026-02-13 10:01:10 |
| 101 | HOOD | 2026-02-13 10:05:00 |
| 103 | TSLA | 2026-02-13 11:00:05 |
Expected Output
| user_id(INTEGER) | stock_id(VARCHAR) | trade_minute(TIMESTAMP) | trade_count(INTEGER) |
|---|---|---|---|
| 101 | HOOD | 2026-02-13 10:01:00 | 5 |
Tags
MediumASKED IN INTERVIEWDateTime AggregationFilteringGroupingSecurity
15-20 min
41%