ngxuan885Top 10 wallets in # of atomic purchases in 2022
    Updated 2022-06-17
    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