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 = 'tether' then 'USDT'

when ASSET_ID = 'solana' then 'SOL'

when ASSET_ID = 'turbo-eth' then 'tETH'

end as Eclipse_tk

from crosschain.price.fact_prices_ohlc_hourly

where hour::date >= '2025-04-01' and

ASSET_ID in ('ethereum', 'solana', 'usd-coin','turbo-eth','tether')

and PROVIDER = 'coingecko'

GROUP by all),

settle as

(select

BLOCK_TIMESTAMP, case

when mint = 'BeRUj3h7BqkbdfFU7FBNYbodgf8GCHodzKvF9aVjNNfL' then 'SOL'

when mint = 'AKEWE7Bgh87GPp171b4cJPSSZfmZwQ3KaqYqXoKLNAEE' then 'USDC'

when mint = 'Eth1111111111111111111111111111111111111111' then 'ETH'

when mint = 'So11111111111111111111111111111111111111112' then 'ETH'

when mint = 'GU7NS9xCwgNPiAdJ69iusFrRfawjDDPjeMBovhV1d4kn' THEN 'tETH'

when mint = 'CEBP3CqAbW4zdZA57H2wfaSG1QNdzQ72GiQEbQXyW9Tm' then 'USDT'

else mint

end as token,

AMOUNT/pow(10,decimal) as volume,

volume*pr as volume_usd,

from eclipse.core.fact_transfers

join prices

on token = Eclipse_tk

and BLOCK_TIMESTAMP::date = day

where BLOCK_TIMESTAMP::date >= '2025-04-01'

and tx_id in

(select distinct tx_id