SELECT

date_trunc('d', hour) as _date,

avg(price) as avg_price_usd

from ethereum.core.fact_hourly_token_prices

where _date between '2022-01-01' and '2022-06-30'

and symbol = 'WETH'

group by 1

),

openfee as (

SELECT

DATE_TRUNC('d', block_timestamp) as _date,

-- CASE

-- WHEN currency_symbol = 'ETH' then 'ETH'

-- WHEN currency_symbol = 'WETH' then 'WETH'

-- WHEN currency_symbol = 'USDC' then 'USDC'

-- ELSE 'Other'

-- END AS currency_symbol,

SUM(platform_fee) AS total_platform_fee,

SUM(platform_fee_usd) AS total_platform_fees_usd

FROM ethereum.core.ez_nft_sales

WHERE block_timestamp::DATE BETWEEN '2022-01-01' AND '2022-06-30'

AND platform_name = 'opensea'

GROUP BY 1

),

final as (

o._date,

p.avg_price_usd,

o.total_platform_fees_usd

from openfee o

left join prices p using (_date)

select * from final

order by 1