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
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.
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;
| 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 #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