prices as

(SELECT date_trunc('day',hour) as day, avg(close) as pr, case

when ASSET_ID = 'ethereum' then 'ETH'

when ASSET_ID = 'usd-coin' then 'USDC'

when ASSET_ID = 'solana' then 'SOL'

end as Eclipse_cr

from crosschain.price.fact_prices_ohlc_hourly

where hour::date >= '2024-08-01' and

ASSET_ID in ('ethereum', 'solana', 'usd-coin')

and PROVIDER = 'coingecko'

GROUP by all),

buying_swaps as

(with side_sell as

(select case

when mint = 'BeRUj3h7BqkbdfFU7FBNYbodgf8GCHodzKvF9aVjNNfL' then 'SOL'

when mint = 'AKEWE7Bgh87GPp171b4cJPSSZfmZwQ3KaqYqXoKLNAEE' then 'USDC'

when mint = 'Eth1111111111111111111111111111111111111111' then 'ETH'

when mint = 'So11111111111111111111111111111111111111112' then 'ETH'

else mint

end as token_sell,

AMOUNT/pow(10,decimal) as volume_sell,

a.TX_ID as hash,

SIGNERS[0] as trader,

a.BLOCK_TIMESTAMP as time

from eclipse.core.fact_transfers a

join eclipse.core.fact_transactions b

on SIGNERS[0]= TX_FROM and a.TX_ID = b.TX_ID

where a.tx_to not in ('D4P9HJYPczLFHvxBgpLKooy7eWczci8pr4x9Zu7iYCVN','7nuPtH4c7BJUbfyGvRYQYBqZpPf81SHCJxPuMSYXtC6i')

),

side_buy as (select

'tETH' as token_buy,

AMOUNT/pow(10,decimal) as volume_buy,