prices as (

select

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

avg(swap_to_amount/swap_from_amount) as price

from solana.fact_swaps

where

swap_from_mint = 'So11111111111111111111111111111111111111112'

and 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

select

date "Date",

count(distinct tx_id) "TX Number",

count(distinct signers[0]) "Users",

sum((pre_balances[0] / power(10, 9)) - (post_balances[0] / power(10, 9))) "SOL Volume",

avg((pre_balances[0] / power(10, 9)) - (post_balances[0] / power(10, 9))) "AVG SOL Volume",

"SOL Volume" * price "USD Volume",

"AVG SOL Volume" * price "AVG USD Volume",

sum ("SOL Volume") over (order by "Date") as "Comulative SOL Volume",

sum ("USD Volume") over (order by "Date") as "Comulative USD Volume"

from solana.core.fact_staking_lp_actions a

join prices

on date = a.block_timestamp::date

where event_type in ('delegate')

and succeeded = 1

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

and program_id = 'Stake11111111111111111111111111111111111111'