Busiest stock per client
mediumBusiest stock per client
Zerodha SQL Interview Question
Zerodha earns on volume, so the account team wants each client's biggest stock by the money moved through it. Both directions count: a client who bought ten lakh and sold ten lakh generated twice the business of one who only bought.
For each client, return the client_id, the symbol they moved the most money through, and that amount as traded_value.
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_value |
|---|---|---|
| Z1001 | INFY | 445250 |
| Z1004 | INFY | 445500 |
Explanation
Z1001 moved 445250 through INFY and 308000 through TCS, so INFY is their busiest stock. Z1004 traded only INFY, buying and then selling the same 150 shares, and both sides of that count toward the total.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Zerodha
Difficulty
medium
Topic
ranking
Language
SQL
Your query
Starting the SQL engine...