WITH PRICE AS (
SELECT
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