SELECT

date_trunc('day', recorded_at) as day,

symbol,

avg(price) as price

FROM osmosis.core.dim_prices

WHERE recorded_at > '2022-01-01'

GROUP BY 1,2

),

df as (

SELECT

DATE,

project_name as currency,

count(DISTINCT osmosis.core.fact_daily_balances.address) as holders,

sum((balance/ power(10, decimal)) * price) as balance_sum

FROM osmosis.core.fact_daily_balances LEFT outer JOIN osmosis.core.dim_labels ON CURRENCY = osmosis.core.dim_labels.ADDRESS

LEFT OUTER JOIN df_prices on project_name = symbol AND day = date

WHERE date = CURRENT_DATE - 1 --and osmosis.core.fact_daily_balances.BALANCE_TYPE = 'liquid'

GROUP BY 1,2)

select

currency,

sum(holders) as holders,

sum(balance_sum) as balance_sum

FROM df

where balance_sum is not null

group by currency

order by holders desc, balance_sum DESC

limit 10