-- 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()))
-- )