-- forked from part2 copy copy copy copy copy copy copy copy copy copy copy copy copy copy copy copy copy copy copy copy copy copy copy copy copy copy copy copy copy @ https://flipsidecrypto.xyz/edit/queries/1d4b9d73-e70b-47cd-b609-38b752e6ca59
-- forked from part2 copy copy copy copy copy copy copy copy copy copy copy copy copy copy copy copy copy copy copy copy copy copy copy copy copy copy copy copy @ https://flipsidecrypto.xyz/edit/queries/088946b4-95ea-451c-a84c-c144ed75fe32
with t as (
select
tx_id,
block_timestamp,
nvl(signers[1], signers[0]) as user,
iff(pre.value:owner = 'FK1VcVh5PqEUm8U3aEz69DRSNSCjD6cMjzGx3KKbiE8', 'Fee', 'Subscribe') as type,
(post.value:uiTokenAmount:amount - pre.value:uiTokenAmount:amount)/1e6 as amount_acs
from solana.core.fact_transactions
join lateral flatten (input => pre_token_balances) pre
join lateral flatten (input => post_token_balances) post
where log_messages::string ilike '%6HW8dXjtiTGkD4jzXs7igdFmZExPpmwUrRN5195xGup%'
and log_messages::string ilike '%Program log: Instruction: Stake%'
and succeeded = 1
and pre.value:owner != user
and pre.value:owner = post.value:owner
)
select * from t
where block_timestamp::date >= '2023-08-01' and block_timestamp::date < '2023-09-01'
order by block_timestamp
limit 15000
offset 30000;