-- forked from flow staking activity @ https://flipsidecrypto.xyz/edit/queries/2827b8df-2047-40ee-8841-0c820ff352a6

WITH

base AS (

SELECT

block_timestamp,

tx_id,

CASE

WHEN action IN ('TokensCommitted', 'DelegatorTokensCommitted') THEN 'Stake'

WHEN action IN ('UnstakedTokensWithdrawn', 'DelegatorUnstakedTokensWithdrawn') THEN 'Unstake'

END AS action,

delegator,

amount

FROM

flow.gov.ez_staking_actions

WHERE

tx_succeeded = TRUE

AND action NOT IN ('RewardTokensWithdrawn', 'DelegatorRewardTokensWithdrawn')

--DATE_TRUNC({{interval}},mindate) AS date,

--action as action,

--COUNT(DISTINCT tx_id) AS txs,

COUNT(DISTINCT delegator) AS users,

round(SUM(amount)) AS volume,

volume/users as avg_volume_staked_per_user

--SUM(txs) OVER(PARTITION BY action ORDER BY date) AS cum_txs,

--SUM(new_users) OVER(PARTITION BY action ORDER BY date) AS cum_users

--SUM(volume) OVER(PARTITION BY action ORDER BY date) AS cum_volume

base