with base as ( SELECT sender, tx_id, block_timestamp, TRY_BASE64_DECODE_STRING(TX_MESSAGE:txn:note) as msg, REGEXP_SUBSTR( TRY_BASE64_DECODE_STRING(TX_MESSAGE:txn:note), '\\:\\s?(\\d+)', 1, 1, 'e', 1) as num_value, TO_NUMBER(last_value(REGEXP_SUBSTR( TRY_BASE64_DECODE_STRING(TX_MESSAGE:txn:note), '\\:\\s?(\\d+)', 1, 1, 'e', 1) ) OVER (PARTITION BY sender order by block_id asc))/1e6 as final_commit, last_value(block_timestamp) OVER (PARTITION BY sender order by block_id asc) as final_bs, CASE WHEN num_value IS NULL THEN msg ELSE NULL END as error from algorand.payment_transaction WHERE TRY_BASE64_DECODE_STRING(TX_MESSAGE:txn:note) LIKE 'af/gov1:j{"com%' AND RECEIVER ='57QZ4S7YHTWPRAM3DQ2MLNSVLAQB7DTK4D7SUNRIEFMRGOU7DMYFGF55BY' AND (block_timestamp>='2021-12-24' AND block_timestamp<'2022-01-08') ), refine as ( SELECT MAX(date_trunc('day', final_bs)) as ddate, MAX(final_commit) as fcommit, sender from base GROUP BY sender ), pre_final as ( SELECT sender, ddate, fcommit, balance, CASE WHEN balance<1 THEN '<1' WHEN balance>=1 AND balance<10 THEN '1-10' WHEN balance>=10 AND balance<100 THEN '10-100' WHEN balance>=100 AND balance<1000 THEN '100-1K' WHEN balance>=1000 AND balance<10000 THEN '1K-10k' WHEN balance>=10000 AND balance<100000 THEN '10K-100k' WHEN balance>=100000 AND balance<1000000 THEN '100K-1M' WHEN balance>=1000000 AND balance<10000000 THEN '1M-10M' WHEN balance>=10000000 AND balance<100000000 THEN '10M-100M' WHEN balance>=100000000 AND balance<1000000000 THEN '100M-1B' WHEN balance>=1000000000 THEN '>=1B' END as category from refine LEFT JOIN algorand.account acc ON refine.sender = acc.address WHERE balance>=fcommit ) SELECT AVG((fcommit/balance) * 100) as avg_pct, COUNT(DISTINCT sender) as wallets, category from pre_final WHERE balance!=0 GROUP BY category

[ALGO][Q19] Governance Period 2 Initial Commitments