-- 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 @ https://flipsidecrypto.xyz/edit/queries/5ea0ffa7-d2ef-4c9d-91b3-bd81e8824551
-- 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 @ https://flipsidecrypto.xyz/edit/queries/a8cf8def-0080-42f6-be43-2cd91cb90347
-- 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 @ https://flipsidecrypto.xyz/edit/queries/dde6f060-5066-4a2a-9800-6cfa426a3605
-- 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 @ https://flipsidecrypto.xyz/edit/queries/c06a2184-53e7-4183-8242-22b8e21d9529
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-07-01' and block_timestamp::date < '2023-08-01'
order by block_timestamp
limit 15000
offset 255000;