msafadoostUntitled Query
    Updated 2022-06-15
    WITH arc69 as (
    SELECT asset_id AS asset_69_id, try_parse_json(try_base64_decode_string(tx_message:txn:note::string)) as note
    FROM algorand.asset_configuration_transaction
    WHERE note:standard::string = 'arc69'
    )

    , arc3 as (
    SELECT asset_id AS asset_3_id, ASSET_PARAMETERS:au::string as url
    FROM algorand.asset_configuration_transaction
    WHERE CONTAINS(url, '#arc3')
    ),
    nfts AS (
    SELECT asset_69_id AS ALL_NFTS_ID
    FROM arc69 FULL JOIN arc3 ON asset_69_id = asset_3_id
    ),
    firsti AS (
    SELECT date(BLOCK_TIMESTAMP),
    TX_GROUP_ID as tx_g_id,
    COUNT(TX_ID) as three,
    COUNT(DISTINCT(ASSET_ID)) as two,
    COUNT(DISTINCT(SENDER)) as twoo,
    COUNT(DISTINCT(TX_TYPE)) as twooo
    FROM flipside_prod_db.algorand.transactions
    WHERE BLOCK_TIMESTAMP >= '2022-01-01'
    and (
    ASSET_ID IN (
    SELECT asset_69_id FROM arc69
    )
    OR ASSET_ID IN (
    SELECT asset_3_id FROM arc3
    )
    OR ASSET_ID = '0'
    )
    GROUP by 1,2 HAVING (three = 3 AND two = 2 AND twoo = 2 AND twooo = 2)
    ORDER by 1
    ),
    Run a query to Download Data