KeyrockTortuga STAPT - Supply copy
Updated 2024-10-25
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 calendar_dates AS (
SELECT DATE_TRUNC('day', MIN(day)) AS min_date, DATE_TRUNC('day', MAX(day)) AS max_date
FROM (
SELECT DATE_TRUNC('day', BLOCK_TIMESTAMP) AS day
FROM aptos.core.fact_events
WHERE ACCOUNT_ADDRESS = '0x84d7aeef42d38a5ffc3ccef853e1b82e4958659d16a7de736a29c55fbbeb0114'
)
),
subquery AS (
SELECT
date_trunc('day', BLOCK_TIMESTAMP) AS day,
SUM(CASE WHEN EVENT_TYPE = '0x1::coin::DepositEvent' THEN EVENT_DATA['amount'] / 10e8 ELSE 0 END) AS mint_stAPT,
SUM(CASE WHEN EVENT_TYPE = '0x8f396e4246b2ba87b51c0739ef5ea4f26515a98375308c31ac2ec1e42142a57f::stake_router::IncreaseStakeInValidatorEvent' THEN EVENT_DATA['amount'] / 10e8 ELSE 0 END) AS increase_stAPT,
SUM(CASE WHEN EVENT_TYPE = '0x8f396e4246b2ba87b51c0739ef5ea4f26515a98375308c31ac2ec1e42142a57f::validator_router::WithdrawToReserveEvent' THEN EVENT_DATA['amount'] / 10e8 ELSE 0 END) AS burn_stAPT
FROM
aptos.core.fact_events
WHERE
ACCOUNT_ADDRESS = '0x84d7aeef42d38a5ffc3ccef853e1b82e4958659d16a7de736a29c55fbbeb0114'
GROUP BY date_trunc('day', BLOCK_TIMESTAMP)
)
SELECT
day,
COALESCE(s.mint_stAPT, 0) AS mint_stAPT,
COALESCE(s.burn_stAPT, 0) AS burn_stAPT,
COALESCE(s.increase_stAPT, 0) AS increase_stAPT,
SUM(COALESCE(s.mint_stAPT, 0) + COALESCE(s.increase_stAPT, 0) - COALESCE(s.burn_stAPT, 0)) OVER (ORDER BY day) AS supply_stAPT
FROM
calendar_dates c
LEFT JOIN
subquery s ON c.min_date <= s.day AND s.day <= c.max_date
WHERE
c.min_date <= (SELECT min_date FROM calendar_dates)
AND c.max_date >= (SELECT max_date FROM calendar_dates)
QueryRunArchived: QueryRun has been archived