kaibladeMonthly Active Pool Volume
Updated 2022-11-09
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
›
⌄
WITH raw_data AS
(
SELECT block_timestamp,
tx_hash,
tx_receiver,
tx_signer,
tx:actions[0]:FunctionCall:deposit/1e24 AS staked_amount,
tx:receipt[0]:outcome:logs as logs,
logs[array_size(logs)-1] AS last_item,
split(last_item, ' ') AS item_array,
TRIM(item_array[5], '.') AS total_node_stake,
item_array[10] AS total_stake_shares
FROM near.core.fact_transactions
WHERE tx_hash IN ( SELECT tx_hash
FROM near.core.fact_actions_events_function_call
WHERE method_name IN ('deposit_and_stake'))
AND (total_node_stake IS NOT NULL AND total_stake_shares IS NOT NULL)
)
SELECT
DATE_TRUNC('month',block_timestamp) AS months,
count(DISTINCT tx_receiver) as active_pools
FROM raw_data
GROUP BY 1
Run a query to Download Data