Open positions by user
mediumOpen positions by user
Robinhood SQL Interview Question
Robinhood's portfolio page shows how many shares of each stock a user still holds, based on their buy and sell trades.
For each user_id and symbol, find the shares bought minus the shares sold as net_shares. Only return positions where the user still holds more than 0 shares. Sort by user_id, then by symbol.
Asked of
- Data Analyst
- Business Analyst
- BI Analyst
- Product Analyst
- Analytics Engineer
tradesTable18 rows
| Column Name | Type |
|---|---|
| trade_id | BIGINT |
| user_id | BIGINT |
| symbol | VARCHAR |
| side | VARCHAR |
| shares | BIGINT |
| price | DOUBLE |
| executed_at | TIMESTAMP |
tradesExample Input
| trade_id | user_id | symbol | side | shares | price | executed_at |
|---|---|---|---|---|---|---|
| 61001 | 801 | AAPL | buy | 10 | 185.2 | 2024-01-04 09:35:12 |
| 61002 | 802 | TSLA | buy | 5 | 238.45 | 2024-01-09 10:02:44 |
| 61003 | 801 | NVDA | buy | 2 | 495.1 | 2024-01-22 13:15:05 |
| 61004 | 803 | AMZN | buy | 8 | 151.3 | 2024-01-30 15:42:18 |
| 61005 | 802 | TSLA | sell | 5 | 187.9 | 2024-02-06 09:48:31 |
| 61006 | 804 | AAPL | buy | 3 | 188.85 | 2024-02-13 11:21:09 |
| 61007 | 801 | AAPL | sell | 4 | 182.3 | 2024-02-20 14:05:57 |
| 61008 | 803 | NVDA | buy | 1 | 721.33 | 2024-02-27 10:30:00 |
| 61009 | 805 | AMZN | buy | 12 | 175.1 | 2024-03-01 09:31:40 |
| 61010 | 804 | TSLA | buy | 6 | 199.4 | 2024-03-08 12:44:23 |
| 61011 | 802 | NVDA | buy | 2 | 875.28 | 2024-03-14 15:10:11 |
| 61012 | 805 | AAPL | sell | 2 | 172.62 | 2024-03-21 13:55:02 |
| 61013 | 801 | NVDA | sell | 2 | 903.56 | 2024-03-28 09:59:48 |
| 61014 | 803 | AMZN | sell | 8 | 180.97 | 2024-04-02 10:12:30 |
| 61015 | 804 | AAPL | buy | 5 | 169.61 | 2024-04-10 11:47:16 |
| 61016 | 806 | TSLA | buy | 10 | 162.5 | 2024-04-16 14:22:05 |
| 61017 | 805 | NVDA | buy | 1 | 846.71 | 2024-04-23 09:40:59 |
| 61018 | 802 | AMZN | buy | 4 | 179.62 | 2024-04-30 15:58:21 |
Example Output
| user_id | symbol | net_shares |
|---|---|---|
| 801 | AAPL | 6 |
| 802 | AMZN | 4 |
| 802 | NVDA | 2 |
| 803 | NVDA | 1 |
| 804 | AAPL | 8 |
| 804 | TSLA | 6 |
| 805 | AMZN | 12 |
| 805 | NVDA | 1 |
| 806 | TSLA | 10 |
Explanation
User 801 bought 10 AAPL shares and sold 4, so 6 are left. They also bought and sold 2 NVDA shares, which leaves 0, so that position is not shown. User 805 sold 2 AAPL shares with no matching buy in the data, which leaves -2, so it is not shown either.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Robinhood
Difficulty
medium
Topic
conditional logic
Language
SQL
Your query
Starting the SQL engine...