select block_timestamp, instruction:parsed:info:lamports/pow(10,9) as sol_amount from solana.core.fact_events

where block_timestamp::date >= date('2022-01-01' ) and succeeded=TRUE

and sol_amount is not null),-- and event_type='transfer'),

stake_txs as (

select tx_id from solana.core.fact_events

where block_timestamp::date >= date('2022-01-01' ) and succeeded=TRUE

and event_type='delegate'),

all_sol_stake as(

select block_timestamp, instruction:parsed:info:lamports/pow(10,9) as sol_amount from solana.core.fact_events a

join stake_txs b

on a.tx_id=b.tx_id

where block_timestamp::date >= date('2022-01-01' ) and succeeded=TRUE

and sol_amount is not null),-- and event_type='createAccountWithSeed'),

daily_sol_staked as (

select block_timestamp::date as date, sum(sol_amount) as daily_staked,

avg(sol_amount) as avg_sol_staked_per_tx from all_sol_stake

group by date),

daily_sol_transacted as (

select block_timestamp::date as date, sum(sol_amount) as daily_transacted from all_sol_tarnsfers

group by date)

select a.date, daily_staked, daily_transacted,

100*daily_staked/(daily_transacted) as percent_staked,

avg_sol_staked_per_tx

from daily_sol_staked a

join daily_sol_transacted b

on a.date=b.date