-- with arbitrum as ( -- select count(*) as transactions, -- count(distinct from_address) as users, -- sum(value*power(0.1,18)) as Transaction_Token_Amount, -- cast(count(*)as double)/ count(distinct from_address) as txsuser_ratio, -- 'Arbitrum' as chain -- from "arbitrum_transactions" -- where receipt_status=1 and block_timestamp >= (date_add('hour', -24,NOW())) -- ), -- ethereum as ( with eth_price as ( select avg(price) as price from token_price_5min where token_address = '0xeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee' and chain ='Ethereum' and timestamp >= date_add('hour',-24,current_timestamp) ) select count(*) as transactions, count(distinct from_address) as users, sum(value*power(0.1,18)) as Transaction_Token_Amount, count(*)/ count(distinct from_address) as txsuser_ratio, 'Ethereum' as chain, cast(count(distinct hash) as double)/ 86400 as TPS, avg(value*power(0.1,18))* (select price from eth_price)as "Avg Transaction Value", sum(value*power(0.1,18) ) * (select price from eth_price) as "Transaction Value", avg(( coalesce(receipt_gas_used,gas) * power(0.1, 18) * coalesce(receipt_effective_gas_price,gas_price)))* (select price from eth_price) as "Avg Transaction Fee", avg(( coalesce(receipt_gas_used,gas) * power(0.1, 18) * coalesce(receipt_effective_gas_price,gas_price))) as "Avg Transaction Fee Amount" from "ethereum_transactions" where receipt_status=1 and block_timestamp >= (date_add('hour', -24,NOW())) -- ), -- avalanche as ( -- select count(*) as transactions, count(distinct from_address) as users, -- sum(value*power(0.1,18)) as Transaction_Token_Amount, -- count(*)/ count(distinct from_address) as txs_user_ratio, 'Avalanche' as chain -- from "avalanche_transactions" -- where receipt_status=1 and block_timestamp >= (date_add('hour', -24,NOW())) -- ), -- boba as ( -- select count(*) as transactions, count(distinct from_address) as users, -- sum(value*power(0.1,18)) as Transaction_Token_Amount, -- count(*)/ count(distinct from_address) as txs_user_ratio, 'Boba' as chain -- from "boba_transactions" -- where receipt_status=1 and block_timestamp >= (date_add('hour', -24,NOW())) -- ), -- bsc as ( -- select count(*) as transactions, count(distinct from_address) as users, -- sum(value*power(0.1,18)) as Transaction_Token_Amount, -- count(*)/ count(distinct from_address) as txs_user_ratio, 'BNB Chain' as chain -- from "bsc_transactions" -- where receipt_status=1 and block_timestamp >= (date_add('hour', -24,NOW())) -- ), -- celo as ( -- select count(*) as transactions, count(distinct from_address) as users, -- sum(value*power(0.1,18)) as Transaction_Token_Amount, -- count(*)/ count(distinct from_address) as txs_user_ratio, 'Celo' as chain -- from "celo_transactions" -- where receipt_status=1 and block_timestamp >= (date_add('hour', -24,NOW())) -- ), -- dfk as ( -- select count(*) as transactions, count(distinct from_address) as users, -- sum(value*power(0.1,18)) as Transaction_Token_Amount, -- count(*)/ count(distinct from_address) as txsuser_ratio, -- 'DFK' as chain -- from "dfk_transactions" -- where receipt_status=1 and block_timestamp >= (date_add('hour', -24,NOW())) -- ), -- fantom as ( -- select count(*) as transactions, count(distinct from_address) as users, -- sum(value*power(0.1,18)) as Transaction_Token_Amount, -- count(*)/ count(distinct from_address) as txs_user_ratio, 'Fantom' as chain -- from "fantom_transactions" -- where receipt_status=1 and block_timestamp >= (date_add('hour', -24,NOW())) -- ) -- , -- harmony as ( -- select count(*) as transactions, count(distinct from_address) as users, -- sum(value*power(0.1,18)) as Transaction_Token_Amount, -- count(*)/ count(distinct from_address) as txs_user_ratio, 'Harmony' as chain -- from "harmony_transactions" -- where receipt_status=1 and block_timestamp >= (date_add('hour', -24,NOW())) -- ), -- iotex as ( -- select count(*) as transactions, count(distinct from_address) as users, -- sum(value*power(0.1,18)) as Transaction_Token_Amount, -- count(*)/ count(distinct from_address) as txs_user_ratio, 'IoTeX' as chain -- from "iotex_transactions" -- where -- -- status='success' and -- block_timestamp >= (date_add('hour', -24,NOW())) -- ), -- moonbeam as ( -- select count(*) as transactions, count(distinct from_address) as users, -- sum(value*power(0.1,18)) as Transaction_Token_Amount, -- count(*)/ count(distinct from_address) as txs_user_ratio, 'Moonbeam' as chain -- from "moonbeam_transactions" -- where receipt_status=1 and block_timestamp >= (date_add('hour', -24,NOW())) -- ) -- , -- moonriver as ( -- select count(*) as transactions, count(distinct from_address) as users, -- sum(value*power(0.1,18)) as Transaction_Token_Amount, -- count(*)/ count(distinct from_address) as txs_user_ratio, 'Moonriver' as chain -- from "moonriver_transactions" -- where receipt_status=1 and block_timestamp >= (date_add('hour', -24,NOW())) -- ), -- polygon as ( -- select count(*) as transactions, count(distinct from_address) as users, -- sum(value*power(0.1,18)) as Transaction_Token_Amount, -- count(*)/ count(distinct from_address) as txs_user_ratio, 'Polygon' as chain -- from "polygon_transactions" -- where receipt_status=1 and block_timestamp >= (date_add('hour', -24,NOW())) -- ) -- , -- thundercore as ( -- select count(*) as transactions, count(distinct from_address) as users, -- sum(value*power(0.1,18)) as Transaction_Token_Amount, -- count(*)/ count(distinct from_address) as txs_user_ratio, 'ThunderCore' as chain -- from "thundercore_transactions" -- where receipt_status=1 and block_timestamp >= (date_add('hour', -24,NOW())) -- ) -- , -- oasys as ( -- select count(*) as transactions, count(distinct from_address) as users, -- sum(value*power(0.1,18)) as Transaction_Token_Amount, -- count(*)/ count(distinct from_address) as txsuser_ratio, -- 'Oasys' as chain -- from "oasys_transactions" -- where receipt_status=1 and block_timestamp >= (date_add('hour', -24,NOW())) -- ) -- ,optimism as ( -- select count(*) as transactions, count(distinct from_address) as users, -- sum(value*power(0.1,18)) as Transaction_Token_Amount, -- count(*)/ count(distinct from_address) as txs_user_ratio, -- 'Optimism' as chain -- from "optimism_transactions" -- where receipt_status=1 and block_timestamp >= (date_add('hour', -24,NOW())) -- ) -- ,cronos as ( -- select count(*) as transactions, -- count(distinct from_address) as users, -- sum(value*power(0.1,18)) as Transaction_Token_Amount, -- cast(count(*)as double)/ count(distinct from_address) as txsuser_ratio, -- 'Cronos' as chain -- from "cronos_transactions" -- where -- receipt_status=1 and -- block_timestamp >= (date_add('hour', -24,NOW())) -- ) -- ,ronin as ( -- select count(*) as transactions, -- count(distinct from_address) as users, -- sum(value*power(0.1,18)) as Transaction_Token_Amount, -- cast(count(*)as double)/ count(distinct from_address) as txsuser_ratio, -- 'Ronin' as chain -- from "ronin_transactions" -- where receipt_status=1 and block_timestamp >= (date_add('hour', -24,NOW())) -- )