tx_id,

from_address,

blockchain,

pool_name,

from_asset,

to_asset,

from_amount,

to_amount,

FROM_AMOUNT_USD,

from thorchain.defi.fact_swaps)

,

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,

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,

case when to_asset = 'THOR.RUNE' then to_amt else from_amt end as amt_2,

case when amt_1 is null then amt_2 else amt_1 end as amount_usd

from swap a left outer join price b on date_trunc('day',block_timestamp) = b.day::date and a.from_asset = b.symbol