with CGF_COLLECTION as ( select ASSET_ID, ASSET_NAME from flipside_prod_db.algorand.asset where CREATOR_ADDRESS in ('GOOSECHXVEKJ4SO43NTW5HXOIGLFGC2SQDAVWQGJCN576ODJ5SECV6MUOM', 'GOOSE7PN4S366W5LLQ3TRO4BCB2C66VBSMMXRVAWAPGZJHJYR34VNK2AU4', 'GOOSEOQSO2XM54KNCN2ESH3A7VCHRFSCFMACE24QLBUGCR256JOGRCYSSI', 'GOOSEKPIKOZPEPBMFO7TRRR2EPXLWKOIBLKXJKXWMK2J56SOXWRC3FLNSU', 'GOOSE4NW53JZLCG6NX37WWPUAOOYSAHPVPPEMBI7FGZZBZ4OET27JV4O3U') and ASSET_DELETED = 'FALSE' ), latest_tx as ( select ASSET_RECEIVER, ASSET_ID, rank() over (partition by ASSET_ID order by BLOCK_TIMESTAMP desc) as latest_rx from flipside_prod_db.algorand.asset_transfer_transaction where ASSET_ID in (select ASSET_ID from CGF_COLLECTION) and BLOCK_TIMESTAMP < '2022-06-09' ), latest_holder as ( select ASSET_RECEIVER as HOLDER, count(distinct ASSET_ID) as NUM_CGF_HOLD from latest_tx where latest_rx = 1 group by 1 ), raw_table as ( select ADDRESS, BALANCE as BALANCE_ALGO, NUM_CGF_HOLD, CREATED_AT, DATEDIFF(day, BLOCK_TIMESTAMP, CURRENT_DATE) as WALLET_LIFETIME_DAY, case when BALANCE_ALGO < 1 then '1. Balance <= 1 ALGO' when BALANCE_ALGO > 1 and BALANCE_ALGO <= 10 then '2. 1 ALGO < Balance <= 10 ALGO' when BALANCE_ALGO > 10 and BALANCE_ALGO <= 100 then '3. 10 ALGO < Balance <= 100 ALGO' when BALANCE_ALGO > 100 and BALANCE_ALGO <= 1000 then '4. 100 ALGO < Balance <= 1K ALGO' when BALANCE_ALGO > 1000 and BALANCE_ALGO <= 10000 then '5. 1K ALGO < Balance <= 10K ALGO' when BALANCE_ALGO > 10000 and BALANCE_ALGO <= 100000 then '6. 10K ALGO < Balance <= 100K ALGO' when BALANCE_ALGO > 100000 then '7. Balance > 100K ALGO' end as type from flipside_prod_db.algorand.account as a inner join latest_holder as h on a.ADDRESS = h.HOLDER inner join flipside_prod_db.algorand.block as b on a.CREATED_AT = b.BLOCK_ID order by 2 desc ) -- select -- count(case when BALANCE_ALGO < 1 then 1 end) as "1. Balance <= 1 ALGO", -- count(case when BALANCE_ALGO > 1 and BALANCE_ALGO <= 10 then 1 end) as "2. 1 ALGO < Balance <= 10 ALGO", -- count(case when BALANCE_ALGO > 10 and BALANCE_ALGO <= 100 then 1 end) as "3. 10 ALGO < Balance <= 100 ALGO", -- count(case when BALANCE_ALGO > 100 and BALANCE_ALGO <= 1000 then 1 end) as "4. 100 ALGO < Balance <= 1K ALGO", -- count(case when BALANCE_ALGO > 1000 and BALANCE_ALGO <= 10000 then 1 end) as "5. 1K ALGO < Balance <= 10K ALGO", -- count(case when BALANCE_ALGO > 10000 and BALANCE_ALGO <= 1000000 then 1 end) as "6. 10K ALGO < Balance <= 100K ALGO", -- count(case when BALANCE_ALGO > 100000 then 1 end) as "7. Balance > 100K ALGO" -- from raw_table select type, count(*) as total_wallet from raw_table group by 1

Algorand Bounties: Crazy Goose Flock and $CRUMB Token