DATE_TRUNC('day',RECORDED_AT) as P_DATE,

SYMBOL, T2.ADDRESS,

AVG (PRICE) AS TOKEN_PRICE

FROM osmosis.core.dim_prices T1

JOIN osmosis.core.dim_labels T2 ON PROJECT_NAME = SYMBOL

GROUP BY 1,2,3),

TOP_TOKEN AS (

SELECT

DATE,

SYMBOL,

SUM (BALANCE/pow(10,DECIMAL) * TOKEN_PRICE) AS USD_BALANCE,

RANK () over (partition by DATE order by USD_BALANCE DESC ) as RANK

FROM osmosis.core.fact_daily_balances T1

JOIN PRICE T2 ON CURRENCY = T2.ADDRESS AND P_DATE = DATE

WHERE DATE = '2022-10-26'

AND BALANCE_TYPE = 'liquid' AND SYMBOL NOT IN ('INJ')

GROUP BY 1,2

)

SELECT * FROM TOP_TOKEN WHERE RANK <= 10