HOUR::date as day,

avg(price) as token_price

from ethereum.core.fact_hourly_token_prices

where SYMBOL = 'WETH'

group by 1

),

decoded_sale as (select

s.BLOCK_TIMESTAMP,

s.tx_hash,

DECODED_LOG:from as seller,

DECODED_LOG:to as buyer,

DECODED_LOG:tokenId as TOKENID,

ETH_value as amount,

ETH_value*token_price as amount_usd

from arbitrum.core.fact_decoded_event_logs s

left join arbitrum.core.fact_transactions a on s.tx_hash=a.tx_hash

left join p_eth p on s.BLOCK_TIMESTAMP::date=p.day

where s.CONTRACT_ADDRESS = '0x17f4baa9d35ee54ffbcb2608e20786473c7aa49f'

and s.EVENT_NAME = 'Transfer'

and DECODED_LOG:from != '0x0000000000000000000000000000000000000000'

and ETH_value>0.001

and a.STATUS='SUCCESS'

CASE

WHEN (amount_usd) <= 50 THEN 'a.below 50'

WHEN (amount_usd) > 50 and (amount_usd) <=200 THEN 'b.50-200'

WHEN (amount_usd) > 200 and (amount_usd) <=500 THEN 'c.200-500'

WHEN (amount_usd) > 500 and (amount_usd) <=1000 THEN 'd.500-1000'

WHEN (amount_usd) > 1000 THEN 'e.above 1000'

END as buckets,

count(distinct tx_hash) as sale_no,

count(distinct buyer) as buyer_no