prices as (

select

date_trunc('day', block_timestamp::date) as date,

swap_from_mint as mint,

avg(swap_to_amount/swap_from_amount) as price

from solana.fact_swaps

where swap_to_mint in (

'Es9vMFrzaCERmJfrF4H2FYD4KCoNkY11McCe8BenwNYB',

'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v'

)

and succeeded = true

and date >= '{{ date }}'

and swap_to_amount > 0

and swap_from_amount > 0

group by date, mint

),

txs as (

select

tx_id,

program_id,

iff(program_id = 'srmqPvymJeFKQ4zGQed1GFppgkRHL9kaELCbyksJtPX', 'openbook', label) as program

from solana.core.fact_events c

left join solana.core.dim_labels b

on address = program_id and label_type in ('dex', 'defi')

where block_timestamp::date >= '{{ date }}'

and succeeded = true and label is not null

date as "Date",

sum(amount * price) "Total Amount ($)",

avg(amount * price) "Average Amount ($)",

median(amount * price) "Median Amount ($)",

count(distinct a.tx_id) "TX Number",