select address,label

from solana.core.dim_labels

where label_type in ('defi','dex')

union all

select 'srmqPvymJeFKQ4zGQed1GFppgkRHL9kaELCbyksJtPX' as address, 'OpenBook' as label),

pricet as (

select block_timestamp::date as day,

swap_from_mint as token,

median (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 - 21

and succeeded = 'TRUE'

group by 1,2)

select t1.block_timestamp::Date as date,

initcap (label) as Program_Name,

count (distinct t1.tx_id) as Swaps_Count,

count (distinct swapper) as Swappers_Count,

sum (swap_from_amount*usdprice) as USD_Volume,

avg (swap_from_amount*usdprice) as Average_USD_Volume

from solana.core.fact_events t1 join DefiTable t2 on t1.program_id = t2.address

join solana.core.fact_swaps t4 on t1.tx_id = t4.tx_id

join pricet t3 on t1.block_timestamp::Date = t3.day and t4.swap_from_mint = t3.token

where t1.block_timestamp >= CURRENT_DATE - 21

and t1.succeeded = 'TRUE'

group by 1,2

order by 1