WITH price AS (

SELECT

recorded_hour::DATE AS date,

currency,

AVG(price) AS token_price

FROM osmosis.price.ez_prices

WHERE currency = 'factory/osmo17fel472lgzs87ekt9dvk0zqyh5gl80sqp4sk4n/LAB'

GROUP BY 1, 2

SELECT

a.Date,

COUNT(DISTINCT a.address) AS token_holders,

SUM(a.balance)/1000000/10 AS token_balance,

SUM(b.token_price*a.balance)/1000000/10 AS token_balance_usd

FROM osmosis.core.fact_daily_balances a

LEFT JOIN price b

ON a.DATE= b.date AND a.currency = b.currency

a.currency = 'factory/osmo17fel472lgzs87ekt9dvk0zqyh5gl80sqp4sk4n/LAB'

AND a.date >= '2024-04-04'

AND balance/1000000 >= 10

AND balance/1000000 <= 1000

GROUP BY 1

ORDER BY 1 DESC