WITH swapper_addresses as ( SELECT from_address as swapper_address FROM thorchain.swaps WHERE block_timestamp > date('2021-11-17T00:00:00Z') --AND from_asset = 'THOR.RUNE' AND blockchain = 'THOR' GROUP BY from_address ), LP_remover_addresses as ( SELECT from_address as unique_remove_LPers FROM thorchain.liquidity_actions WHERE lp_action = 'remove_liquidity' AND block_timestamp > date('2021-11-17T00:00:00Z') GROUP BY from_address ), all_remover_addresses as ( SELECT swapper_address as wallet_address FROM swapper_addresses UNION ALL SELECT unique_remove_LPers as wallet_address FROM LP_remover_addresses ), all_remover_distinct_addresses as ( SELECT DISTINCT wallet_address FROM all_remover_addresses ), received as ( SELECT to_address, SUM(rune_amount) as rune_amount_received FROM thorchain.transfers WHERE to_address in (SELECT wallet_address FROM all_remover_distinct_addresses) AND block_timestamp < date('2021-11-16T00:00:00Z') GROUP BY to_address ), sent as ( SELECT from_address, SUM(rune_amount) as rune_amount_sent FROM thorchain.transfers WHERE from_address in (SELECT wallet_address FROM all_remover_distinct_addresses) AND block_timestamp < date('2021-11-16T00:00:00Z') GROUP BY from_address ), distribution as ( SELECT from_address as wallet_address, rune_amount_received - rune_amount_sent as total_balance, CASE WHEN total_balance > 100000 THEN 'g. 100k RUNE and above' WHEN total_balance > 10000 AND total_balance <= 100000 THEN 'f. 10k to 100k RUNE' WHEN total_balance > 1000 AND total_balance <= 10000 THEN 'e. 1k to 10k RUNE' WHEN total_balance > 100 AND total_balance <= 1000 THEN 'd. 100 to 1000 RUNE' WHEN total_balance > 10 AND total_balance <= 100 THEN 'c. 10 to 100 RUNE' WHEN total_balance > 1 AND total_balance <= 10 THEN 'b. 1 to 10 RUNE' WHEN total_balance <= 1 THEN 'a. Lesser than 1 RUNE' ELSE 'null' END as category FROM received LEFT JOIN sent ON from_address = to_address WHERE from_address is not NULL ), received_after as ( SELECT to_address, SUM(rune_amount) as rune_amount_received FROM thorchain.transfers WHERE to_address in (SELECT wallet_address FROM all_remover_distinct_addresses) GROUP BY to_address ), sent_after as ( SELECT from_address, SUM(rune_amount) as rune_amount_sent FROM thorchain.transfers WHERE from_address in (SELECT wallet_address FROM all_remover_distinct_addresses) GROUP BY from_address ), distribution_after as ( SELECT from_address as wallet_address2, rune_amount_received - rune_amount_sent as total_balance, CASE WHEN total_balance > 100000 THEN 'g. 100k RUNE and above' WHEN total_balance > 10000 AND total_balance <= 100000 THEN 'f. 10k to 100k RUNE' WHEN total_balance > 1000 AND total_balance <= 10000 THEN 'e. 1k to 10k RUNE' WHEN total_balance > 100 AND total_balance <= 1000 THEN 'd. 100 to 1000 RUNE' WHEN total_balance > 10 AND total_balance <= 100 THEN 'c. 10 to 100 RUNE' WHEN total_balance > 1 AND total_balance <= 10 THEN 'b. 1 to 10 RUNE' WHEN total_balance <= 1 THEN 'a. Lesser than 1 RUNE' ELSE 'null' END as category FROM received_after LEFT JOIN sent_after ON from_address = to_address WHERE from_address is not NULL ), count_after as ( SELECT category, count(wallet_address2) as wallet_after FROM distribution_after GROUP BY category ) SELECT distribution.category, count(wallet_address) as "Before", avg(wallet_after) as "After" FROM distribution LEFT JOIN count_after ON count_after.category = distribution.category GROUP BY distribution.category

22. [THORChain] Network Shutdown