date_trunc('day', block_timestamp) as date,

count(distinct tx_id) as no_of_swaps,

-- a.swap_from_mint as from_token_address,

b1.address_name as from_token_address_name,

sum(swap_from_amount) as sol_volume,

-- a.swap_to_mint as to_token_address,

b.address_name as to_token_address_name,

sum(swap_to_amount) as usdc_volume

from solana.defi.fact_swaps a

left join solana.core.dim_labels b -- Identifies from_token_address as Wrapped Sol

on a.swap_to_mint = b.address -- on from_token_address_name #check results

left join solana.core.dim_labels b1 -- -- Identifies to_token_address as USDC

on a.swap_from_mint = b1.address -- on to_token_address_name #check results

where swap_from_mint = 'So11111111111111111111111111111111111111112' -- Wrapped Sol

and swap_to_mint = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v' -- USDC

and swap_program ilike 'Orca%' -- on Orca

and succeeded = 'True' -- Successful swaps

and block_timestamp >= current_date - 30

group by from_token_address_name, to_token_address_name, date

order by date desc;