Position after each trade
mediumPosition after each trade
Zerodha SQL Interview Question
Zerodha has to know what every client is holding at any moment, because margin is charged on the position, not on the trades that built it. The log records buys and sells as separate rows, so a holding at any point is the buys so far minus the sells so far.
Return the client_id, the symbol, the traded_on date, the side of the trade, and the shares the client holds in that symbol immediately after it as position_after. Sort by client_id, then symbol, then traded_on.
Asked of
- Data Analyst
- Business Analyst
- BI Analyst
- Product Analyst
- Analytics Engineer
- Data Engineer
- Data Scientist
zerodha_tradesTable21 rows
| Column Name | Type |
|---|---|
| trade_id | BIGINT |
| client_id | VARCHAR |
| symbol | VARCHAR |
| traded_on | DATE |
| side | VARCHAR |
| quantity | BIGINT |
| price | DOUBLE |
zerodha_tradesExample Input
| trade_id | client_id | symbol | traded_on | side | quantity | price |
|---|---|---|---|---|---|---|
| 9001 | Z1001 | INFY | 2024-07-01 | buy | 100 | 1450 |
| 9002 | Z1001 | TCS | 2024-07-02 | buy | 40 | 3800 |
| 9003 | Z1001 | INFY | 2024-07-03 | buy | 50 | 1480 |
| 9004 | Z1001 | INFY | 2024-07-05 | sell | 80 | 1520 |
| 9005 | Z1001 | TCS | 2024-07-06 | sell | 15 | 3950 |
| 9006 | Z1001 | INFY | 2024-07-08 | sell | 70 | 1495 |
| 9007 | Z1001 | TCS | 2024-07-09 | buy | 25 | 3870 |
| 9020 | Z1004 | INFY | 2024-07-02 | buy | 150 | 1440 |
| 9021 | Z1004 | INFY | 2024-07-10 | sell | 150 | 1530 |
Example Output
| client_id | symbol | traded_on | side | position_after |
|---|---|---|---|---|
| Z1001 | INFY | 2024-07-01 | buy | 100 |
| Z1001 | INFY | 2024-07-03 | buy | 150 |
| Z1001 | INFY | 2024-07-05 | sell | 70 |
| Z1001 | INFY | 2024-07-08 | sell | 0 |
| Z1001 | TCS | 2024-07-02 | buy | 40 |
| Z1001 | TCS | 2024-07-06 | sell | 25 |
| Z1001 | TCS | 2024-07-09 | buy | 50 |
| Z1004 | INFY | 2024-07-02 | buy | 150 |
| Z1004 | INFY | 2024-07-10 | sell | 0 |
Explanation
Z1001 buys 100 shares of INFY and holds 100. A second buy of 50 takes the holding to 150, a sale of 80 brings it down to 70, and a sale of 70 leaves nothing. The TCS holding is counted on its own and is never mixed in with INFY.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Zerodha
Difficulty
medium
Topic
running totals
Language
SQL
Your query
Starting the SQL engine...