),

prices AS

SELECT

date_trunc('day', hour) as day,

token_address,

avg(price) as price

FROM ethereum.token_prices_hourly

WHERE

hour > getdate() - interval '3 months'

GROUP BY 1,2

),

liquid AS (

SELECT

date_trunc('day', balance_date) as day,

user_address as pair,

contract_address as token,

sum(amount_usd) as liquidity,

row_number() over(partition by date_trunc('day', balance_date), user_address ORDER BY date_trunc('day', balance_date)) as rn

FROM ethereum.erc20_balances b

LEFT JOIN pairs ON b.user_address = pairs.pair

LEFT JOIN prices ON date_trunc('day', balance_date) = prices.day AND prices.token_address = b.contract_address

WHERE

balance_date > getdate() - interval '3 months'

AND user_address IN (pairs.pair)

AND balance != 0

AND amount_usd IS NOT NULL

GROUP BY 1,2,3

),

pairtvl AS (