with eth as (

select

hour::date as day,

avg(price) as eth

from ethereum.core.fact_hourly_token_prices

where

day between '2022-06-01' and current_date - 1 and

symbol = 'WETH'

group by 1

),

op as (

select

hour::date as day,

avg(price) as op

from optimism.core.fact_hourly_token_prices

where

day between '2022-06-01' and current_date - 1 and

symbol = 'OP'

group by 1

select

date_trunc('day', block_timestamp) as date,

case when nft_address = '0x81b30ff521d1feb67ede32db726d95714eb00637' then 'Optimistic Explorer'

when nft_address = '0xa95579592078783b409803ddc75bb402c217a924' then 'Optimism Collective: Hello World'

when nft_address = '0x69a68eb548a37ee475d9f89646945588558796d1' then 'Oliens'

when nft_address = '0xb91b2276bd5a98994bf1f496e3886f688f8d4581' then 'Kasou-shin Genesis NFTs'

when nft_address = '0xbe81eabdbd437cba43e4c1c330c63022772c2520' then 'unknown'

when nft_address = '0xdbfeaae58b6da8901a8a40ba0712beb2ee18368e' then 'Dope Wars Hustlers'

when nft_address = '0x8e56343adafa62dac9c9a8ac8c742851b0fb8b03' then 'Bored Town'

when nft_address = '0xa698713a3bc386970cdc95a720b5754cc0f96931' then 'Words (viaMirror)'

when nft_address = '0xc36442b4a4522e871399cd717abdd847ab11fe88' then 'Uniswap V3 Positions'

when nft_address = '0xe0a3711d4286e628998d47bef524c292defd1719' then 'Holo Mask : White & Black' else 'others' end as collection,

sum(price * eth) as volume_in_usd,

sum(volume_in_usd) over (partition by collection order by date) as cumulative_volume_in_usd,