PhonePe: Open Share.Market Positions From the Order Book

Medium
885.

PhonePe: Open Share.Market Positions From the Order Book

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

ColumnTypeValues
order_idint
user_idint
symbolvarchar
sideenumBUY, SELL
quantityint
statusenumexecuted, rejected, cancelled
placed_atdatetime

Primary key: order_id.

One row per order; quantity is the number of shares and is always positive.

Example

The rows must come back in the order shown.

TradeOrder

order_iduser_idsymbolsidequantitystatusplaced_at
17INFYBUY10executed2025-02-03 09:20:00
27INFYSELL4executed2025-02-05 10:10:00
37ITCBUY50executed2025-02-03 09:25:00
47ITCSELL50executed2025-02-10 14:00:00
58SBINBUY20rejected2025-02-04 11:00:00
68TCSBUY2executed2025-02-04 11:05:00
78TCSSELL1cancelled2025-02-06 12:00:00
89SBINBUY15executed2025-02-07 09:45:00

Output

user_idsymbolnet_quantity
7INFY6
8TCS2
9SBIN15
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
order_idint
user_idint
symbolvarchar
sideenum(BUY, SELL)
quantityint
statusenum(executed, rejected, cancelled)
placed_atdatetime

Primary key: order_id.

One row per order; quantity is the number of shares and is always positive.

Examples

Example 1

TradeOrder

order_iduser_idsymbolsidequantitystatusplaced_at
17INFYBUY10executed2025-02-03 09:20:00
27INFYSELL4executed2025-02-05 10:10:00
37ITCBUY50executed2025-02-03 09:25:00
47ITCSELL50executed2025-02-10 14:00:00
58SBINBUY20rejected2025-02-04 11:00:00
68TCSBUY2executed2025-02-04 11:05:00
78TCSSELL1cancelled2025-02-06 12:00:00
89SBINBUY15executed2025-02-07 09:45:00

Output

user_idsymbolnet_quantity
7INFY6
8TCS2
9SBIN15

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, symbol

Another 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.symbol

Another 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 →