CoinConverseAvalanche-Open Daily STPM in each chain
Updated 2022-08-26
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 a_ as (SELECT --Avalanche
'avalanche' as chain,
date_trunc('day', block_timestamp) as dt,
count(*) as tx_count,
count(case when status like '%FAIL%' then 1 else null end) as fail_tx_count
FROM avalanche.core.fact_transactions
WHERE block_timestamp::date >= current_date-90 and block_timestamp::date <= current_date-1
GROUP BY dt
)
,op_ as (SELECT --Optimism
'optimism' as chain,
date_trunc('day', block_timestamp) as dt,
count(*) as tx_count,
count(case when status like '%FAIL%' then 1 else null end) as fail_tx_count
FROM optimism.core.fact_transactions
WHERE block_timestamp::date >= current_date-90 and block_timestamp::date <= current_date-1
GROUP BY dt
)
, n_ as ( -- near
SELECT
'near' as chain
, date_trunc('day', block_timestamp) as dt
, count(*) as tx_count
, count(CASE WHEN tx_receipt[0]:"outcome":"status":"Failure" is not null THEN 1 END) as fail_tx_count
FROM flipside_prod_db.mdao_near.transactions
WHERE block_timestamp::date >= current_date-90 and block_timestamp::date <= current_date-1
GROUP BY dt
)
, e_ as ( -- ethereum
SELECT
'ethereum' as chain
, date_trunc('day', block_timestamp) as dt
, count(*) as tx_count
, count(CASE WHEN status <> 'SUCCESS' THEN 1 END) as fail_tx_count
FROM ethereum.core.fact_transactions
WHERE block_timestamp::date >= current_date-90 and block_timestamp::date <= current_date-1
Run a query to Download Data