PRICE as ( select

hour

,price as ETH_Price

from optimism.core.fact_hourly_token_prices

where symbol = 'ETH' )

,

maintable2 as (

select block_timestamp,tx_hash,

buyer_address,

seller_address,

price_usd

,price_usd/ETH_Price as ETH_Volume

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

where a.block_timestamp:: date < CURRENT_DATE and PRICE_USD is not null and a.block_timestamp:: date>= '2021-12-18')

(select

block_timestamp::date,

'Ethereum' as Blockchain,

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

from maintable2 where ETH_Volume is not null

group by 1,2,3

order by 2 desc

)