t2.token as Token_Symbol,

count (distinct t1.tx_id) as TX_Count,

sum (coalesce(swap_to_amount,0) * t2.USDPrice) as Total_Volume

from solana.core.fact_swaps t1 join

(

select t4.USDPrice,

t.*

from (

select t2.value:parsed:info:amount/1e8 as token_amount,

t2.value:parsed:info:mint as token_address,

t1.instruction:accounts[0] as from_address,

t3.address_name as token,

t1.*

from solana.core.fact_events t1, lateral flatten(input => inner_instruction:instructions) t2

join solana.core.dim_labels t3 on t2.value:parsed:info:mint = t3.address

where program_id = 'wormDTUJ6AWPNvk59vGQbDvGJmqbDTdgWgAqcLBCgUb'

and block_timestamp >= CURRENT_DATE - 7

and t2.value:parsed:type = 'burn'

) t join (select block_timestamp::date as day,

swap_from_mint as token,

avg (swap_to_amount/swap_from_amount) as USDPrice

from solana.fact_swaps

where swap_to_mint in ('EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v','Es9vMFrzaCERmJfrF4H2FYD4KCoNkY11McCe8BenwNYB') --USDC,USDT

and swap_to_amount > 0

and swap_from_amount > 0

and block_timestamp >= CURRENT_DATE - 7

and succeeded = 'TRUE'

group by 1,2) t4 on t.token_address = t4.token and t.block_timestamp::date = t4.day

) t2 on t1.swapper = t2.from_address and t1.block_timestamp between t2.block_timestamp - interval'2 days' and t1.block_timestamp and t2.block_timestamp >= CURRENT_DATE - 7 and t1.swap_to_mint = t2.token_address

group by 1,2