select

date_trunc('week',block_timestamp) as week_start,

count(distinct from_address)+ count(distinct to_address) as total_unique_users,

count(tx_hash) as total_tx,

sum(case when lower(status)= 'success' then 1 else 0 end) as successful_tx,

sum(case when lower(status) != 'success' and status is not null then 1 else 0 end) as failed_tx,

sum(gas_used * gas_price)/1e18 as total_gas_spent_eth

from ethereum.core.fact_transactions

where block_timestamp >='2024-09-01' and block_timestamp <'2024-10-01'

group by week_start

select

week_start,

total_unique_users,

total_tx,

successful_tx,

failed_tx,

round((successful_tx :: float/total_tx)*100,2) as success_rate,

round((failed_tx :: float/total_tx)*100,2) as failure_rate,

from weekly_activity

order by week_start asc;