/* "What is the distribution of RUNE held by accounts that have been active in the last week/1 months/3months/6months?" Not including network modules or treasury addresses. For latestblock: SELECT MAX(block_id) AS latestblock FROM thorchain.transfer_events Assumptions: 14400 blocks per day. (Mimir 'Churninterval'/3 and 'FullImpLossProtectionBlocks'/100) 30 days per month. A single entity can split RUNE between multiple addresses (as in a Sybil attack), so the representation used for RUNE distribution is 'amount of RUNE in addresses over a certain balance'. Keep in mind that Flipside tables still have some rows missing (where identical rows should be present), here giving the impression of some negative current balances. */ WITH detailstable AS ( SELECT 5140274 AS latestblock ), networkmodules AS ( SELECT 'thor1v8ppstuf6e3x0r4glqc68d5jqcs2tf38cg2q6y' AS minter_module, 'thor1g98cy3n9mmjrpn0sxmn63lztelera37n8n67c0' AS pool_module, 'thor17gw75axcnr8747pkanye45pnrwk7p9c3cqncsv' AS bond_module, 'thor1dheycdevq39qlkxs2a6wuuzyn4aqxhve4qxtxt' AS reserve_module, 'thor1lj62pg6ryxv2htekqx04nv7wd3g98qf9gfvamy' AS standby_reserve, 'thor1lrnrawjlfp6jyrzf39r740ymnuk9qgdgp29rqv' AS team, 'thor16qnm285eez48r4u9whedq4qunydu2ucmzchz7p' AS seed, 'thor1505gp5h48zd24uexrfgka70fg8ccedafsnj0e3' AS treasury1, 'thor14n2q7tpemxcha8zc26j0g5pksx4x3a9xw9ryq9' AS treasury2, 'thor1egxvam70a86jafa8gcg3kqfmfax3s0m2g3m754' AS treasury_lp, 'thor1wfe7hsuvup27lx04p5al4zlcnx6elsnyft7dzm' AS unlabelled1, 'thor1q6mhk70ecvnmw3ekawm5pr8wty509hcqug65gt' AS unlabelled2, 'thor1qwuruyqmge8uwr2z00e7sdga4p3s7gp0nycgt8' AS unlabelled3 ), lastactive AS ( SELECT from_address AS address, MAX(block_id) AS last_active_block, MAX(block_timestamp) as last_active_timestamp --'from_address' only in order to select by activity, rather than passive receiving. FROM detailstable, thorchain.transfer_events WHERE (block_id <= latestblock) --'latestblock' for consistent results, and this '<=' to not ignore address active after it. AND (SELECT CONCAT(*) FROM networkmodules) NOT LIKE CONCAT('%', address, '%') GROUP BY from_address ), currentbalances AS ( SELECT address, SUM(balance_change) AS current_balance FROM ( ( SELECT to_address AS address, SUM(POWER(10,-8) * amount_e8) AS balance_change FROM thorchain.transfer_events GROUP BY to_address ) UNION ALL ( SELECT from_address AS address, SUM(-1 * POWER(10,-8) * amount_e8) AS balance_change FROM thorchain.transfer_events GROUP BY from_address ) ) WHERE (SELECT CONCAT(*) FROM networkmodules) NOT LIKE CONCAT('%', address, '%') GROUP BY address ), joined AS ( SELECT lastactive.*, current_balance FROM (lastactive LEFT JOIN currentbalances ON lastactive.address = currentbalances.address) ), --<1000, 1001_10000, 10001_100000, 100001_1000000, 1000001_10000000 ranges AS ( SELECT (CASE WHEN current_balance <=1000 THEN '(1) <=1000' WHEN current_balance <=10000 THEN '(2) 1,001-10,000' WHEN current_balance <=100000 THEN '(3) 10,001-100,000' WHEN current_balance <=1000000 THEN '(4) 100,001-1,000,000' WHEN current_balance <=10000000 THEN '(5) 1,000,001-10,000,000' ELSE '(6) >10,000,000' END) AS balance_range, --Even when ordered by balance_order, Flipside does not display the bars in the same order, thus the '()' necessity. (CASE WHEN current_balance <=1000 THEN 1 WHEN current_balance <=10000 THEN 2 WHEN current_balance <=100000 THEN 3 WHEN current_balance <=1000000 THEN 4 WHEN current_balance <=10000000 THEN 5 ELSE 6 END) AS balance_order, 1 AS all_addresses, (CASE WHEN last_active_block BETWEEN (latestblock - 14400 * 30 * 6) AND latestblock THEN 1 ELSE 0 END) AS active_in_last_six_months, (CASE WHEN last_active_block BETWEEN (latestblock - 14400 * 30 * 3) AND latestblock THEN 1 ELSE 0 END) AS active_in_last_three_months, (CASE WHEN last_active_block BETWEEN (latestblock - 14400 * 30 * 1) AND latestblock THEN 1 ELSE 0 END) AS active_in_last_month, (CASE WHEN last_active_block BETWEEN (latestblock - 14400 * 7) AND latestblock THEN 1 ELSE 0 END) AS active_in_last_week FROM detailstable, joined ) SELECT balance_range, SUM(all_addresses) AS all_addresses, SUM(active_in_last_six_months) AS active_in_last_six_months, SUM(active_in_last_three_months) AS active_in_last_three_months, SUM(active_in_last_month) AS active_in_last_month, SUM(active_in_last_week) AS active_in_last_week FROM ranges GROUP BY balance_range, balance_order ORDER BY balance_order ASC

2022-04-16 Histogram of RUNE balances in recently-active addresses.