with exchanges as (

SELECT

BLOCK_TIMESTAMP as time

, tx_hash

, case when token_in = '0xae7ab96520de3a18e5e111b5eaab095312d7fe84' then amount_in

when token_out = '0xae7ab96520de3a18e5e111b5eaab095312d7fe84' then amount_out

end as steth_amount

, case when token_in = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' then amount_in

when token_out = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' then amount_out

end as eth_amount

FROM Ethereum.defi.ez_dex_swaps

where CONTRACT_ADDRESS = '0xdc24316b9ae028f1497c275eb9192a3ea0f67022'

AND steth_amount is NOT NULL

and eth_amount is NOT NULL

)

SELECT

date_trunc('hour', time) as time

, SUM(steth_amount) as steth_amount

, SUM(eth_amount) as eth_amount

, SUM(eth_amount)/ SUM(steth_amount) as rate

FROM exchanges

WHERE steth_amount > 2 -- filter for txns with minor quantities with screwed rate

and time >= current_date - interval '30 days'

GROUP by 1

ORDER by 1 DESC