ngxuan885Top 10 wallets in # of atomic purchases in 2022
Updated 2022-06-17
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
with NFTs as ( SELECT ASSET_ID from flipside_prod_db.algorand.asset
where TOTAL_SUPPLY = 1 and DECIMALS = 0),
arc3 as ( SELECT Asset_id from flipside_prod_db.algorand.asset
where ASSET_URL ilike '%#arc3%' and DECIMALS = 0 and TOTAL_SUPPLY = 1),
Arc69 as (SELECT Asset_id from flipside_prod_db.algorand.asset_configuration_transaction
where try_PARSE_JSON(TRY_BASE64_DECODE_STRING(tx_message:txn:note::string)):standard::string = 'arc69'
and Asset_id in (SELECT Asset_id from NFTs)),
-------------------------------------------------------------------------------------------------------------
a as (SELECT TX_GROUP_ID, ASSET_ID , count(*) as counts from flipside_prod_db.algorand.transfers
WHERE BLOCK_TIMESTAMP >= '2022-01-01' and ASSET_ID = 0 group by 1,2 having counts = 1 ),
--------------------------------------------------------------------------------------------------------------
b as (SELECT TX_GROUP_ID, ASSET_ID , count(*) as counts from flipside_prod_db.algorand.transfers
WHERE BLOCK_TIMESTAMP >= '2022-01-01' and ASSET_ID != 0 and ASSET_ID in (SELECT ASSET_ID from NFTs)
group by 1,2 having counts = 2 ),
------------------------------------------------------------------------------------------------------------
c as (SELECT TX_GROUP_ID, ASSET_ID , amount, count(*) as counts from flipside_prod_db.algorand.transfers
WHERE BLOCK_TIMESTAMP >= '2022-01-01' and ASSET_ID != 0 and ASSET_ID in (SELECT ASSET_ID from NFTs)
and amount in (0,1) group by 1,2,3 having counts = 1 ),
----------------------------------------------------------------------------------------------------------
algo_part as ( SELECT BLOCK_TIMESTAMP, TX_GROUP_ID, amount as Algo_volume, ASSET_SENDER as buyer, RECEIVER as seller
from flipside_prod_db.algorand.transfers
where TX_GROUP_ID in (SELECT TX_GROUP_ID from a)
and TX_GROUP_ID in (SELECT TX_GROUP_ID from b)
and TX_GROUP_ID in (SELECT TX_GROUP_ID from c)
and BLOCK_TIMESTAMP::date >= '2022-01-01' and asset_id = 0) ,
----------------------------------------------------------------------------------------------
NFT_part as ( SELECT TX_GROUP_ID, asset_id as NFT
from flipside_prod_db.algorand.transfers
where TX_GROUP_ID in (SELECT TX_GROUP_ID from a)
and TX_GROUP_ID in (SELECT TX_GROUP_ID from b)
and TX_GROUP_ID in (SELECT TX_GROUP_ID from c)
and BLOCK_TIMESTAMP::date >= '2022-01-01' and asset_id != 0 and amount = 1)
---------------------------------------------------------------------------------------------------
Run a query to Download Data