select date_trunc('day', hour)::date as op_date, avg(price) as op_price

from optimism.core.fact_hourly_token_prices

where symbol = 'OP'

group by 1

), eth_prices as (

select date_trunc('day', hour)::date as eth_date, avg(price) as eth_price

from optimism.core.fact_hourly_token_prices

where symbol = 'WETH'

group by 1

), nft_sales as (

select

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

PLATFORM_NAME,

TX_HASH,

SELLER_ADDRESS,

BUYER_ADDRESS,

NFT_ADDRESS,

case

when CURRENCY_SYMBOL = 'ETH' or CURRENCY_SYMBOL = 'WETH' then PRICE * eth_price

when CURRENCY_SYMBOL = 'OP' then PRICE * op_price

end as price_usd_calc,

TOTAL_FEES,

TOTAL_FEES * eth_price as TOTAL_FEES_USD,

eth_price,

op_price

from optimism.core.ez_nft_sales sales left join op_prices on date_trunc('day', block_timestamp)::date = op_date

left join eth_prices on date_trunc('day', block_timestamp)::date = eth_date

where CURRENCY_SYMBOL is not null

), final_data as (

select

date_trunc('week', sale_date) as date,

PLATFORM_NAME as marketplace,

count(distinct TX_HASH) as N_SALES,

count(distinct SELLER_ADDRESS) as N_SELLERS,

count(distinct BUYER_ADDRESS) as N_BUYERS,