WITH weth_prices as (
SELECT
date(hour) as date,
avg(price) as price
FROM
ETHEREUM_CORE.fact_hourly_token_prices
WHERE
symbol = 'WETH'
and date(hour) >= CURRENT_DATE - 8
GROUP BY date
),
pools AS (
SELECT
pool_name,
pool_address,
token0,
token1
FROM
ETHEREUM_CORE.DIM_DEX_LIQUIDITY_POOLS
WHERE
pool_address = LOWER('0xceff51756c56ceffca006cd410b03ffc46dd3a58')
),
-- get details for tokens in relevant pool
decimals AS (
SELECT
address,
symbol,
decimals
FROM
ETHEREUM_CORE.DIM_CONTRACTS
WHERE
address = (
SELECT
LOWER(token1)
FROM
pools