select * from (

select

block_timestamp,

block_id,

tx_id,

to_e8/pow(10,8) as rune_collected,

split(memo,':')[4]::string as affliate_address,

split(memo,':')[5]::string as affliate_fee_percent,

rank() over (partition by tx_id order by to_e8) as rank

from thorchain.swap_events

where array_size(split(memo,':')) > 4

and to_asset = 'THOR.RUNE'

)

where rank = 1

),

thor_prices as (

select distinct * from (

select

block_id,

block_timestamp,

rune_usd

from thorchain.prices

)

),

fee_tx as (

select

f.block_timestamp,

f.block_id,

tx_id,

rune_collected,

affliate_address,

affliate_fee_percent,

rune_usd ,

rune_collected*rune_usd as fee_collected_usd

from fee_collected f join thor_prices p on p.block_id=f.block_id