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

<script defer data-domain="html.cafe" src="https://milkymouse.com/js/script.js"></script>