(SELECT date_trunc('hour',BLOCK_TIMESTAMP) as date, 'OP' as SYMBOL, avg(AMOUNT_OUT/AMOUNT_IN) as op_price

FROM optimism.velodrome.ez_swaps

where SYMBOL_IN='OP' and SYMBOL_OUT in('USDC','DAI')

group by date),

eth_price as (

select HOUR as date, SYMBOL, PRICE as eth_price

from flipside_prod_db.ethereum.token_prices_hourly

where TOKEN_ADDRESS is null

),

nft_sales as (

BLOCK_TIMESTAMP, SELLER_ADDRESS, BUYER_ADDRESS, NFT_ADDRESS, TOKENID, CURRENCY_SYMBOL, PRICE, op_price, eth_price,

case

when CURRENCY_SYMBOL='ETH' then PRICE*eth_price

when CURRENCY_SYMBOL='OP' then PRICE*op_price

end as price_USD

from optimism.core.ez_nft_sales a

left join op_price b

on date_trunc('hour',a.BLOCK_TIMESTAMP)=date_trunc('hour',b.date)

left join eth_price c

on date_trunc('hour',a.BLOCK_TIMESTAMP)=date_trunc('hour',c.date)

where seller_address!='0x0a0805082ea0fc8bfdcc6218a986efda6704efe5'

)

SELECT *,

row_number() over (order by volume desc) as rank

from

(select SELLER_ADDRESS||'.' as SELLER_ADDRESS, sum(PRICE_USD) as volume, count(*) as sales

from nft_sales

group by SELLER_ADDRESS

order by volume desc

limit 20)