from ethereum.core.ez_dex_swaps

where platform in ('balancer','uniswap-v2')

and date >= '2022-10-01'

and amount_in_usd < 10000000)

,

optimism as ( select date(block_timestamp) as date, platform, tx_hash, origin_from_address, amount_in_usd

from optimism.velodrome.ez_swaps

where date >= '2022-10-01'

and amount_in_usd < 10000000

select date(block_timestamp) as date, platform, tx_hash, origin_from_address, amount_in_usd

from optimism.sushi.ez_swaps

where date >= '2022-10-01'

and amount_in_usd < 10000000)

,

raydium as ( select date(block_timestamp) as swap_date, tx_id, swapper, swap_from_amount,SWAP_FROM_MINT

from solana.core.fact_swaps

where (SWAP_PROGRAM ilike '%raydium%' or SWAP_PROGRAM ilike '%jupiter%' ) and block_timestamp::date >= '2022-10-01')

,

sol_price as ( select date(block_timestamp) as p_date, a.swap_from_mint , (sum(SWAP_TO_AMOUNT)/sum(a.SWAP_FROM_AMOUNT)) as price

from solana.core.fact_swaps a join raydium b on a.SWAP_FROM_MINT = b.SWAP_FROM_MINT

where a.SWAP_FROM_MINT = b.SWAP_FROM_MINT

and SWAP_TO_MINT = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v'

and p_date >= '2022-10-01'

and SWAP_TO_AMOUNT > 0 and a.SWAP_FROM_AMOUNT > 0

group by 1,2 )

,

raydium_usd as ( select swap_date, tx_id, swapper, swap_from_amount*price as amount_usd

from raydium a left outer join sol_price b on swap_date = b.p_date

where a.swap_from_mint = b.SWAP_FROM_MINT)

,

final as ( select date, 'Ethereum' as chain, count(DISTINCT(origin_from_address)) as total_user, count(DISTINCT(tx_hash)) as total_tx,

sum(amount_in_usd) as volume, avg(amount_in_usd) as avg_usd, sum(total_user) over (order by date asc) as cum_user

from ethereum

group by 1,2