Flipside TeamSeat Price & Active Validators Over Time
Updated 2024-09-26
999
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
-- forked from 2023-07-30 01:32 PM @ https://flipsidecrypto.xyz/edit/queries/c7b77393-f275-4238-a302-ad93d455299a
-- Forked & Credit: RMAS | https://flipsidecrypto.xyz/rmas/q/fYS9OfHnfp51/dash-daily-seat-price-since-2022
WITH 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 AS epoch_num
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 (
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 '
QUALIFY row_number() OVER (partition by epoch_num, validator_address order by block_timestamp desc) = 1),
validator_proposals AS (
WITH parsed_proposals AS (
SELECT b.block_id,
QueryRunArchived: QueryRun has been archived