Airbnb: converting on a day with no published rate

Constraints

1. 1Convert each booking with the most recent rate published on or before booked_on for that currency.
2. 27 and 8 March 2026 are a weekend with no published rates. Five of the twelve bookings fall on those days and all five must still be converted.
3. 3Every booking counts. bookings is 4 for Lisbon, 3 for Tokyo, 3 for London and 2 for Austin.
4. 4Do not use the latest rate overall — a booking on 2 March must not be priced at the 10 March rate.
5. 5revenue_usd is the total converted amount, rounded to 2 decimal places.
6. 6Order by revenue_usd descending, then city.

Worked example

Booking 2 is 250 EUR on 2026-03-07. There is no EUR rate for that date, so the correct rate is the most recent one before it — 1.07, published on 6 March — giving 267.50 USD. An equi-join on rate_date silently drops that booking, along with the four others made that weekend. Lisbon then reports 2 bookings and 648.80 instead of 4 and 1248.00, and the total understates revenue by roughly a third. Nothing errors; the report is simply smaller than the truth.

What this tests

The as-of lookup against a sparse dimension: when the reference table has gaps, matching on equality drops facts, and the correct join is 'the latest row at or before this date'.