date_trunc('day',a.block_timestamp) as date,

count(DISTINCT a.tx_id) as n_swaps,

COUNT(DISTINCT a.swapper) as n_swappers,

avg(a.swap_from_amount*b.price) as avg_amnt_usd,

sum(a.swap_from_amount*b.price) as amnt_usd

FROM solana.core.fact_swaps a

LEFT JOIN (

SELECT date_trunc('day', block_timestamp) as dates,

swap_from_mint,

median(swap_to_amount/swap_from_amount) as price

FROM solana.core.fact_swaps

WHERE (swap_to_mint = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v' or swap_to_mint = 'Es9vMFrzaCERmJfrF4H2FYD4KCoNkY11McCe8BenwNYB'

or swap_to_mint = 'BQcdHdAQW1hczDbBi9hiegXAR7A98Q9jx3X3iBBBDiq4')

and succeeded = 'TRUE'

AND swap_from_amount is not NULL

AND swap_from_amount > 10

AND dates between CURRENT_DATE-366 AND CURRENT_DATE-1

GROUP BY dates,swap_from_mint

)b

ON date_trunc('day',a.block_timestamp)=b.dates AND a.swap_from_mint = b.swap_from_mint

WHERE succeeded = 'TRUE'

AND date between CURRENT_DATE-366 AND CURRENT_DATE-1

GROUP BY date

SELECT 'ethereum' as chain,

date_trunc('day',block_timestamp) as date,

count(DISTINCT tx_hash) as n_swaps,

COUNT(DISTINCT origin_from_address) as n_swappers,

avg(CASE WHEN amount_in_usd> 10*amount_out_usd THEN amount_out_usd ELSE amount_in_usd END) as avg_amnt_usd,

sum(CASE WHEN amount_in_usd> 10*amount_out_usd THEN amount_out_usd ELSE amount_in_usd END) as amnt_usd

FROM ethereum.core.ez_dex_swaps

WHERE date between CURRENT_DATE-366 AND CURRENT_DATE-1

AND amount_in_usd < pow(10,10)

GROUP BY date