dim_epochs AS (

SELECT epoch_id

, min(block_id) AS min_block_id

, max(block_id) AS max_block_id

, count(*) AS blocks

, count(distinct block_author) AS block_producers

, min(block_timestamp) AS start_time

, max(block_timestamp) AS end_time

, max(total_supply) / 1e24 AS total_near_supply

, row_number() OVER (order by min_block_id asc) - 1 + 900 AS epoch_num

FROM near.core.fact_blocks AS b

WHERE block_id >= (SELECT min(block_id) FROM near.core.fact_blocks WHERE epoch_id = '4ZhPXTpkB6njvkDFgqquu36S4TPDNU5wwgom86vVBwJn') -- epoch #900

AND epoch_id != (SELECT epoch_id FROM near.core.fact_blocks QUALIFY row_number() OVER (order by block_timestamp desc) = 1) -- exclude current epoch

GROUP BY 1

),

staking_actions AS (

SELECT r.tx_hash

, r.block_timestamp

, r.receiver_id AS validator_address

, replace(split(l.value::string, ': Contract received total')[0], 'Epoch ', '')::integer AS epoch_num

, split(split(l.value::string, 'New total staked balance is ')[1], '. Total number of shares')[0]::bigint / 1e24 AS staked_balance

FROM near.core.fact_receipts AS r

, lateral flatten( input => r.logs ) AS l

WHERE ( right(receiver_id, 12) = '.poolv1.near' OR right(receiver_id, 10) = '.pool.near' )

AND r.tx_hash IN ( SELECT tx_hash

FROM near.core.fact_actions_events_function_call

WHERE method_name IN ('ping','stake','unstake','stake_all','unstake_all','deposit_and_stake') )

AND left(l.value::string, 6) = 'Epoch '