Ticket Price by Latest Exchange Rate

Problem Given rates(rate_time, usd_to_inr) where the rate changes at arbitrary times, and tickets(ticket_id, sale_date, price_usd), write SQL returning each ticket's price in INR using the latest rate on or before its sale date.

Input / Output

• Input: rates and tickets tables as above.
• Output: (ticket_id, sale_date, price_inr).

Constraints

• Multiple rate changes per day possible; a correlated point-in-time lookup ("as-of join") is the crux.

Example

• Rates: (Jan 1, 82.0), (Jan 5, 83.5). Ticket sold Jan 4 → uses 82.0; sold Jan 5 → 83.5.

Expected approach

• As-of join. Portable form: correlated subquery — SELECT t.*, t.price_usd * (SELECT r.usd_to_inr FROM rates r WHERE r.rate_time <= t.sale_date ORDER BY r.rate_time DESC LIMIT 1). Window form: union/join rates to tickets, LAST_VALUE/LAG over time partitions. Postgres also allows LATERAL. Discuss index on rates(rate_time) and what to do when no prior rate exists.