Pine AnalyticsTIA on Eclipsie copy
Updated 2024-11-07
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 tab1 as (
SELECT
date(block_timestamp) as date,
post.value:uiTokenAmount:amount as post,
pre.value:uiTokenAmount:amount as pre
--sum(
-- case when pre.value:uiTokenAmount:amount = 0 and not post.value:uiTokenAmount:amount = 0 then 1
-- when post.value:uiTokenAmount:amount = 0 and not pre.value:uiTokenAmount:amount = 0 then -1
-- else 0 end
--) as net_holders,
--sum(net_holders) over (ORDER by date) as total_holders
-- tx.*,
-- pre.value as pre_balance,
-- post.value as post_balance,
-- pre.index as position
FROM eclipse.core.fact_transactions tx,
LATERAL FLATTEN(input => PRE_TOKEN_BALANCES) pre,
LATERAL FLATTEN(input => POST_TOKEN_BALANCES) post
WHERE pre.index = post.index
AND pre.value:mint like '9RryNMhAVJpAwAGjCAMKbbTFwgjapqPkzpGMfTQhEjf8'
and SUCCEEDED
--LIMIT 1000
--GROUP BY 1
)
SELECT
date,
sum(
case when post = '0' then -1
when pre = '0' then 1
else 0 end
) as net_holders,
sum(net_holders) over (order by date) as total_holders
QueryRunArchived: QueryRun has been archived