WITH Stake_transactions AS (
SELECT
TXN_HASH AS Stake_txns
FROM flipside_prod_db.mdao_near.actions_events
WHERE ACTION_DATA:method_name::string='deposit_and_stake'
AND BLOCK_TIMESTAMP::DATE >= CURRENT_DATE - 90)
SELECT
DATE_TRUNC('DAY',BLOCK_TIMESTAMP) AS DAY,
COUNT(DISTINCT A.TXN_HASH) AS Number_of_staking_txns,
COUNT(DISTINCT A.TX_SIGNER) AS Active_stakers,
AVG(Active_stakers) OVER (ORDER BY DAY) Average_daily_active_stakers
FROM flipside_prod_db.mdao_near.transactions A INNER JOIN Stake_transactions B ON A.TXN_HASH = B.Stake_txns
GROUP BY DAY
ORDER BY DAY ASC