with start_of_the_year as(
SELECT
address,
BALANCE/pow(10,decimal) as balance_start_of_year,
RANK() OVER(ORDER BY BALANCE desc) as rank_start_of_year
FROM osmosis.core.fact_daily_balances
where CURRENCY='uosmo' and BALANCE_TYPE='staked' and date='2022-01-01'),
right_now as(
SELECT
address,
BALANCE/pow(10,decimal) as balance_right_now,
RANK() OVER(ORDER BY BALANCE desc) as rank_right_now
FROM osmosis.core.fact_daily_balances
where CURRENCY='uosmo' and BALANCE_TYPE='staked' and date=CURRENT_DATE),
main as(
SELECT r.address,rank_right_now,balance_right_now,rank_start_of_year,balance_start_of_year
FROM right_now r full join start_of_the_year s on r.address=s.address
WHERE rank_right_now<=100
order by rank_right_now asc)
SELECT
case
WHEN balance_start_of_year is not null then 'At the beginning of the year, They had a balance.'
WHEN rank_start_of_year is null then 'At the beginning of the year, They did not have a balance.'
end as Condition,
count(condition) as number
from main
GROUP BY 1