with

PRICE as ( select

hour

,price as ETH_Price

from optimism.core.fact_hourly_token_prices

where symbol = 'ETH' )

,

maintable as (

select block_timestamp,

tx_hash,

buyer_address,

seller_address,

price_usd,

price_usd/ETH_Price as ETH_Volume

from optimism.core.ez_nft_sales a join PRICE p on date_trunc('hour',a.block_timestamp)= p.hour

where a.block_timestamp:: date < CURRENT_DATE

,

final as (

select block_timestamp::date as date,

case when ETH_Volume < 0.01 then 'Less Than 0.01 ETH'

when ETH_Volume >= 0.01 and ETH_Volume < 0.1 then '0.01 - 0.1 ETH'

when ETH_Volume >= 0.1 and ETH_Volume < 1 then '0.1 - 1 ETH'

when ETH_Volume >= 1 then 'More Than 1 ETH'

end as Type,

count (distinct tx_hash) as TX_Count,

sum (tx_count) over (order by date) as Cumulative_TX_Count

from maintable where ETH_Volume is not null

group by 1,2

order by 1 asc

)

select * , (select Avg(TX_Count) from final) as "Avg sales"