fact_stake as (

select

block_timestamp as f_block_timestamp,

tx_id as f_tx_id,

index as f_index,

event_type as f_event_type,

instruction

from

solana.core.fact_staking_lp_actions

),

-- select * from solana.core.fact_transactions where tx_id = '121TFJ25jcrdgbwuAja8PnD5z64m79JTNyjDZBJkebQyVhh3SvRtzKVtRE5mhwjbxMwjbCYHTXRHyCmrXuPbRqxr' and block_timestamp::date >= '2022-12-28' and block_timestamp::date <= '2022-12-31'

-- select * from solana.core.ez_staking_lp_actions where

-- tx_id='4s44JmB7sL93epUU5eMUT5uJxw7LVytKFet8f1hr3eTPqoSu7F184h2L4vc7kX1WNbRTrV3LdKzvmdaXG7ovcTDg'

-- stake_account ='6nGxYRuU3N8DQvRnq8CfeU2KaSnzwNLusFgUu1JY56nK'

-- tx_id='26HpZQuXSEJPoq9rvozgwmJ82g2h5GvByEocnrQ7pULeZ67g3rFfrdJGMxx7AFXXtdJoQDLsLhohXqd8x5LVPPdu'

base as (

select

*,

-- case when idx_minor_raw in (1,2) then

-- case when

-- fact_tx.inner_instructions:index = idx_major

-- case when array_size(fact_tx.inner_instructions:index) >= 10 ...

-- else 1

signers[0] as fee_payer,

post_tx_staked_balance / pow(10, 9) as net_stake,

split_part(index::float, '.', 1)::int as idx_major,

case

when contains(index::float::string, '.') then split_part(index::float, '.', 2)::int

else 0

end as idx_minor,

row_number() over (

partition by

stake_account