tx_id,

affiliate_address,

from_address,

blockchain,

pool_name,

from_asset,

to_asset,

from_amount,

to_amount,

FROM_AMOUNT_USD,

from thorchain.defi.fact_swaps

where affiliate_address in ('vi', 'va', 'v0'))

,

price as (select hour::date as day,

'THOR.RUNE' as symbol,

avg(price) as avg_price

from thorchain.price.ez_prices_hourly

where symbol = 'RUNE'

group by 1,2

,

final as ( select block_timestamp,

tx_id,

from_address,

blockchain,

pool_name,

from_asset,

to_asset,

from_amount,

affiliate_address,

to_amount,

case when from_asset = 'THOR.RUNE' then from_amount*b.avg_price else FROM_AMOUNT_USD end as from_amt,

case when to_asset = 'THOR.RUNE' then to_amount*c.avg_price else to_AMOUNT_USD end as to_amt,

case when from_asset = 'THOR.RUNE' then from_amt else to_amt end as amt_1,