Drill 7 of 8 in Fintech questions
0

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…