KeyrockKryptonite -- User Count daily
Updated 2024-05-30
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
›
⌄
-- forked from Kryptonite -- User Count daily @ https://flipsidecrypto.xyz/edit/queries/787aa3c3-7748-4644-aa63-64c53a4c4743
WITH Staking_CTE AS (
SELECT
date_trunc('day', block_timestamp) AS day,
COUNT(DISTINCT tx_from) AS stakers_count
FROM
sei.core.fact_transactions
--WHERE msgs[8]:"type"='transfer' and msgs[9]:"type"='execute' and msgs[10]:"type"='wasm' and msgs[11]:"type"='coin_spent' and msgs[17]:"type"='delegate' and (msgs[19]:"type"='wasm' or msgs[33]:"type"='wasm')
WHERE msgs[10]:attributes[4]:"key"='bWludGVk'
GROUP BY
date_trunc('day', block_timestamp)
)
SELECT
day,
stakers_count,
SUM(stakers_count) OVER (ORDER BY day) AS cumulative_stakers_count
FROM
Staking_CTE
ORDER BY
day ASC;
--SELECT * FROM sei.core.fact_transactions WHERE msgs[11]:"type"='execute' and msgs[12]:"type"='wasm' and msgs[13]:"type"='coin_spent' and msgs[19]:"type"='delegate' limit 1
-- SELECT * FROM sei.core.fact_transactions WHERE msgs LIKE '%bWludGVk%' limit 2
--SELECT * FROM sei.core.fact_transactions WHERE msgs[30]:"type"='wasm-silohub/received' limit 1
--SELECT * FROM sei.core.fact_transactions WHERE msgs[0]:attributes[1]:"key"='YW1vdW50' limit 1
--SELECT * FROM sei.core.fact_transactions WHERE msgs[10]:attributes[4]:"key"='bWludGVk' limit 1
-- SELECT * from sei.core.fact_transactions where tx_id='BE17C2C13D265B8059D2EA9BC9F684C294F5435C979432F09359D207DB689381'
QueryRunArchived: QueryRun has been archived