nitsUntitled Query
Updated 2022-11-16
999
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
›
⌄
-- -Number of transactions -Transaction success rates -Number of unique wallets to make a transaction -Number of wallets that used the chain everyday since May 9th -Transaction fees
with flow_data as
(SELECT date(block_timestamp) as day, count(DISTINCT tx_id) as total_txs, sum(success)/(sum(success)+sum(failed))*100 as percent_sucess,
avg(percent_sucess) over (order by day) as avg_sucess, sum(total_txs) over (order by day) as cumulative_txs, count(DISTINCT payer) as daily_users,
sum(daily_users) over (order by day) as cumulative_wallets, 'flow' as type, '0' AS FEES, sum(FEES) over (order by day) as cumulative_fees
, fees/total_txs as fee_per_tx
from (SELECT *, iff(tx_succeeded, '1', '0') as success,iff(tx_succeeded, '0', '1') as failed
from flow.core.fact_transactions)
where day >= '2022-07-01'
GROUP by 1 ),
osmosis_data as
(SELECT date(block_timestamp) as day, count(DISTINCT tx_id) as total_txs, sum(success)/(sum(success)+sum(failed))*100 as percent_sucess,
avg(percent_sucess) over (order by day) as avg_sucess, sum(total_txs) over (order by day) as cumulative_txs, count(DISTINCT tx_from) as daily_users,
sum(daily_users) over (order by day) as cumulative_wallets , 'osmosis' as type, sum(fee_)/pow(10,6) as fees, sum(FEES) over (order by day) as cumulative_fees
, fees/total_txs as fee_per_tx
from (SELECT *, iff(tx_status ='SUCCEEDED', '1', '0') as success,iff(tx_status='FAILED', '1', '0') as failed, replace(fee,'uosmo','') as fee_
from osmosis.core.fact_transactions)
where day >= '2022-07-01' and contains(fee, 'uosmo')
GROUP by 1 ),
eth_data as
(SELECT date(block_timestamp) as day, count(DISTINCT tx_hash) as total_txs, sum(success)/(sum(success)+sum(failed))*100 as percent_sucess,
avg(percent_sucess) over (order by day) as avg_sucess, sum(total_txs) over (order by day) as cumulative_txs, count(DISTINCT from_address) as daily_users,
sum(daily_users) over (order by day) as cumulative_wallets , 'ethereum' as type, sum(tx_fee)*1200 as fees, sum(FEES) over (order by day) as cumulative_fees
, fees/total_txs as fee_per_tx
from (SELECT *, iff(status ='SUCCESS', '1', '0') as success,iff(status='FAIL', '1', '0') as failed
from ethereum_core.fact_transactions)
where day >= '2022-07-01'
GROUP by 1 ),
sol_data as
(SELECT date(block_timestamp) as day, count(DISTINCT tx_id) as total_txs, sum(success)/(sum(success)+sum(failed))*100 as percent_sucess,
avg(percent_sucess) over (order by day) as avg_sucess, sum(total_txs) over (order by day) as cumulative_txs, count(DISTINCT signers[0]) as daily_users,
sum(daily_users) over (order by day) as cumulative_wallets , 'solana' as type, sum(fee)/pow(10,8)*40 as fees, sum(FEES) over (order by day) as cumulative_fees
, fees/total_txs as fee_per_tx
from (SELECT *, iff(succeeded, '1', '0') as success,iff(succeeded, '0', '1') as failed
from solana.fact_transactions)
Run a query to Download Data