with withdraw as ( with maintable as ( select a.BLOCK_TIMESTAMP, a.TX_ID, b.Authorizers[0]::string, b.PAYER, a.EVENT_DATA:amount as amount, a.EVENT_DATA:from as "User" from flow.core.fact_events a join flow.core.fact_transactions b on a.tx_id = b.tx_id where a.EVENT_CONTRACT = 'A.1654653399040a61.FlowToken' and a.EVENT_TYPE = 'TokensWithdrawn' and a.TX_SUCCEEDED = true) select "User", count(distinct TX_ID) as "Number of Withdrawal Transactions", count(*) as "Number of Withdrawal Events", sum(amount) as "Withdrawals Volume" from maintable group by 1), deposit as ( with maintable as ( select a.BLOCK_TIMESTAMP, a.TX_ID, b.Authorizers[0]::string, b.PAYER, a.EVENT_DATA:amount as amount, a.EVENT_DATA:to as "User" from flow.core.fact_events a join flow.core.fact_transactions b on a.tx_id=b.tx_id where a.EVENT_CONTRACT = 'A.1654653399040a61.FlowToken' and a.EVENT_TYPE = 'TokensDeposited' and a.TX_SUCCEEDED = true) select "User", count(distinct TX_ID) as "Number of Deposit Transactions", count(*) as "Number of Deposit Events", sum(amount) as "Deposits Volume" from maintable group by 1), maindata as ( select a."User", "Number of Deposit Transactions", "Number of Deposit Events", "Number of Withdrawal Transactions", "Number of Withdrawal Events", "Deposits Volume", "Withdrawals Volume", ("Deposits Volume" - "Withdrawals Volume") as "FLOW Balance of The Wallet", row_number () over (order by "FLOW Balance of The Wallet" desc) as "Rank" from withdraw a join deposit b on a."User"=b."User" order by 8 desc) select case when "FLOW Balance of The Wallet" > 0 and "FLOW Balance of The Wallet" < 1 then 'Less than 1 FLOW' when "FLOW Balance of The Wallet" >= 1 and "FLOW Balance of The Wallet" <= 5 then '1 - 5 FLOW' when "FLOW Balance of The Wallet" > 5 and "FLOW Balance of The Wallet" <= 10 then '6 - 10 FLOW' when "FLOW Balance of The Wallet" > 10 and "FLOW Balance of The Wallet" <= 25 then '11 - 25 FLOW' when "FLOW Balance of The Wallet" > 25 and "FLOW Balance of The Wallet" <= 50 then '26 - 50 FLOW' when "FLOW Balance of The Wallet" > 50 and "FLOW Balance of The Wallet" <= 100 then '51 - 100 FLOW' when "FLOW Balance of The Wallet" > 100 and "FLOW Balance of The Wallet" <= 250 then '101 -250 FLOW' when "FLOW Balance of The Wallet" > 250 and "FLOW Balance of The Wallet" <= 500 then '251 - 500 FLOW' when "FLOW Balance of The Wallet" > 500 and "FLOW Balance of The Wallet" <= 1000 then '501 - 1000 FLOW' when "FLOW Balance of The Wallet" > 1000 and "FLOW Balance of The Wallet" <= 10000 then '1001 - 10000 FLOW' when "FLOW Balance of The Wallet" > 10000 then 'More Than 10000 FLOW' end as "Wallet Balance Level", count(distinct "User") as "Number of Holders" from maindata group by 1 order by 1

[Flow] - Who's Got The Flow?