WITH

filtered_liquidity_pools AS (

SELECT pool_address

, trim(replace(replace(pool_name, 'SLP', ''), 'LP', '')) AS _trimmed_pool_name

, token0

, token1

, split(_trimmed_pool_name, '-')[0] AS symbol_in

, split(_trimmed_pool_name, '-')[1] AS symbol_out

, _trimmed_pool_name AS pool_name

FROM ethereum.core.dim_dex_liquidity_pools

WHERE pool_address = lower('{{Ethereum_Pool_Address}}') --'0x397ff1542f962076d0bfe58ea045ffa2d347aca0'

and platform ilike '%uniswap-v3%'

),

block_params AS (

SELECT min(block_number) AS start_block

, max(block_number) as end_block

FROM ethereum.core.fact_blocks --where block_timestamp>='2023-06-21'

),

latest_prices AS (

SELECT *

FROM (

SELECT token_address, hour, avg(price) AS price

FROM ethereum.core.fact_hourly_token_prices

--WHERE hour >= CURRENT_TIMESTAMP - interval '7 days'

GROUP BY 1,2

)

QUALIFY row_number() OVER (partition by token_address order by hour desc) = 1

),