with eth_price_data as (
select hour::date as date, avg(price) as eth_price
from optimism.core.fact_hourly_token_prices
where symbol = 'ETH'
group by 1
), Quix_all_sale_data as (
select
block_timestamp::date as date,
tx_hash,
BUYER_ADDRESS as user,
SELLER_ADDRESS,
NFT_ADDRESS,
ADDRESS_NAME as collection_name,
CURRENCY_SYMBOL as symbol,
case
when symbol = 'ETH' then price
else price_usd/eth_price
end as price_eth,
price_usd
from optimism.core.ez_nft_sales s
left join eth_price_data on s.block_timestamp::date = eth_price_data.date
left join optimism.core.dim_labels labels on s.NFT_ADDRESS = labels.ADDRESS
where tx_hash != '0x69d196ce34aac73f4ca25198b153babe618d4f14726e3d827b47801a4bec1f72'
), opensea_all_sale_data as (
with opensea_weth as (
select
t.block_timestamp::date as date,
t.tx_hash,
t.ORIGIN_FROM_ADDRESS as user,
l.contract_Address as nft_address,
ADDRESS_NAME as collection_name,
t.raw_amount/1e18 as price_eth,
'WTH' as symbol
from optimism.core.fact_token_transfers t
join optimism.core.fact_event_logs l using(tx_hash)
left join optimism.core.dim_labels labels on l.contract_Address = labels.ADDRESS