Paytm: Digital Gold Customers Holding at Least One Gram

Easy
855.

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

ColumnTypeValues
txn_idint
user_idint
txn_typeenumbuy, sell
milligramsint
txn_datedate

Primary key: txn_id.

One row per gold purchase or sale; milligrams is always positive, and a customer never sells more than they hold.

Example

The rows must come back in the order shown.

GoldTxn

txn_iduser_idtxn_typemilligramstxn_date
11buy8002025-01-03
21buy4002025-01-17
31sell2002025-02-01
42buy25002025-01-05
52sell5002025-02-10
63buy9002025-01-09
74buy15002025-01-12
84sell15002025-01-30
95buy12002025-02-14

Output

user_idholding_mg
22000
51200
11000
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.

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

ColumnType
txn_idint
user_idint
txn_typeenum(buy, sell)
milligramsint
txn_datedate

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_iduser_idtxn_typemilligramstxn_date
11buy8002025-01-03
21buy4002025-01-17
31sell2002025-02-01
42buy25002025-01-05
52sell5002025-02-10
63buy9002025-01-09
74buy15002025-01-12
84sell15002025-01-30
95buy12002025-02-14

Output

user_idholding_mg
22000
51200
11000

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_id

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