with ethpricet as (

select hour,

avg (price) as ethprice

from ethereum.price.ez_hourly_token_prices

where token_address = lower ('0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2')

group by 1),

userst as (

select from_address as "Wallet Address",

count (Distinct tx_hash) as "Transactions",

count (Distinct block_timestamp::date) as "Active Days",

sum (tx_fee) as "Paid Fees [ETH]",

sum (tx_fee*ethprice) as "Paid Fees [USD]"

from blast.core.fact_transactions t1 join ethpricet t2 on date_Trunc (hour,block_timestamp) = t2.hour

where block_timestamp >= '2024-02-29 00:00:00.000'

group by 1)

select case when "Active Days" = '1' then '1 Day'

when "Active Days" > 1 and "Active Days" <= 7 then '1 - 7 Days'

when "Active Days" > 7 and "Active Days" <= 14 then '7 - 14 Days'

when "Active Days" > 14 and "Active Days" <= 30 then '14 - 30 Days'

when "Active Days" > 30 and "Active Days" <= 60 then '30 - 60 Days'

when "Active Days" > 60 and "Active Days" <= 90 then '60 - 90 Days'

else '> 90 Days' end as type,

count (Distinct "Wallet Address") as "Wallets"

from userst

group by 1

order by 2 desc