Interview Question Bank · LATERAL · latest row on or before a date
7. Convert at the rate in effect
HardFintechQuestion #106
Concept: LATERAL · latest row on or before a dateshow
Rate tables only have rows on the days a rate changes, so most dates need 'the latest row on or before this date'. A LATERAL subquery runs once per outer row and can use its columns: WHERE r.day <= outer.day ORDER BY r.day DESC LIMIT 1 picks exactly that row. An equality join misses the dates between changes, and a plain <= join returns every earlier rate.
Example (different data):
SELECT o.order_id, o.ordered_on, t.vat_rate FROM orders o CROSS JOIN LATERAL ( SELECT vat_rate FROM vat_rates v WHERE v.country = o.country AND v.valid_from <= o.ordered_on ORDER BY v.valid_from DESC LIMIT 1 ) t;
Your task
Quillpay settles cross-border payments in US dollars. Finance publishes a rate per currency on business days only, and each rate stays in effect until the next one is published.
For every payment in fin_intl_payments, use the fin_fx_rates row for its currency with the latest effective_on on or before paid_on. Return payment_id, currency, amount, rate_date (that rate's effective_on) and amount_usd (amount * usd_per_unit, ROUND(..., 2)). Sort by payment_id.
Starting Postgres in your browser…
Checking your access…