from thorchain.swaps

where to_asset = 'THOR.RUNE' and days >= CURRENT_DATE - 30),

raw2 as (select date(block_timestamp) as days, tx_id, to_asset

from thorchain.swaps

where from_asset = 'THOR.RUNE' and days >= CURRENT_DATE - 30),

final as (select a.days, a.tx_id, a.from_asset, b.to_asset, case

when b.to_asset is not null then 'Different_blockchain'

when b.to_asset is null then 'Single_swap'

else null end as blockchain

from raw a

left outer join raw2 b on a.tx_id = b.tx_id),

ffinal as (select days, tx_id, from_asset, to_asset, blockchain

else blockchain end as swap_chain

from final)

select * from ffinal