WITH accounts AS (
SELECT * FROM flipside_prod_db.algorand.account
), blocks AS (
SELECT * FROM flipside_prod_db.algorand.block
), main AS (
SELECT
accounts.ADDRESS AS address,
blocks.BLOCK_TIMESTAMP AS created_at,
accounts.BALANCE AS balance,
accounts.ACCOUNT_DATA AS metadata
FROM accounts, blocks
WHERE blocks.BLOCK_ID = accounts.CREATED_AT
AND accounts.ACCOUNT_CLOSED = FALSE
ORDER BY created_at DESC
), may_wallets AS (
SELECT COUNT(ADDRESS) AS cnt FROM main
WHERE created_at > '2022-05-01' AND created_at < '2022-06-01'
), may_wallets_one_algo AS (
SELECT COUNT(ADDRESS) AS cnt FROM main
WHERE created_at > '2022-05-01' AND created_at < '2022-06-01'
AND balance > 1
)
SELECT
'Distribution',
may_wallets.cnt AS may_wallets,
may_wallets_one_algo.cnt AS may_wallets_with_more_than_one_algo,
may_wallets - may_wallets_with_more_than_one_algo AS other_wallets,
may_wallets_with_more_than_one_algo/may_wallets * 100 AS percent
/* CONCAT(cnt_2/cnt_1 * 100, '%') AS percent */
FROM may_wallets, may_wallets_one_algo
/* POSIX converter
AND dateadd(s, 1654041600, '1970-01-01') > created_at > dateadd(s, 1651363200, '1970-01-01')
*/