KaskoazulRef_finance user stats (bots)
Updated 2023-05-07
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
›
⌄
with raw as (
select t.tx_hash,
t.block_timestamp,
t.tx_signer,
t.transaction_fee / pow(10,24) as fee,
e.action_name, --if function_call table, all are FunctionCall
e.method_name,
try_parse_json(e.args) as p_args,
p_args:actions[0]:amount_in as amount_in_decimals,
p_args:actions[0]:min_amount_out as amount_out_decimals,
p_args:actions[0]:pool_id as pool_id,
p_args:actions[0]:token_in as address_in,
p_args:actions[0]:token_out as address_out
from near.core.fact_transactions t
left join near.core.fact_actions_events_function_call e
on t.tx_hash = e.tx_hash
where t.tx_receiver = 'v2.ref-finance.near'
and e.method_name = 'swap'
),
failed_swaps as (
select r.tx_hash,
case
when regexp_substr(status_value, 'Failure') = 'Failure' then 'Failure'
else 'Success'
end as status
from near.core.fact_receipts r
left join near.core.fact_transactions t
on r.tx_hash = t.tx_hash
left join near.core.fact_actions_events_function_call e
on r.tx_hash = e.tx_hash
where t.tx_receiver = 'v2.ref-finance.near'
and e.method_name = 'swap'
and status = 'Failure'
),