sum(swap_to_amount)/sum(swap_from_amount) as avg_dust_price

from solana.core.fact_swaps

where

swap_from_mint in ('DUSTawucrTsGU8hcqRdHDCbuYhCPADMLM2VcCb8VnFnQ')

and swap_to_mint in ('7kbnvuGBxxj8AG9qp8Scn56muWGaRaFqxg1FsRp3PaFT','Ea5SjE2Y6yvCeW5dYTn7PYMuW5ikXkvbGdcmSnXeaLjS','Es9vMFrzaCERmJfrF4H2FYD4KCoNkY11McCe8BenwNYB', 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v', 'USDH1SM1ojwWUga67PGrgFWUHibbjqMvuMaDkRJTgkX')

and succeeded = 'TRUE'

and swap_from_amount > 0

and swap_to_amount > 0

group by 1),

s_ as ( -- swaps

SELECT

count(tx_id) as swap_count,

sum(case when swap_to_mint = 'DUSTawucrTsGU8hcqRdHDCbuYhCPADMLM2VcCb8VnFnQ' then swap_to_amount

else swap_from_amount end) as dust_volume,

sum(case when swap_to_mint = 'DUSTawucrTsGU8hcqRdHDCbuYhCPADMLM2VcCb8VnFnQ' then swap_to_amount* avg_dust_price

else swap_from_amount*avg_dust_price end) as usd_volume,

count(distinct swapper) as swappers,

dust_volume/count(distinct block_timestamp::date) as daily_dust,

usd_volume/count(distinct block_timestamp::date) as dailly_usd,

swappers/count(distinct block_timestamp::date) as daily_swappers

FROM solana.core.fact_swaps s join dust_price p on block_timestamp::date = day

WHERE block_timestamp::date < CURRENT_DATE

and succeeded = 'TRUE'

and (swap_to_mint = 'DUSTawucrTsGU8hcqRdHDCbuYhCPADMLM2VcCb8VnFnQ'

or swap_from_mint = 'DUSTawucrTsGU8hcqRdHDCbuYhCPADMLM2VcCb8VnFnQ')

select * from s_