SELECT hour::date AS date,

avg(price) AS UsdPrice

FROM ethereum.core.fact_hourly_token_prices

WHERE symbol ='WETH'

GROUP BY 1)

, a AS( SELECT WITHDRAWER AS wallet,

sum(AMOUNT_WITHDRAWN) AS numberOfWETHs ,

sum(UsdPrice*AMOUNT_WITHDRAWN) AS Amount

FROM ethereum.maker.ez_withdrawals w INNER JOIN hourlyPrice h ON BLOCK_TIMESTAMP::date=h.date

WHERE symbol='WETH' AND BLOCK_TIMESTAMP::date='2020-09-19 00:00:00.000'

AND TOKEN_WITHDRAWN='0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' --WETH

and AMOUNT_WITHDRAWN >0

GROUP BY 1

)

SELECT *

FROM a

ORDER BY 2 DESC