KeyrockAstroport stSEI/SEI Liquidity -- new -- all data aggregated
Updated 2024-03-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
›
⌄
------------- lp'ing -------------
WITH liquidity_provision AS (
SELECT
DATE_TRUNC('day', block_timestamp) AS day_d,
SUM(REGEXP_SUBSTR(BASE64_DECODE_STRING(msgs[23]:attributes[4]:"value"), '\\d+')) / 1e6 AS amount_stSEI_deposit,
SUM(SUBSTR(REGEXP_SUBSTR(BASE64_DECODE_STRING(msgs[23]:attributes[4]:"value"), ',\\s(\\d+)'), 3)) / 1e6 AS amount_sei_deposit
FROM
sei.core.fact_transactions
WHERE
msgs[14]:attributes[2]:"value" = 'cHJvdmlkZV9saXF1aWRpdHk=' -- provide liquidity
AND msgs[7]:attributes[0]:"value" = 'c2VpMWxuN250c3FteGw4czUwMmY4M2ttOWE0NzV6eWhjZmhwajd2MmZzbTNwY21ja2R5eXMzdHNrdHg5dms=' -- contract
group by 1
),
------------- liq withdrawals -------------
liquidity_withdrawal AS (
SELECT
DATE_TRUNC('day', block_timestamp) AS day_w,
-SUM(REGEXP_SUBSTR(BASE64_DECODE_STRING(msgs[13]:attributes[4]:"value"), '\\d+')) / 1e6 AS amount_stSEI_withd,
-SUM(SUBSTR(REGEXP_SUBSTR(BASE64_DECODE_STRING(msgs[13]:attributes[4]:"value"), ',\\s(\\d+)'), 3))/ 1e6 AS amount_sei_withd
FROM
sei.core.fact_transactions
WHERE
msgs[13]:attributes[3]:"key" = 'd2l0aGRyYXduX3NoYXJl' -- contract
AND msgs[11]:attributes[3]:"value" = 'c2VpMWpxa3FwN3FsMG45ZWRwOWU3eTg2em50MG0yaDdyc3RsYzJyd2Z2eXh6cmo4NGV0OWN6YXE4OXVxZHg=' -- withdraw liquidity
group by 1
),
--- swaps ------
sei_swap AS (
SELECT
DATE_TRUNC('DAY', block_timestamp) AS day_swap,
SUM(
QueryRunArchived: QueryRun has been archived