Currency Conversions

In-Depth Discussions
9

Hey @ryanonymous :wave:

There’s two options here:

:one: Create a valid_to column in your exchange_rates model, and then use an inequality join in orders.
To create the valid_to column, you need to check the next record, which you can do by using the lead function.
Depending on your SQL dialect, your exchange rate model might look like:

select
  source_currency,
  target_currency,
  rate,
  date as valid_from, 

  -- use a window function to find the next record
  lead(date) over (
    partition by source_currency, target_currency
    order by date
  ) as next_record,
  
  -- coalesce this with today's date so your most recent record has a valid_to date
  coalesce(
    next_record,
    current_date::date
  )
from exchange_rates

Then in your orders model, use something like:

select
   ...
from orders

left join exchange_rates as usd_exchange_rates
    on usd_exchange_rates.target_currency = 'USD'
    and orders.currency = usd_exchange_rates.source_currency
    -- order happens on or after valid_from
    and orders.ordered_at >= usd_exchange_rates.valid_from
    -- order happens before valid_to
    and orders.ordered_at < usd_exchange_rates.valid_to

:two: Use an “all days” table to get your exchange_rates model to have one record per day.
We call this “date spining”, and you can see an article about it over here :point_right: Finding active days for a subscription/user/account (date spining)

2 Likes