SELECT

TX_ID,

BLOCK_TIMESTAMP,

DELEGATOR,

ACTION,

AMOUNT as STAKED_FLOW,

AMOUNT * FLOW_PRICE as STAKED_USD,

NODE_ID

FROM

flow.core.ez_staking_actions a JOIN (SELECT RECORDED_HOUR::date as date, AVG(CLOSE) as FLOW_PRICE FROM flow.core.fact_hourly_prices WHERE ID = 'flow' GROUP BY 1) b

ON a.BLOCK_TIMESTAMP::date = b.date

WHERE

TX_SUCCEEDED = 'true'

SELECT

date_trunc('{{interval}}', BLOCK_TIMESTAMP) as date,

CASE WHEN ACTION IN ('DelegatorTokensCommitted', 'TokensCommitted') then 'Stake'

WHEN ACTION IN ('DelegatorUnstakedTokensWithdrawn', 'UnstakedTokensWithdrawn') then 'Unstake'

ELSE 'NA' END AS STAKE_TYPE,

COUNT(distinct NODE_ID) as VALIDATOR_AMOUNT

FROM

RAW_FLOW_STAKE_TABLE

WHERE

STAKE_TYPE != 'NA'

GROUP BY 1, 2