with Contracts as ( select contract_address,

count(DISTINCT a.block_timestamp::date) as "Active Days",

count(DISTINCT a.tx_hash) as "Transactions",

count(DISTINCT a.ORIGIN_FROM_ADDRESS) as "DAU",

"Transactions"/"DAU" as "Avg Txns per User",

sum(tx_fee) as "Consumed Fees (BERA)",

avg(tx_fee) as "Avg Fees (BERA)",

max(tx_fee) as "Max Fees (BERA)",

median(tx_fee) as "Median Fee (BERA)"

from berachain.testnet.fact_event_logs a join berachain.testnet.fact_transactions b on a.tx_hash = b.tx_hash

group by 1)

,

first_interaction as ( select origin_from_address as user,

MIN(block_timestamp::DATE) as first_interaction_date

from berachain.testnet.fact_event_logs

group by origin_from_address),

new_users as (select f.block_timestamp::date as date,

f.contract_address,

f.origin_from_address as user

from berachain.testnet.fact_event_logs f JOIN first_interaction fi ON f.origin_from_address = fi.user AND f.block_timestamp::DATE = fi.first_interaction_date)

,

final_new as (select contract_address,

COUNT(DISTINCT user) as new_user_count_24

from new_users

where date >= current_timestamp() - interval '24 hours'

group by contract_address

order by 2 desc)

,

total_new as ( select count(DISTINCT user) as total_new_24

from first_interaction

where first_interaction_date >= current_timestamp() - interval '24 hours' )

,

total_activity as ( select count(DISTINCT tx_hash) as Total_transactions,