Paytm: Gold Buyers Whose Weighted Cost Beat the Simple Average

Hard
866.

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

ColumnType
rate_datedate
price_per_gramint

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

ColumnTypeValues
order_idint
user_idint
txn_typeenumbuy, sell
ordered_atdatetime
milligramsint
statusenumsuccess, failed

Primary key: order_id.

One row per digital gold order.

Example

The rows must come back in the order shown.

GoldRate

rate_dateprice_per_gram
2025-03-016400
2025-03-026500
2025-03-036300
2025-03-046600

GoldOrder

order_iduser_idtxn_typeordered_atmilligramsstatus
11buy2025-03-01 09:00:001000success
21buy2025-03-03 23:59:593000success
31buy2025-03-04 10:00:00500success
42buy2025-03-02 12:00:002000success
52buy2025-03-04 12:00:002000success
63buy2025-03-01 08:00:00100success
73buy2025-03-04 08:00:00900success
84buy2025-03-02 18:00:00500success
94buy2025-03-03 18:00:004000failed
104buy2025-03-03 19:00:001000success
2 more rows

Output

user_idbought_mgweighted_ratesimple_rate
1450063566433
4150063676400
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: 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

ColumnType
rate_datedate
price_per_gramint

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

ColumnType
order_idint
user_idint
txn_typeenum(buy, sell)
ordered_atdatetime
milligramsint
statusenum(success, failed)

Primary key: order_id.

One row per digital gold order.

Examples

Example 1

GoldRate

rate_dateprice_per_gram
2025-03-016400
2025-03-026500
2025-03-036300
2025-03-046600

GoldOrder

order_iduser_idtxn_typeordered_atmilligramsstatus
11buy2025-03-01 09:00:001000success
21buy2025-03-03 23:59:593000success
31buy2025-03-04 10:00:00500success
42buy2025-03-02 12:00:002000success
52buy2025-03-04 12:00:002000success
63buy2025-03-01 08:00:00100success
73buy2025-03-04 08:00:00900success
84buy2025-03-02 18:00:00500success
94buy2025-03-03 18:00:004000failed
104buy2025-03-03 19:00:001000success
114sell2025-03-04 09:00:001000success
125buy2025-03-03 07:00:002500success

Output

user_idbought_mgweighted_ratesimple_rate
1450063566433
4150063676400

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_id

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