WITH proposaltx AS ( SELECT tx_id FROM cosmos.core.fact_msg_attributes WHERE ATTRIBUTE_KEY= 'proposal_id' AND ATTRIBUTE_VALUE= '848' AND tx_succeeded = 'TRUE' ), voter AS ( SELECT block_timestamp, tx_id, attribute_value AS Voter FROM cosmos.core.fact_msg_attributes WHERE ATTRIBUTE_KEY= 'spender' AND tx_id IN (SELECT tx_id FROM proposaltx) AND tx_succeeded = 'TRUE' ), mergetable AS ( SELECT t2.block_timestamp, t1.tx_id, Voter FROM proposaltx t1 JOIN voter t2 ON t1.tx_id = t2.tx_id ), DailyReceived AS ( SELECT DATE(block_timestamp) as day, receiver, SUM(amount/1e6) AS daily_received_volume FROM cosmos.core.fact_transfers WHERE transfer_type IN ('COSMOS', 'IBC_TRANSFER_IN') AND tx_succeeded = 'TRUE' AND currency = 'uatom' GROUP BY DATE(block_timestamp), receiver ), DailySent AS ( SELECT DATE(block_timestamp) as day, sender, SUM(amount/1e6) AS daily_sent_volume FROM cosmos.core.fact_transfers WHERE transfer_type IN ('COSMOS', 'IBC_TRANSFER_OUT') AND tx_succeeded = 'TRUE' AND currency = 'uatom' GROUP BY DATE(block_timestamp), sender ), DailyBalance AS ( SELECT COALESCE(dr.day, ds.day) AS day, COALESCE(dr.receiver, ds.sender) AS atom_user, COALESCE(daily_received_volume, 0) - COALESCE(daily_sent_volume, 0) as daily_balance_change FROM DailyReceived dr FULL OUTER JOIN DailySent ds ON dr.day = ds.day AND dr.receiver = ds.sender ), CumulativeBalance AS ( SELECT day, atom_user, SUM(daily_balance_change) OVER(PARTITION BY atom_user ORDER BY day) AS cumulative_balance FROM DailyBalance WHERE atom_user IN (SELECT voter FROM mergetable) ) SELECT direction AS balance_change_direction, CASE WHEN ABS(total_balance_change) < 1 THEN 'Less Than 1 $ATOM' WHEN ABS(total_balance_change) >= 1 AND ABS(total_balance_change) < 10 THEN '1 - 10 $ATOM' WHEN ABS(total_balance_change) >= 10 AND ABS(total_balance_change) < 100 THEN '10 - 100 $ATOM' WHEN ABS(total_balance_change) >= 100 AND ABS(total_balance_change) < 1000 THEN '100 - 1000 $ATOM' WHEN ABS(total_balance_change) >= 1000 AND ABS(total_balance_change) < 10000 THEN '1000 - 10000 $ATOM' ELSE 'More Than 10000 $ATOM' END AS balance_change_category, COUNT(DISTINCT atom_user) AS number_of_wallets FROM ( SELECT atom_user, SUM(cumulative_balance) AS total_balance_change, CASE WHEN SUM(cumulative_balance) < 0 THEN 'Decrease' ELSE 'Increase' END AS direction FROM CumulativeBalance GROUP BY atom_user ) AS WalletBalanceChange GROUP BY direction, CASE WHEN ABS(total_balance_change) < 1 THEN 'Less Than 1 $ATOM' WHEN ABS(total_balance_change) >= 1 AND ABS(total_balance_change) < 10 THEN '1 - 10 $ATOM' WHEN ABS(total_balance_change) >= 10 AND ABS(total_balance_change) < 100 THEN '10 - 100 $ATOM' WHEN ABS(total_balance_change) >= 100 AND ABS(total_balance_change) < 1000 THEN '100 - 1000 $ATOM' WHEN ABS(total_balance_change) >= 1000 AND ABS(total_balance_change) < 10000 THEN '1000 - 10000 $ATOM' ELSE 'More Than 10000 $ATOM' END ORDER BY balance_change_direction, balance_change_category;

Query	Creator	Created
dis cosmos ibc	rarma	Nov 12, 2023
Learning cosmos ibc	rarma	Aug 7, 2023
Distribution of Proposal #82 Voters By Their $ATOM Balance cosmos ibc	rarma	Nov 12, 2023
main 1 cosmos ibc	rarma	Nov 12, 2023
Cosmos Voter Balance Over time cosmos ibc	rarma	Aug 7, 2023

rarma