Paytm: Gold Buyers Whose Weighted Cost Beat the Simple Average
Digital gold is bought at the day's published rate. The gold team wants to show customers who bought more on cheap days that their gram-for-gram cost was…
- Difficulty: Hard
- Topics: Joins, Aggregation, Dates
- Dialect: MySQL
- Problem: #866
Problem statement
Digital gold is bought at the day's published rate. The gold team wants to show customers who bought more on cheap days that their gram-for-gram cost was better than the plain average of the rates they saw.
Use only successful buy orders; each executes at the GoldRate of the day of its ordered_at. A customer's weighted rate is the average rate weighted by milligrams (Σ rate × mg ÷ Σ mg); their simple rate is the plain average of the rate over their orders, each order counting once. Return customers whose weighted rate is strictly lower than their simple rate, with user_id, bought_mg (milligrams bought), weighted_rate and simple_rate, both rates rounded to whole rupees. Compare the exact rates, not the rounded ones. Return the rows ordered 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: GoldRate
| Column | Type |
|---|---|
| rate_date | date |
| price_per_gram | int |
Primary key: rate_date.
The buy rate published for each day, in rupees per gram. Every day an order was placed has a rate.
Table: GoldOrder
| Column | Type |
|---|---|
| order_id | int |
| user_id | int |
| txn_type | enum(buy, sell) |
| ordered_at | datetime |
| milligrams | int |
| status | enum(success, failed) |
Primary key: order_id.
One row per digital gold order.
Examples
Example 1
GoldRate
| rate_date | price_per_gram |
|---|---|
| 2025-03-01 | 6400 |
| 2025-03-02 | 6500 |
| 2025-03-03 | 6300 |
| 2025-03-04 | 6600 |
GoldOrder
| order_id | user_id | txn_type | ordered_at | milligrams | status |
|---|---|---|---|---|---|
| 1 | 1 | buy | 2025-03-01 09:00:00 | 1000 | success |
| 2 | 1 | buy | 2025-03-03 23:59:59 | 3000 | success |
| 3 | 1 | buy | 2025-03-04 10:00:00 | 500 | success |
| 4 | 2 | buy | 2025-03-02 12:00:00 | 2000 | success |
| 5 | 2 | buy | 2025-03-04 12:00:00 | 2000 | success |
| 6 | 3 | buy | 2025-03-01 08:00:00 | 100 | success |
| 7 | 3 | buy | 2025-03-04 08:00:00 | 900 | success |
| 8 | 4 | buy | 2025-03-02 18:00:00 | 500 | success |
| 9 | 4 | buy | 2025-03-03 18:00:00 | 4000 | failed |
| 10 | 4 | buy | 2025-03-03 19:00:00 | 1000 | success |
| 11 | 4 | sell | 2025-03-04 09:00:00 | 1000 | success |
| 12 | 5 | buy | 2025-03-03 07:00:00 | 2500 | success |
Output
| user_id | bought_mg | weighted_rate | simple_rate |
|---|---|---|---|
| 1 | 4500 | 6356 | 6433 |
| 4 | 1500 | 6367 | 6400 |
How to solve Paytm: Gold Buyers Whose Weighted Cost Beat the Simple Average
Each order's price is the rate of its day, so the first step is a join on the order's calendar day — CAST(ordered_at AS DATE) = rate_date — which puts customer 1's order at 23:59:59 on 3 March at that day's rate. Sells and failed orders are filtered out first; they did not buy any gold.
The weighted average weighs every rupee rate by the milligrams bought at it: SUM(rate × mg) / SUM(mg). Customer 1 bought 3,000 mg at 6,300 and only 1,500 mg at higher rates, so their weighted rate is 6,355.56, while the simple average of the three rates they saw, AVG(rate), is 6,433.33 — they beat it. When every order has the same size the two are equal (customer 2), and when the big order fell on an expensive day the weighted rate is higher (customer 3); neither is reported.
The comparison must use the exact values, so the reference query cross-multiplies: weighted < simple is Σ(rate·mg) / Σmg < Σrate / n, and with positive denominators that is Σ(rate·mg) × n < Σrate × Σmg — all integers, no rounding involved. Only the displayed columns are rounded. The alternative computes both averages in a derived table and compares them directly; it agrees because equal fractions divide to equal values. One join and one grouping.
Reference solution (MySQL)
SELECT o.user_id,
SUM(o.milligrams) AS bought_mg,
ROUND(SUM(r.price_per_gram * o.milligrams) / SUM(o.milligrams)) AS weighted_rate,
ROUND(AVG(r.price_per_gram)) AS simple_rate
FROM GoldOrder o
JOIN GoldRate r ON r.rate_date = CAST(o.ordered_at AS DATE)
WHERE o.txn_type = 'buy' AND o.status = 'success'
GROUP BY o.user_id
HAVING SUM(r.price_per_gram * o.milligrams) * COUNT(*) < SUM(r.price_per_gram) * SUM(o.milligrams)
ORDER BY o.user_idAnother way
SELECT user_id, bought_mg, ROUND(w) AS weighted_rate, ROUND(s) AS simple_rate
FROM (
SELECT o.user_id, SUM(o.milligrams) AS bought_mg,
SUM(r.price_per_gram * o.milligrams) / SUM(o.milligrams) AS w,
SUM(r.price_per_gram) / COUNT(o.order_id) AS s
FROM GoldOrder o
JOIN GoldRate r ON r.rate_date = LEFT(o.ordered_at, 10)
WHERE o.txn_type = 'buy' AND o.status = 'success'
GROUP BY o.user_id
) t
WHERE w < s
ORDER BY user_id← Paytm: Merchant Days When QR Collections Fell by More Than Half · Paytm: FASTag Reads Charged Off the Plaza's Axle-Band Rate →