DAY | ASSET_NAME | AVG_SAVER_AGE_BY_POOL | AVG_SAVER_AGE | |
|---|---|---|---|---|
1 | 2025-05-05 00:00:00.000 | Stablecoin | 361.792381 | 500.4112883 |
2 | 2025-05-05 00:00:00.000 | LTC/LTC | 494.207765 | 500.4112883 |
3 | 2025-05-05 00:00:00.000 | GAIA/ATOM | 472.074184 | 500.4112883 |
4 | 2025-05-05 00:00:00.000 | DOGE/DOGE | 486.544406 | 500.4112883 |
5 | 2025-05-05 00:00:00.000 | ETH/ETH | 534.522684 | 500.4112883 |
6 | 2025-05-05 00:00:00.000 | BCH/BCH | 537.664723 | 500.4112883 |
7 | 2025-05-05 00:00:00.000 | AVAX/AVAX | 471.052083 | 500.4112883 |
8 | 2025-05-05 00:00:00.000 | BSC/BNB | 415.470395 | 500.4112883 |
9 | 2025-05-05 00:00:00.000 | BNB/BNB | 674.263158 | 500.4112883 |
10 | 2025-05-05 00:00:00.000 | BTC/BTC | 556.521104 | 500.4112883 |
11 | 2025-05-01 00:00:00.000 | ETH/ETH | 530.522684 | 496.4112883 |
12 | 2025-05-01 00:00:00.000 | Stablecoin | 357.792381 | 496.4112883 |
13 | 2025-05-01 00:00:00.000 | LTC/LTC | 490.207765 | 496.4112883 |
14 | 2025-05-01 00:00:00.000 | DOGE/DOGE | 482.544406 | 496.4112883 |
15 | 2025-05-01 00:00:00.000 | GAIA/ATOM | 468.074184 | 496.4112883 |
16 | 2025-05-01 00:00:00.000 | BSC/BNB | 411.470395 | 496.4112883 |
17 | 2025-05-01 00:00:00.000 | AVAX/AVAX | 467.052083 | 496.4112883 |
18 | 2025-05-01 00:00:00.000 | BTC/BTC | 552.521104 | 496.4112883 |
19 | 2025-05-01 00:00:00.000 | BCH/BCH | 533.664723 | 496.4112883 |
20 | 2025-05-01 00:00:00.000 | BNB/BNB | 670.263158 | 496.4112883 |
pietrektAvg Saver Age
Updated 2025-05-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 deposit_units AS (
SELECT block_timestamp, asset_tx_id as tx_id, asset_address as address, pool_name, stake_units, asset_e8 / pow(10, 8) as asset_amount, 1 as type
FROM thorchain.defi.fact_stake_events
WHERE pool_name LIKE '%/%'
),
withdraw_units AS (
SELECT block_timestamp, tx_id, from_address as address, pool_name, -stake_units as stake_units, -emit_asset_e8 / pow(10,8) as asset_amount, 0 as type
FROM thorchain.defi.fact_withdraw_events
WHERE MEMO like '%-:%' and MEMO like '%/%' and block_timestamp is not null
),
unioned_units AS (
SELECT block_timestamp, tx_id, address, pool_name, stake_units, asset_amount, type FROM deposit_units
UNION
SELECT block_timestamp, tx_id, address, pool_name, stake_units, asset_amount, type FROM withdraw_units
),
units_full AS (SELECT *,
(SELECT COUNT(*) FROM unioned_units as c WHERE c.type=1 AND c.address = a.address AND c.pool_name = a.pool_name AND c.block_timestamp <= a.block_timestamp) as index,
SUM(stake_units) OVER(PARTITION BY address, pool_name ORDER BY block_timestamp) AS user_units,
LEAD(block_timestamp, 1, CURRENT_TIMESTAMP) OVER(PARTITION BY address, pool_name ORDER BY block_timestamp) as next_timestamp
from unioned_units as a),
units_full_with_lastest_deposit AS(SELECT a.*, b.block_timestamp as latest_deposit_timestamp
FROM units_full AS a LEFT JOIN units_full AS b ON a.index = b.index AND b.type = 1 AND a.pool_name = b.pool_name AND a.address = b.address),
savers_depths AS (SELECT block_timestamp,
pool_name,
POWER(10,-8) * asset_e8 AS savers_depth
FROM thorchain.defi.fact_block_pool_depths
WHERE (pool_name LIKE '%/%')
QUALIFY (block_timestamp = MIN(block_timestamp) OVER(PARTITION BY time_slice(block_timestamp, 1, 'HOUR', 'START')))),
saver_depths_with_pool_units AS (SELECT *, (SELECT SUM(stake_units) FROM units_full as c WHERE c.block_timestamp <= b.block_timestamp AND c.pool_name = b.pool_name) as pool_units FROM savers_depths as b),
saver_units_and_depths AS (SELECT b.block_timestamp, LOWER(a.address) as address, b.pool_name, a.user_units, latest_deposit_timestamp
FROM units_full_with_lastest_deposit AS a INNER JOIN saver_depths_with_pool_units AS b ON b.block_timestamp >= a.block_timestamp AND b.block_timestamp < a.next_timestamp AND a.pool_name = b.pool_name AND a.user_units > 0 ),
Last run: 14 days ago
...
7099
440KB
12s