Paytm: Digital Gold Customers Holding at Least One Gram
Customers can buy and sell digital gold in tiny amounts; from one gram upward they may ask for physical delivery as a coin.
- Difficulty: Easy
- Topics: Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #855
Problem statement
Customers can buy and sell digital gold in tiny amounts; from one gram upward they may ask for physical delivery as a coin. The gold team wants the customers who already qualify.
A customer's holding is the milligrams they bought minus the milligrams they sold. Return every customer whose holding is at least 1000 mg, with the columns user_id and holding_mg, ordered by holding_mg descending, then by user_id.
A practice scenario set at Paytm: the tables and data are invented, and this is not a question from, or endorsed by, Paytm.
Tables
Table: GoldTxn
| Column | Type |
|---|---|
| txn_id | int |
| user_id | int |
| txn_type | enum(buy, sell) |
| milligrams | int |
| txn_date | date |
Primary key: txn_id.
One row per gold purchase or sale; milligrams is always positive, and a customer never sells more than they hold.
Examples
Example 1
GoldTxn
| txn_id | user_id | txn_type | milligrams | txn_date |
|---|---|---|---|---|
| 1 | 1 | buy | 800 | 2025-01-03 |
| 2 | 1 | buy | 400 | 2025-01-17 |
| 3 | 1 | sell | 200 | 2025-02-01 |
| 4 | 2 | buy | 2500 | 2025-01-05 |
| 5 | 2 | sell | 500 | 2025-02-10 |
| 6 | 3 | buy | 900 | 2025-01-09 |
| 7 | 4 | buy | 1500 | 2025-01-12 |
| 8 | 4 | sell | 1500 | 2025-01-30 |
| 9 | 5 | buy | 1200 | 2025-02-14 |
Output
| user_id | holding_mg |
|---|---|
| 2 | 2000 |
| 5 | 1200 |
| 1 | 1000 |
How to solve Paytm: Digital Gold Customers Holding at Least One Gram
A holding is a signed sum: every purchase adds its milligrams and every sale takes them away. CASE WHEN txn_type = 'buy' THEN milligrams ELSE -milligrams END gives each row its sign, and SUM over the customer's rows is what they hold now. Because the condition is on the total and not on any one row, it goes in HAVING, after grouping; WHERE runs before the sum exists.
The boundary is inclusive — exactly one gram is enough for a coin — so the test is >= 1000. A customer who bought and later sold everything sums to 0 and is left out; one who bought 900 mg is just short.
The alternative adds purchases and sales in two separate conditional sums and subtracts them, filtering in an outer query instead of HAVING. Storing weights as whole milligrams keeps the arithmetic exact; summing grams as decimals could leave 0.9999999 where 1 was meant. Cost: one scan and one grouping.
Reference solution (MySQL)
SELECT user_id,
SUM(CASE WHEN txn_type = 'buy' THEN milligrams ELSE -milligrams END) AS holding_mg
FROM GoldTxn
GROUP BY user_id
HAVING holding_mg >= 1000
ORDER BY holding_mg DESC, user_idAnother way
SELECT user_id, holding_mg FROM (
SELECT user_id, SUM(IF(txn_type = 'buy', milligrams, 0)) - SUM(IF(txn_type = 'sell', milligrams, 0)) AS holding_mg
FROM GoldTxn GROUP BY user_id
) h WHERE holding_mg >= 1000 ORDER BY holding_mg DESC, user_id← Paytm: Tatkal Train Bookings Made in the First Five Minutes · Paytm: Runner-Up Movie by Seats Sold in Each City →