HiveSQL - Querying to find out HIVE Coming IN [Users] & Going OUT [Exchanges]

Hey All,

This time around I wanted to leverage HiveSQL to get me information around #HIVE that the users are bring IN i.e. are buying HIVE and at the same time checking on users who are withdrawing HIVE. For this I have used the TxTransfers table that performs the following Blockchain operations

• transfer
• transfer_to_vesting
• transfer_to_savings
• transfer_from_savings
• cancel_transfer_from_savings

And here is the image of the table taken from HiveSql docs page.

Now coming to the queries part, the first query I triggered was to get me an idea on the users, recipient and total hive withdrawn. I now wanted to put some limitations in terms of eliminating exchanges coming into picture like ""Binance, MEXC & Gateio" not these involved.

Top 50 users withdrawing hive...

WITH WithdrawalSummary AS (
    SELECT 
        [from] AS [user],  
        [to] AS recipient, 
        SUM(amount) AS total_hive_withdrawn
    FROM TxTransfers
    WHERE amount_symbol = 'HIVE'  
    AND type = 'transfer'  
    AND timestamp >= DATEADD(DAY, -7, GETUTCDATE())  -- Filters last 7 days
    GROUP BY [from], [to]
)
SELECT TOP 50 [user], recipient, total_hive_withdrawn
FROM WithdrawalSummary
ORDER BY total_hive_withdrawn DESC;

Refined with users sending HIVE to exchange like - "Binance, MEXC & Gateio" restricting users not being an exchange...

WITH WithdrawalSummary AS (
    SELECT 
        [from] AS [user],  
        [to] AS recipient, 
        SUM(amount) AS total_hive_withdrawn
    FROM TxTransfers
    WHERE amount_symbol = 'HIVE'  
    AND type = 'transfer'  
    AND timestamp >= DATEADD(DAY, -7, GETUTCDATE())  -- check for last 7 days
    AND [from] NOT IN ('mxchive', 'mxsteem', 'keychain', 'bdhivesteemcash', 'honey-swap', 'hbdstabilizer', 'huobi-pro', 'bdhivesteem', 'gateiohivesteem', 'huobi-withdrawal', 'binance-hot2', 'bdhive-steem')  -- Exclude these exchanges
    AND [from] NOT LIKE 'hot[0-9]%'  -- Excludes users starting with 'hot' followed by a number
    AND [from] NOT LIKE 'uid[0-9]%'  -- Excludes users starting with 'uid' followed by numbers
    AND [from] NOT LIKE 'user%'  -- Excludes users starting with 'user'
    GROUP BY [from], [to]
)
SELECT TOP 100 [user], recipient, total_hive_withdrawn
FROM WithdrawalSummary
ORDER BY total_hive_withdrawn DESC;

Output of the Query; Top 100 users withdrawing HIVE...

I know there can be further refinement to the query to eliminate more exchanges but I guess we get some idea on the users that has been withdrawing #HIVE in the last 7 days. And before I end this post I also wanted to check on users bringing #HIVE not counting in the exchanges involved to an extent. Here is the query I triggered.

WITH ExchangePayouts AS (
    SELECT 
        [from] AS exchange_account,  
        [to] AS [user],  
        SUM(amount) AS total_hive_received
    FROM TxTransfers
    WHERE amount_symbol = 'HIVE'  
    AND type = 'transfer'  
    AND timestamp >= DATEADD(DAY, -15, GETUTCDATE())  -- Filters last 7 days
    AND [from] IN ('mxchive', 'bdhivesteem', 'gateiohivesteem')  -- Exchanges sending HIVE
    AND [to] NOT IN ('binance-hot2', 'binance-hot1', 'huobi-pro', 'bitgethive', 'bdhivesteem')  -- Ignoring specific users
    AND [to] NOT LIKE 'htx%'   -- Excludes usernames starting with 'htx'
    AND [to] NOT LIKE 'uid[0-9]%'   -- Excludes 'uid' followed by numbers
    AND [to] NOT LIKE 'bitmart[0-9]%'  -- Excludes 'bitmart' followed by numbers
    AND [to] NOT LIKE 'mxc%' -- Excludes 'mxc' patterns like mxccommxchive, mxchive, mxcsteem, mxchivemxcsteem and any others
    GROUP BY [from], [to]
)
SELECT TOP 100 exchange_account, [user], total_hive_received
FROM ExchangePayouts
ORDER BY total_hive_received DESC;

And here is the output screen shot taken from DBeaver UI; not getting into formatting it for now as I see the query further needs some refinement in terms of eliminating exchanges involved.

Well this should be it for todays post on learning and triggering HiveSQL queries - "HiveSQL - Querying to find out HIVE Coming IN [Users] & Going OUT [Exchanges]". Let me know your views and if you have any other approach in getting to these numbers. Happy Learning... cheers

HiveSQL - Querying Json_metadata with App = "Ecency" - Figuring Out Active Users Number by Post & Comment Numbers....

#hivesql #DBeaver #learning #hivestatistics #data #datamining #hiveaccount #liquidhive #DynamicGlobalProperties #ecency #TxComments

Refer my previous posts around querying HiveSql::

• HiveSQL - Querying Json_metadata with App = "Ecency" - Figuring Out Active Users Number by Post & Comment Numbers....
• HiveSQL - Top 50 #HIVE Reputable Users - HIVE POWER & #HBD in Savings they #HODL...
• Top 50 accounts #HOLDING Liquid HIVE - Eliminated Accounts having more than a Million #HIVE...

image source:: hive.io, hive x, canva

Best Regards
Paras