mz0111weekly report 15
Updated 2023-01-18
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
›
⌄
--credit to https://app.flipsidecrypto.com/dashboard/p22Vqu
--
with near_event AS (
SELECT
block_timestamp,
tx_hash,
receiver_id,
replace(value, 'EVENT_JSON:') as logs_cleaned,
split(logs_cleaned, ' ') as split_logs,
try_parse_json(logs_cleaned):data as data_log,
nvl(try_parse_json(logs_cleaned):event, try_parse_json(logs_cleaned):type) as event,
case
-- Staking NEAR
-- when receiver_id IN ('aurora.pool.near', 'aurora.poolv1.near') and split_logs[1] = 'staking' then 'Stake NEAR (Aurora Pool)'
-- when receiver_id NOT IN ('aurora.pool.near', 'aurora.poolv1.near') and receiver_id like '%pool%' and split_logs[1] = 'staking' then 'Stake NEAR (Other Pools)'
when receiver_id = 'meta-pool.near' and event = 'STAKE' then 'Liquid Stake NEAR (Meta)'
when receiver_id = 'linear-protocol.near' and event = 'stake' then 'Liquid Stake NEAR (Linear)'
when receiver_id = 'nearx.stader-labs.near' and event = 'deposit_and_stake' then 'Liquid Stake NEAR (Stader)'
else null end as event_action,
-- case when event_action = 'Stake NEAR (Aurora Pool)' then try_to_numeric(replace(split_logs[2],'.'))/pow(10,24) end as native_aurora_stake,
-- case when event_action = 'Stake NEAR (Other Pools)' then try_to_numeric(replace(split_logs[2],'.'))/pow(10,24) end as native_non_aurora_stake,
-- stake amount
try_to_numeric(replace(split_logs[2],'.'))/pow(10,24) as native_stake, -- native staking pool relies on log
try_parse_json(logs_cleaned):amount/pow(10,24) as meta_stake_amount, -- meta pool / relies on json
data_log[0]:amount/pow(10,24) as stader_stake_amount, -- stader pool
data_log[0]:staked_amount/pow(10,24) as linear_stake_amount, -- linear pool
regexp_substr(status_value, 'Success') as reg_success,
coalesce(native_stake, meta_stake_amount, stader_stake_amount, linear_stake_amount) as near_staked
FROM near.core.fact_receipts,
table(flatten(input =>logs))
WHERE reg_success IS NOT NULL -- filter out successful transactions
AND receiver_id NOT IN ('nearton-staking.near', 'xtoken.ref-finance.near')
)
SELECT
date_trunc('day', block_timestamp) as date,
count(distinct tx_hash) as "Tx Count",
Run a query to Download Data