BigQuery, NULL values when joining transactions and currency exchange tables

I need to convert transactions imports over the last 3 years present in a table to €. To do this, I took a separate table with conversion values. So I need to join this two tables, attach the right conversion rate to the first table. The rates table has two fields (valid_from and valid_to) that contain the rates for the last 3 years. So I must attach the rate referring to the right currency (for €, we have a single rate = 1) at the right moment in time. Here the query I built:

select * 
from(SELECT date_trx, view_currency FROM transactions_table) transactions
left join
    (SELECT Valid_from, Exchange_Rate, Exchange_Rate_Type, From_currency, To_currency, 
        coalesce(LEAD(Valid_from) OVER ( ORDER BY Valid_from ASC), cast('3001-12-31' as DATE)) valid_to
     FROM exchange_table where To_currency='EUR' and Exchange_Rate_Type = 'EURX') rates
on transactions.view_currency = rates.From_currency
and transactions.date_trx >= rates.Valid_from 
and transactions.date_trx < rates.valid_to

I created valid_to manually simply putting the valid_from values of one period to the valid_to of the previous one. When the rate is still valid now I put a 3001-12-31.

The problem with this query is that it returns NULL values for all the rates fields. I checked the two separate tables and they are fine. The funny thing is that if I filter the rates table for only one currency it works fine (no NULL values for transactions made with that currency), otherwise not. If I try to filter on two or more currencies, it returns NULL values for all transactions as well.

Answer

You need the currency in the lead():

select * 
from(SELECT date_trx, view_currency
     FROM transactions_table
    ) transactions left join
    (SELECT Valid_from, Exchange_Rate, Exchange_Rate_Type, From_currency, To_currency, 
           LEAD(Valid_from, 1, date('3001-12-31')) OVER (partition by currency ORDER BY Valid_from ASC), cast('3001-12-31' as DATE) as valid_to
     FROM exchange_table 
    ) rates
    on transactions.view_currency = rates.From_currency and
       transactions.date_trx >= rates.Valid_from and
       transactions.date_trx < rates.valid_to;

Note that I also simplified the valid_to logic using the three argument form of lead().