WITH tx1 AS ( SELECT event_from AS address, COUNT(tx_id) AS n_txn FROM terra.transfers WHERE block_timestamp > CURRENT_DATE - interval '90 days' GROUP BY 1 UNION SELECT event_to AS address, COUNT(tx_id) AS n_txn FROM terra.transfers WHERE block_timestamp > CURRENT_DATE - interval '90 days' GROUP BY 1 UNION SELECT delegator_address, COUNT(tx_id) AS n_txn FROM terra.staking where block_timestamp > CURRENT_DATE - interval '90 days' GROUP BY 1 UNION SELECT msg_value:to_address::string AS sender, COUNT(tx_id) AS n_txn FROM terra.msgs WHERE msg_value:from_address::string = 'terra13yxhrk08qvdf5zdc9ss5mwsg5sf7zva9xrgwgc' AND msg_value:amount[0]:denom::string = 'uluna' AND tx_status = 'SUCCEEDED' AND msg_value:amount[0]:amount::string/pow(10,6) > 0 GROUP BY 1 UNION SELECT msg_value:from_address::string as sender, COUNT(tx_id) as n_txn FROM terra.msgs where msg_value:to_address::string = 'terra13yxhrk08qvdf5zdc9ss5mwsg5sf7zva9xrgwgc' AND msg_value:amount[0]:denom::string = 'uluna' AND tx_status = 'SUCCEEDED' AND msg_value:amount[0]:amount::string/pow(10,6) > 0 GROUP BY 1 UNION ( WITH msge AS ( SELECT coalesce(event_attributes:sender,event_attributes:"0_sender") as address, COUNT(tx_id) AS n_txn FROM terra.msg_events where block_timestamp > CURRENT_DATE - interval '90 days' GROUP BY 1), msgs AS ( SELECT msg_value:sender as address, COUNT(distinct(tx_id)) as n_txn_msgs FROM terra.msgs WHERE block_timestamp > CURRENT_DATE - interval '90 days' GROUP BY 1 ) SELECT coalesce(msgs.address,msge.address) as address, coalesce(msgs.n_txn_msgs, msge.n_txn) as n_txn FROM msge FULL JOIN msgs ON msge.address = msgs.address ) ), users AS ( SELECT address, sum(n_txn) AS nr_transactions FROM tx1 GROUP BY address ), balances AS ( SELECT address as keepers, balance FROM terra.daily_balances WHERE currency='LUNA' AND balance>0 AND DATE(date) = CURRENT_DATE() - 1 and address_label_type is null ), sum AS ( SELECT address, balance FROM users LEFT JOIN balances ON users.address=balances.keepers WHERE balance>0 ), sum1 AS ( SELECT CASE --WHEN balance>0 THEN 'SUM' WHEN 0<=balance<10 THEN '0-10 LUNA' WHEN 10<=balance<100 THEN '10-100 LUNA' WHEN 100<=balance<500 THEN '100-500 LUNA' WHEN 500<=balance<1000 THEN '500-1000 LUNA' WHEN 1000<=balance<5000 THEN '1000-5000 LUNA' WHEN 5000<=balance<10000 THEN '5000-10000 LUNA' WHEN 10000<=balance<50000 THEN '10000-50000 LUNA' WHEN 50000<=balance<100000 THEN '50000-100000 LUNA' WHEN 100000<=balance<1000000 THEN '100000-1000000 LUNA' WHEN 1000000<=balance<10000000 THEN '100000-1000000 LUNA' WHEN balance>=10000000 THEN '1000000+ LUNA' ELSE NULL END AS level1, balance /* CASE WHEN balance>0 THEN sum(balance) WHEN 01000000 THEN 'sum(balance)' END AS amount */ FROM sum ) SELECT level1 AS Group_type, sum(balance) AS Luna_balance FROM sum1 where group_type is not null GROUP BY 1 ORDER BY 2 DESC

LUNA Distribution, Mvmt II