WITH

epochs AS (

-- Complete list of epoch since genesis.

-- This is used to simulate the validator selection algorithm which is evaluated per epoch

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 AS epoch_num

-- , 0.9 * (max(total_supply) - min(total_supply)) / 1e24 AS total_block_rewards

FROM near.core.fact_blocks AS b

WHERE 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 (

-- Extracts staked balance logs of validators

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' )