from thorchain.swaps

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

raw2 as (select tx_id, to_asset

from thorchain.swaps

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

combine as (select a.tx_id, a.from_asset, a.to_asset as rune, b.to_asset, a.to_amount_usd

from raw a

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

swaps as (select from_asset , rune, to_asset as ass, case

when ass is null then 'RUNE'

else ass end as to_asset,

count(distinct tx_id) as counts, sum(to_amount_usd)

from combine

group by 1,2,3),

raw3 as (select tx_id, to_asset, to_amount_usd

from thorchain.swaps

where from_asset = 'THOR.RUNE' and block_timestamp >= CURRENT_DATE - 30 and tx_id not in (select tx_id from combine)),

from_thor as (select null as from_asset, 'THOR.RUNE' as rune, to_asset as ass, to_asset, count(distinct tx_id) as counts, sum(to_amount_usd)

from raw3

group by 1,2,3)

select * from swaps

select * from from_thor