select

date_trunc('day',HOUR) as date,

round(avg(price)) as eth_price

from ethereum.core.fact_hourly_token_prices

where symbol = 'WETH'

and date >='2022-01-01'

group by date

order by date

),

miners AS (

SELECT distinct miner

FROM ethereum.core.fact_blocks

where BLOCK_TIMESTAMP <='2022-09-14'

b.date as date,

sum (a.AMOUNT_IN) as ETH_amounts,

b.eth_price

from Ethereum.core.ez_dex_swaps a

join eth_price b

on a.BLOCK_TIMESTAMP::date =b.date

where SYMBOL_IN='WETH'

and BLOCK_TIMESTAMP >='2022-06-01'

and ORIGIN_FROM_ADDRESS IN (SELECT * FROM miners)

group by 1,3