WITH ethereum_raw AS (

SELECT

block_timestamp::date AS date,

CASE WHEN token_in = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' THEN amount_in/amount_out ELSE amount_out/amount_in END AS price,

row_number() OVER (PARTITION BY block_timestamp::date ORDER BY block_timestamp) AS nomor_asc,

row_number() OVER (PARTITION BY block_timestamp::date ORDER BY block_timestamp DESC) AS nomor_dsc

FROM

ethereum.core.ez_dex_swaps

WHERE

contract_address = lower('{{Ethereum_Pool_Address}}') --'0x397ff1542f962076d0bfe58ea045ffa2d347aca0'

AND amount_in_usd IS NOT NULL

AND amount_out_usd IS NOT NULL

and platform ilike '%uniswap-v3%'

),

max_min_eth AS (

SELECT

date,

MAX(price) AS max_price,

MIN(price) AS min_price

FROM

ethereum_raw

GROUP BY 1

),

first_eth AS (

SELECT * FROM ethereum_raw WHERE nomor_asc = 1

),

last_eth AS (

SELECT * FROM ethereum_raw WHERE nomor_dsc = 1

),

fix_eth AS (

SELECT

a.date,

'sushi' AS label,

(max_price + min_price + b.price + c.price) / 4 AS price