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

in Hive Statistics • 9 days ago (edited)

Hey All,

This time around I wanted to leverage HiveSQLTxTransfers table that performs the following Blockchain operations 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

  • 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...

SenderReceiverAmount
bithumbrecv2bithumbsend2493284.1640
bithumbsend2user.dunamu401507.5800
bithumbsend2bdhivesteem330571.8100
steemmonsterssteemmonsters154738.8570
bitmart306mxchive125144.6400
cybercitybdhivesteem117300.6430
splinterlandscryptoeater100000.0000
sl-swaphoney-swap100000.0000
steemmonsterssl-swap100000.0000
vicemiamicybercity93873.1210
cryptoeaterbdhivesteem92070.2200
mxccommxchivemxchive80716.4180
bitgethiveuser.dunamu70379.9800
rez11rev09mxchive64610.5200
bitgethivegateiodeposit60933.9900
bdhivesteemfreebitgethive56592.4750
josepimpobdhivesteem53917.7520
euservermxchive51969.8440
michael2024bdhivesteem50662.6710
mxchivemxcsteemmxchive46767.7710
hanvhoney-swap44050.0000
hivetestfreemxchive43682.5400
bitgethivegateiohivesteem43055.7790
exchangeinxchangeout42755.6580
id532984494huobi-pro41367.7810
vsc.networkbdhivesteem40800.0000
htx-9tuqx5jggateiohivesteem40547.7060
reazuliqbalbdhivesteem34378.3860
token-converterhoney-swap34013.8150
hivelotcj300032584.9400
cj3000mxchive32000.0000
hivenetwork1mxchive31993.0900
ghlqh3tkdb4qnsghlhivethumb30220.6990
bitgethivebdhive-steem27997.8230
abh12345hiveswap24543.0000
bitgethivebltgetxpr21593.9740
bltgetxprbitgethive21570.3550
detlevbdhivesteem21500.0000
orinocobdhivesteem19723.2330
bitgethivemxsteem19559.4580
eddiespinobdhivesteem18932.1140
danieljustohiveswap18281.8460
htx-9tuqx5jgmxchive18249.1580
id536934172bdhivesteem17200.0000
id532984494id53693417217146.7340
therealymemxchive17033.0190
danieljustobdhivesteem17015.0000
keychain.swapkeychain16652.4900
vihanbdhivesteem16569.9320
bitgethivexpr-hive16497.2460
xpr-hivebitgethive16497.2460
hivecoinfreebdhivesteem16200.0000
uswapkeychain.swap16153.0220
bluemistappreciator15379.0000
peakdbdhivesteem15000.0000
cryptoeaterhiveswap15000.0000
hive-enginehoney-swap15000.0000
steem.futureuser.dunamu14832.8360
lovejillbdhivesteem14310.4640
hivetestfreehuobi-pro14147.1300
bltgetxprmxchive13428.2740
htx-9tuqx5jgbltgetxpr13411.9570
hiveswapdanieljusto12996.2720
liucixinbdhivesteem12792.7990
fabiyamadabdhivesteem12074.4400

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::

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

Best Regards
Paras

#hivesql #learning #lefinance #neoxian #hodl #cent #indiaunited #hive #exchanges
9 days ago in Hive Statistics by
$16.88
  • Past Payouts $16.88
  • - Author $8.45
  • - Curators $8.43
199 votes
Reply 4
Sort:  
  • Trending
    • Loading...
    [-]
     9 days ago  

    Discord Server.This post has been manually curated by @steemflow from Indiaunited community. Join us on our

    Do you know that you can earn a passive income by delegating to @indiaunited. We share more than 100 % of the curation rewards with the delegators in the form of IUC tokens. HP delegators and IUC token holders also get upto 20% additional vote weight.

    Here are some handy links for delegations: 100HP, 250HP, 500HP, 1000HP.

    image.png

    100% of the rewards from this comment goes to the curator for their manual curation efforts. Please encourage the curator @steemflow by upvoting this comment and support the community by voting the posts made by @indiaunited..

    This post received an extra 10.00% vote for delegating HP / holding IUC tokens.

    $0.00
      Reply
      [-]
       6 days ago  

      Nice query you made there 😍

      $0.00
        Reply