date_trunc('day',block_timestamp) as date,

sum(CASE WHEN tx_status = 'Success' THEN 1 ELSE 0 END) as successful_txns,

sum(CASE WHEN tx_status = 'Fail' THEN 1 ELSE 0 END) as unsuccessful_txns,

successful_txns/(3600*24) as tps,

100*successful_txns/(successful_txns+unsuccessful_txns) as success_rate,

successful_txns+unsuccessful_txns as n_txns ,

sum(transaction_fee)/pow(10,24) as total_tx_fee,

avg(TRANSACTION_fee)/pow(10,24) as avg_tx_fee

FROM near.core.fact_transactions

WHERE date <= CURRENT_DATE-1

GROUP BY date

ORDER BY date DESC