How to calculate values based on different columns in BigQuery?

Question

I have tables like

order_id	date	price_local_currency	local_currency
123456	2022-04-10	12.3	EUR
123457	2022-04-10	131.2	USD

and

date	GBP	EUR	USD
2022-04-10	1.0	1.12	1.15
2022-04-10	1.0	1.11	1.16

So I need to join them on date and calculate price_pounds column. How can I calculate it using different columns? Of course there are a lot more than just 2 currencies, in fact I have all of them.

Answer 1

Consider below approach

with flatten_rates as (
  select date, arr[offset(0)] as local_currency, cast(arr[offset(1)] as float64) as exchange_rate
  from pound_rates t, 
  unnest(split(translate(to_json_string(t), '{}"', ''))) kv,
  unnest([struct(split(kv, ':') as arr)])
  where not arr[offset(0)] = 'date'
)
select o.*, 
  round(price_local_currency * exchange_rate, 2) as price_pounds 
from orders o
join flatten_rates 
using(date, local_currency)          

if to apply to sample/dummy data as in your question

with orders as (
  select 123456 order_id, '2022-04-10' date, 12.3 price_local_currency, 'EUR' local_currency union all
  select 123457, '2022-04-10', 131.2, 'USD'
), pound_rates as (
  select '2022-04-10' date, 1.0 GBP, 1.12 EUR, 1.15 USD union all
  select '2022-04-11', 1.0, 1.11, 1.16 
)         

the output is