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...
| Sender | Receiver | Amount |
|---|---|---|
| bithumbrecv2 | bithumbsend2 | 493284.1640 |
| bithumbsend2 | user.dunamu | 401507.5800 |
| bithumbsend2 | bdhivesteem | 330571.8100 |
| steemmonsters | steemmonsters | 154738.8570 |
| bitmart306 | mxchive | 125144.6400 |
| cybercity | bdhivesteem | 117300.6430 |
| splinterlands | cryptoeater | 100000.0000 |
| sl-swap | honey-swap | 100000.0000 |
| steemmonsters | sl-swap | 100000.0000 |
| vicemiami | cybercity | 93873.1210 |
| cryptoeater | bdhivesteem | 92070.2200 |
| mxccommxchive | mxchive | 80716.4180 |
| bitgethive | user.dunamu | 70379.9800 |
| rez11rev09 | mxchive | 64610.5200 |
| bitgethive | gateiodeposit | 60933.9900 |
| bdhivesteemfree | bitgethive | 56592.4750 |
| josepimpo | bdhivesteem | 53917.7520 |
| euserver | mxchive | 51969.8440 |
| michael2024 | bdhivesteem | 50662.6710 |
| mxchivemxcsteem | mxchive | 46767.7710 |
| hanv | honey-swap | 44050.0000 |
| hivetestfree | mxchive | 43682.5400 |
| bitgethive | gateiohivesteem | 43055.7790 |
| exchangein | xchangeout | 42755.6580 |
| id532984494 | huobi-pro | 41367.7810 |
| vsc.network | bdhivesteem | 40800.0000 |
| htx-9tuqx5jg | gateiohivesteem | 40547.7060 |
| reazuliqbal | bdhivesteem | 34378.3860 |
| token-converter | honey-swap | 34013.8150 |
| hivelot | cj3000 | 32584.9400 |
| cj3000 | mxchive | 32000.0000 |
| hivenetwork1 | mxchive | 31993.0900 |
| ghlqh3tkdb4qns | ghlhivethumb | 30220.6990 |
| bitgethive | bdhive-steem | 27997.8230 |
| abh12345 | hiveswap | 24543.0000 |
| bitgethive | bltgetxpr | 21593.9740 |
| bltgetxpr | bitgethive | 21570.3550 |
| detlev | bdhivesteem | 21500.0000 |
| orinoco | bdhivesteem | 19723.2330 |
| bitgethive | mxsteem | 19559.4580 |
| eddiespino | bdhivesteem | 18932.1140 |
| danieljusto | hiveswap | 18281.8460 |
| htx-9tuqx5jg | mxchive | 18249.1580 |
| id536934172 | bdhivesteem | 17200.0000 |
| id532984494 | id536934172 | 17146.7340 |
| therealyme | mxchive | 17033.0190 |
| danieljusto | bdhivesteem | 17015.0000 |
| keychain.swap | keychain | 16652.4900 |
| vihan | bdhivesteem | 16569.9320 |
| bitgethive | xpr-hive | 16497.2460 |
| xpr-hive | bitgethive | 16497.2460 |
| hivecoinfree | bdhivesteem | 16200.0000 |
| uswap | keychain.swap | 16153.0220 |
| bluemist | appreciator | 15379.0000 |
| peakd | bdhivesteem | 15000.0000 |
| cryptoeater | hiveswap | 15000.0000 |
| hive-engine | honey-swap | 15000.0000 |
| steem.future | user.dunamu | 14832.8360 |
| lovejill | bdhivesteem | 14310.4640 |
| hivetestfree | huobi-pro | 14147.1300 |
| bltgetxpr | mxchive | 13428.2740 |
| htx-9tuqx5jg | bltgetxpr | 13411.9570 |
| hiveswap | danieljusto | 12996.2720 |
| liucixin | bdhivesteem | 12792.7990 |
| fabiyamada | bdhivesteem | 12074.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::
- 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