DATE | NAKAMOTO_COEFFICIENT | |
|---|---|---|
1 | 2022-10-19 00:00:00.000 | 13 |
2 | 2022-10-20 00:00:00.000 | 13 |
3 | 2022-10-21 00:00:00.000 | 13 |
4 | 2022-10-22 00:00:00.000 | 13 |
5 | 2022-10-23 00:00:00.000 | 13 |
6 | 2022-10-24 00:00:00.000 | 13 |
7 | 2022-10-25 00:00:00.000 | 13 |
8 | 2022-10-26 00:00:00.000 | 13 |
9 | 2022-10-27 00:00:00.000 | 13 |
10 | 2022-10-28 00:00:00.000 | 13 |
11 | 2022-10-29 00:00:00.000 | 13 |
12 | 2022-10-30 00:00:00.000 | 13 |
13 | 2022-10-31 00:00:00.000 | 13 |
14 | 2022-11-01 00:00:00.000 | 13 |
15 | 2022-11-02 00:00:00.000 | 13 |
16 | 2022-11-03 00:00:00.000 | 13 |
17 | 2022-11-04 00:00:00.000 | 13 |
18 | 2022-11-05 00:00:00.000 | 13 |
19 | 2022-11-06 00:00:00.000 | 13 |
20 | 2022-11-07 00:00:00.000 | 13 |
messariaptos nakamoto coefficient (@marqu, @ph0rt0n)
Updated 2025-03-09
99
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
›
⌄
WITH stake_txs AS (
SELECT
date_trunc('day', block_timestamp) AS date,
address AS pool_address,
change_data:active:value::int / pow(10, 8) AS active_stake
FROM aptos.core.fact_changes
WHERE success
AND change_type = 'write_resource'
AND inner_change_type = '0x1::stake::StakePool'
AND block_timestamp > '2022-10-19'
QUALIFY row_number() OVER (PARTITION BY address, date ORDER BY version DESC, change_index DESC) = 1
),
dates AS (
SELECT DISTINCT
date_trunc('day', date_hour) AS date,
pool_address
FROM crosschain.core.dim_date_hours
CROSS JOIN (SELECT DISTINCT pool_address FROM stake_txs) pools
),
filled_dates AS (
SELECT
d.date,
st.pool_address,
COALESCE(st.active_stake, 0) AS active_stake
FROM dates d
LEFT JOIN stake_txs st ON d.pool_address = st.pool_address AND d.date = st.date
),
TotalStake AS (
SELECT
date,
SUM(active_stake) AS daily_total_stake
FROM filled_dates
GROUP BY date
Last run: 26 days ago
...
873
26KB
165s