with cte as (

SELECT

date_trunc('day', block_timestamp) as date,

count(1) as trades,

sum(event_inputs:value / pow(10,18)) as fees,

sum(fees) over(order by date asc) as cumulative_fees

FROM polygon.core.fact_event_logs

WHERE block_timestamp >= '2022-06-15'

AND contract_address = lower('0x7ceb23fd6bc0add59e62ac25578270cff1b9f619') -- WETH contract

AND event_name = 'Transfer'

AND event_inputs:to = lower('0x8de9c5a032463c561423387a9648c5c7bcc5bc90') -- OpenSea Polygon Fess Receiver

GROUP BY date

),

eth_prices as (

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

avg(price) as eth_daily_price_avg

FROM ethereum.core.fact_hourly_token_prices

where hour >= '2022-06-15' AND symbol = 'WETH'

group BY date

SELECT cte.*, eth_prices.eth_daily_price_avg

FROM cte

LEFT JOIN eth_prices

on eth_prices.date = cte.date