with adr as (select
ADDRESS
FROM flipside_prod_db.algorand.account
where ACCOUNT_CLOSED = 'FALSE' and balance > 1 ),
new_ad as (select
address
from
(select
*,
BLOCK_TIMESTAMP
from flipside_prod_db.algorand.block b , flipside_prod_db.algorand.account a
where b.BLOCK_ID = a.CREATED_AT
and BLOCK_TIMESTAMP ilike '2022-05%'
and ACCOUNT_CLOSED = 'FALSE' )
where
address in (select address from adr))
select
case
when balance > 0 and balance <= 50 then' balance 1-50'
when balance >50 and balance <= 100 then ' balance 50-100'
when balance >100 and balance <= 500 then 'balance 100-500'
when balance >500 and balance <= 1000 then' balance 500-1000'
when balance >1000 and balance <= 10000 then ' balance 1K-10K'
when balance >10000 and balance <= 100000 then ' balance 10K-100K'
when balance >100000 then ' balance over 100K' end as tier,
count(distinct address) as address_count
from flipside_prod_db.algorand.account
where ACCOUNT_CLOSED = 'FALSE' and address in (select address from new_ad)
group by 1