CODXIIIAll in One
Updated 2023-04-20
999
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
›
⌄
-- forked from All in One @ https://flipsidecrypto.xyz/edit/queries/8943ffcf-1c92-43ed-bb43-b206014f1679
WITH table1 AS
(
SELECT
tx_hASh
FROM
near.core.fact_actions_events_function_call
WHERE
method_name IN
(
'deposit_and_stake'
)
GROUP BY
1
)
,
table2 AS
(
SELECT
date_trunc('month', block_timestamp) AS Month,
tx_receiver AS s_poll,
AVG(try_to_numeric(TRIM(REGEXP_REPLACE(regexp_substr(tx:receipt[0]:outcome:logs, 'Contract total staked balance is\\W+\\w+'), '[a-z/-/A-z/./#/*"]', ''))))*1e-24 AS pool_volume
FROM
near.core.fact_transactions t1
JOIN
table1 t2
WHERE
t1.tx_hASh = t2.tx_hASh
GROUP BY
1,
2
)
,
table3 AS
(