SELECT

date_trunc('day', BLOCK_TIMESTAMP) as date,

count (DISTINCT TX_HASH) as tx_count,

count (DISTINCT BLOCK_ID) as block,

count (DISTINCT TX_SIGNER) as active_wallet,

tx_count/86400 as txpersecond,

sum(TRANSACTION_FEE/pow(10,24)) as total_fee,

total_fee/tx_count as avg_fee,

tx_count/block as tx_block,

tx_count/active_wallet as tx_wallet

from near.core.fact_transactions

where TX_STATUS = 'Success' and date >= '2022-11-01'

group by 1)

SELECT sum(tx_count), sum(tx_count)/sum("Active wallets") as txperwallet

from (

SELECT

date_trunc('day', date) as date,

sum(tx_count) as tx_count,

avg(txpersecond) as "Average",

max(txpersecond) as "Peak",

sum(total_fee) as total_fee,

avg(avg_fee) as avg_fee,

min(avg_fee) as min_fee,

max(avg_fee) as max_fee,

median(avg_fee) as median_fee,

avg(tx_block) as tx_block,

sum(active_wallet) as "Active wallets",

avg(tx_wallet) as "Avg #Txs per wallet"

FROM df group by 1

)