Convert a Purchase at Its Historical Exchange Rate
Reported by candidates from Agoda's online assessment. Pattern, common pitfall, and the honest play if you blank under the timer.
Agoda reported this SQL question in October 2023, and it looks harder than it is. One purchase row, a table of rate changes, and you need the rate that was live at purchase time. If you're taking the OA soon, this is an as-of lookup, not a join puzzle. The only real question is how you pick the latest rate without a brute-force mess. Know the pattern and it's a five-line query. If you blank on the syntax mid-assessment, StealthCoder runs invisibly on your screen and can hand you the query as a safety net.
The problem
A purchase has a price in an international currency and a timestamp. Exchange-rate changes are recorded with the timestamp at which each rate becomes effective. Return the amount payable in internal currency by using the latest exchange rate whose timestamp is less than or equal to the purchase timestamp. The original assessment fetched the purchase through a local REST endpoint and read the rate history from MySQL. In this practice runner, those two source inputs are supplied directly as tables. Tables purchase: product_name (Text), product_price (Integer), purchase_timestamp (Integer) currency: rate_timestamp PK (Integer), exchange_rate (Integer) Constraints The purchase table contains exactly one row. At least one currency row is effective at the purchase timestamp. Currency timestamps are unique.
Reported by candidates. Source: FastPrep
Pattern and pitfall
The trick is an as-of join. Filter the currency table to rows where rate_timestamp <= purchase_timestamp, then take the one with the largest rate_timestamp. Since the purchase table has exactly one row, a cross join with a filter is fine. Then order by rate_timestamp descending and limit 1, or use a subquery with MAX(rate_timestamp). Multiply product_price by exchange_rate for the payable amount. The common pitfall is using < instead of <=, which drops the exact-match case. Another is joining on equality and getting nothing back. A third is grabbing MAX(exchange_rate) instead of the rate at the max timestamp. Those are different things. The constraints guarantee at least one valid rate and unique timestamps, so you don't need NULL handling or tie-breaking. If you freeze on the subquery shape during the live OA, StealthCoder is the hedge that gives you a working query fast.
Drill it cold or hedge it with StealthCoder. Either way, don't walk into the OA hoping you remember the trick.
You can drill Convert a Purchase at Its Historical Exchange Rate cold, or you can hedge it. StealthCoder runs invisibly during screen share and surfaces a working solution in under 2 seconds. The proctor sees the IDE. They don't see what's behind it. Made for the candidate who got the OA invite this morning and has 72 hours, not six months.
Get StealthCoderRelated leaked OAs
You've seen the question.
Make sure you actually pass Agoda's OA.
Agoda reuses patterns across OAs. Made for the candidate who got the OA invite this morning and has 72 hours, not six months. Works on HackerRank, CodeSignal, CoderPad, and Karat.
Convert a Purchase at Its Historical Exchange Rate FAQ
How hard is the Agoda exchange rate SQL question really?+
Easy to medium. There's one purchase row and a small rate table, so the logic is short. The difficulty is knowing you need a less-than-or-equal match on the timestamp, then picking the latest one, instead of a normal equality join.
What's the trick to this query?+
Filter currency to rate_timestamp <= purchase_timestamp, then take the row with the greatest rate_timestamp. Do it with ORDER BY rate_timestamp DESC LIMIT 1 or a MAX subquery. Then multiply the price by that rate.
Do I need a window function here?+
No. Window functions like LEAD or ROW_NUMBER work, but they're overkill. A filtered subquery or ORDER BY with LIMIT 1 is simpler and less error-prone, especially since the purchase table has exactly one row.
What edge cases should I check?+
Check the exact-match case where rate_timestamp equals purchase_timestamp, so use <=. Don't use MAX(exchange_rate) by mistake. The constraints promise a valid rate exists and timestamps are unique, so you can skip empty-result and tie handling.
How do I prepare for this in 48 hours?+
Write the as-of lookup three ways: a MAX subquery, ORDER BY with LIMIT 1, and a join to the max timestamp. Practice multiplying the result into the final amount. That covers this question and most variants of it.