binhachonLet’s keep diving into the Algorand NFT ecosystem. - Count collection
    Updated 2022-05-31
    with potential_ASA as (
    select
    asset_id,
    creator_address,
    asset_name,
    created_at
    from flipside_prod_db.algorand.asset
    where total_supply = 1
    and decimals = 0
    ),
    standard as (
    select
    asset_id,
    try_base64_decode_string(tx_message:txn:note::string) as decoded_note,
    try_parse_json(decoded_note) as json_decoded_note,
    case when json_decoded_note is not null then json_decoded_note:standard else 'Null' end as standard
    from flipside_prod_db.algorand.asset_configuration_transaction
    where asset_id in (select asset_id from potential_ASA)
    and tx_type = 'acfg'
    ),
    NFT_list as (
    select
    potential_ASA.*,
    case when position('#', asset_name, 1) = 0 then asset_name else substr(asset_name, 1, position('#', asset_name, 1) - 1) end as collection,
    block_timestamp
    from potential_ASA
    left join flipside_prod_db.algorand.block on (block_id = created_at)
    where asset_id in (select asset_id from standard where standard ilike '%arc%')
    ),
    count_collection as (
    select
    NFT_list.*
    from NFT_list
    qualify row_number() over (partition by collection order by block_timestamp) = 1
    ),
    count_creator_address as (
    Run a query to Download Data