date(date_trunc('day', block_timestamp)) as date,

nft_address,

count(distinct(tx_hash)) as transaction_count,

sum(price) as eth_volume,

avg(price) as avg_price,

sum(total_fees) as eth_fee,

sum(platform_fee) as eth_platform_fee,

sum(creator_fee) as eth_creator_fee

from optimism.core.ez_nft_sales

where event_type = 'sale'

and currency_symbol = 'ETH'

group by nft_address, date),

eth_price as (select

date(date_trunc('day', hour)) as date,

avg(price) as price

from ethereum.core.fact_hourly_token_prices

where token_address is null

group by date),

joined as (select * from nft_data join eth_price using (date)),

usd_values as (select *,

eth_volume*price as volume_usd,

avg_price*price as avg_price_usd,

eth_fee*price as fee_usd,

eth_platform_fee*price as platform_fee_usd,

eth_creator_fee*price as creator_fee_usd,

case

when nft_address = '0x0110bb5739a6f82eafc748418e572fc67d854a0f' then 'Early Optimists'

when nft_address = '0xfa14e1157f35e1dad95dc3f822a9d18c40e360e2' then 'Optimism Quests'

when nft_address = '0xb8df6cc3050cc02f967db1ee48330ba23276a492' then 'OptiPunks'

when nft_address = '0x0deaac29d8a3d4ebbaaa3ecd3cc97c9def00f720' then 'OAYC'

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

when nft_address = '0x74a002d13f5f8af7f9a971f006b9a46c9b31dabd' then 'RabbitHole L2 Explorer'