mz0111near season 7
Updated 2022-10-19
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 elmiaON
WITH n_ as ( -- success near
SELECT
block_timestamp
, TX_HASH
, substr(split(tx:"actions"[0], ':')[0], 3, len(split(tx:"actions"[0], ':')[0])-3) as action ,
TX_STATUS
from near.core.fact_transactions
WHERE block_timestamp::date >= CURRENT_DATE-90
)
, s_ as ( -- success
SELECT
date_trunc('day', block_timestamp) as date
, action
, count (TX_STATUS ) as tx_count
FROM n_
where TX_STATUS = 'Success'
GROUP BY date, action
)
, f_ as ( -- fail
SELECT
date_trunc('day', block_timestamp) as date
, action
, count (TX_STATUS ) as tx_count
FROM n_
where TX_STATUS = 'Fail'
GROUP BY date, action
)
SELECT
s_.date
, CASE WHEN s_.action = 'eateAccoun' THEN 'CreateAccount' ELSE s_.action END action
, s_.tx_count as success_count
, f_.tx_count as fail_count
, success_count+fail_count as tx_count
FROM s_ join f_ on s_.date = f_.date and s_.action = f_.action
Run a query to Download Data