WITH stake_tx_raw AS
(
SELECT
BLOCK_TIMESTAMP::DATE AS Day,
AMOUNT / POW(10, 9) * CASE
when action like '%withdraw%' then -1.0
when action like '%deposit%' then 1.0
when action = 'order_unstake' then 0.0
when action = 'claim' then -1.0
END AS amount
FROM solana.core.fact_stake_pool_actions
WHERE SUCCEEDED = 'TRUE'
AND ACTION is not NULL
)
SELECT
Day,
SUM(amount) AS DailyAmount
FROM stake_tx_raw
GROUP BY Day
ORDER BY Day ASC