sum(TO_AMOUNT/POW(10, TO_DECIMAL)) AS weekly_tokens_purchased,

TRADER,

POOL_IDS AS pool,

TO_CURRENCY AS token_address,

SUBSTRING(TRADER, 38, 43) as trader_short_address

from osmosis.core.fact_swaps

WHERE BLOCK_TIMESTAMP > CURRENT_DATE - 7

AND TO_CURRENCY='ibc/27394FB092D2ECCD56123C74F36E4C1F926001CEADA9CA97EA622B25F41E5EB2'

GROUP BY 2,4,3,5

),

labels AS (

SELECT

CURRENCY AS token_address,

SYMBOL AS token_name,

avg(PRICE) AS average_price_usd

FROM osmosis.core.ez_prices

WHERE RECORDED_HOUR > CURRENT_DATE - 7

GROUP BY 1,2

s.TRADER,

s.trader_short_address,

s.weekly_tokens_purchased,

s.weekly_tokens_purchased * l.average_price_usd AS value_USD,

l.average_price_usd,

s.pool

FROM swaps s

LEFT JOIN labels l USING (token_address)

WHERE weekly_tokens_purchased>1

ORDER BY 3 DESC

LIMIT 10;