SELECT

date_trunc('month',DATE),

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

FROM osmosis.core.fact_daily_balances

LEFT outer JOIN osmosis.core.dim_labels

ON CURRENCY = osmosis.core.dim_labels.ADDRESS

WHERE project_name LIKE 'OSMO' and date_trunc('month' , DATE) = date_trunc('month' , CURRENT_DATE - interval '1 month')

GROUP BY 1

order by 1

with cte3 as

with cte2 AS

(

with dt as (

select

-- first argument is unit of time to add, second is amount to increment, third is starting date

dateadd(month, '-' || row_number() over (order by null), date_trunc('month' , current_date)) as cols

from table (generator(rowcount => 60)) --increment this

)

select date(date_trunc('month',cols) + INTERVAL '1 month' - INTERVAL '1 DAY' ) as reqcol from dt

)

select date_trunc('month',a.date) as months,

a.address,

a.balance,

row_number() over (partition by date_trunc('month',a.date) order by balance desc) as rnk

from

(

select date,

address,

balance/pow(10,decimal) as balance

from osmosis.core.fact_daily_balances

where currency='uosmo' and date in (select cte2.reqcol from cte2)