PhonePe: Open Share.Market Positions From the Order Book
Share.Market keeps an order book of every buy and sell a user places.
- Difficulty: Medium
- Topics: Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #885
Problem statement
Share.Market keeps an order book of every buy and sell a user places. The holdings service is being rebuilt, and the engineer wants to check its output against the open positions derived straight from the orders.
For each user and symbol, the net quantity is the quantity of executed BUY orders minus the quantity of executed SELL orders; rejected and cancelled orders never traded. Return user_id, symbol and net_quantity for every position whose net quantity is greater than 0 — a position at 0 has been closed. Order the rows by user_id, then symbol.
A practice scenario set at PhonePe: the tables and data are invented, and this is not a question from, or endorsed by, PhonePe.
Tables
Table: TradeOrder
| Column | Type |
|---|---|
| order_id | int |
| user_id | int |
| symbol | varchar |
| side | enum(BUY, SELL) |
| quantity | int |
| status | enum(executed, rejected, cancelled) |
| placed_at | datetime |
Primary key: order_id.
One row per order; quantity is the number of shares and is always positive.
Examples
Example 1
TradeOrder
| order_id | user_id | symbol | side | quantity | status | placed_at |
|---|---|---|---|---|---|---|
| 1 | 7 | INFY | BUY | 10 | executed | 2025-02-03 09:20:00 |
| 2 | 7 | INFY | SELL | 4 | executed | 2025-02-05 10:10:00 |
| 3 | 7 | ITC | BUY | 50 | executed | 2025-02-03 09:25:00 |
| 4 | 7 | ITC | SELL | 50 | executed | 2025-02-10 14:00:00 |
| 5 | 8 | SBIN | BUY | 20 | rejected | 2025-02-04 11:00:00 |
| 6 | 8 | TCS | BUY | 2 | executed | 2025-02-04 11:05:00 |
| 7 | 8 | TCS | SELL | 1 | cancelled | 2025-02-06 12:00:00 |
| 8 | 9 | SBIN | BUY | 15 | executed | 2025-02-07 09:45:00 |
Output
| user_id | symbol | net_quantity |
|---|---|---|
| 7 | INFY | 6 |
| 8 | TCS | 2 |
| 9 | SBIN | 15 |
How to solve PhonePe: Open Share.Market Positions From the Order Book
A position is the running result of trades, and its final size is simply a sum: each executed buy adds its quantity, each executed sell subtracts it. CASE WHEN side = 'BUY' THEN quantity ELSE -quantity END turns every order into a signed quantity, and SUM of that per (user_id, symbol) is the net position. Rejected and cancelled orders never reached the exchange, so status = 'executed' filters them out before grouping — user 8's rejected SBIN buy leaves no position.
Closed positions (net 0, like user 7's ITC) are removed by a condition on the aggregate, which belongs in HAVING. The statement fixes the order, so the query ends with ORDER BY user_id, symbol.
The alternatives make the two sides explicit: one sums buys and sells in separate derived tables and subtracts after a left join (COALESCE covers a symbol never sold), the other subtracts two conditional sums and filters in an outer query. All are one or two scans with a hash aggregate per position.
Reference solution (MySQL)
SELECT user_id, symbol,
SUM(CASE WHEN side = 'BUY' THEN quantity ELSE -quantity END) AS net_quantity
FROM TradeOrder
WHERE status = 'executed'
GROUP BY user_id, symbol
HAVING SUM(CASE WHEN side = 'BUY' THEN quantity ELSE -quantity END) > 0
ORDER BY user_id, symbolAnother way
SELECT b.user_id, b.symbol, b.qty - COALESCE(s.qty, 0) AS net_quantity
FROM (SELECT user_id, symbol, SUM(quantity) AS qty FROM TradeOrder WHERE status = 'executed' AND side = 'BUY' GROUP BY user_id, symbol) b
LEFT JOIN (SELECT user_id, symbol, SUM(quantity) AS qty FROM TradeOrder WHERE status = 'executed' AND side = 'SELL' GROUP BY user_id, symbol) s
ON s.user_id = b.user_id AND s.symbol = b.symbol
WHERE b.qty - COALESCE(s.qty, 0) > 0
ORDER BY b.user_id, b.symbolAnother way
SELECT user_id, symbol, net_quantity FROM (SELECT user_id, symbol, SUM(IF(side = 'BUY', quantity, 0)) - SUM(IF(side = 'SELL', quantity, 0)) AS net_quantity FROM TradeOrder WHERE status = 'executed' GROUP BY user_id, symbol) t WHERE net_quantity > 0 ORDER BY user_id, symbol← PhonePe: Users Whose First Successful Payment Was at a Merchant · PhonePe: Motor Policy Renewal Rate by Expiry Month →