Part 4: Dune Reporting

We use Dune Analytics (www.dune.com) to illustrate operational data and statistics.

Dune Analytics is a powerful and user-friendly platform that revolutionises the way we interact with blockchain data. It enables users to create, share, and explore community-driven data analyses and visualisations, making complex blockchain data accessible and understandable. With Dune Analytics, developers can delve deep into raw blockchain data to uncover insights, trends, and patterns.

Here we have several most wanted Dune queries to inspire the developers.

Replace the parameter wallet_provider to your address holding the wallet NFT.

1. NFT Reporting

1.1 Total Minted NFT in last 7 days

SELECT block_date AS "Date", count(*) AS "TotalNFTMint"
  FROM arbitrum.transactions
 WHERE "from" = {{wallet_provider}}
   AND varbinary_position(data,0x10480e13) = 1
   AND block_time < CURRENT_DATE
   AND block_time >= CURRENT_DATE - interval '7' day
GROUP BY block_date

1.2 Total Verified NFT in last 30 days

SELECT date(call_block_time) AS "Date", count(*) AS "TotalVerified" 
  FROM fiat24_arbitrum.Fiat24Account_call_changeClientStatus
 WHERE _status=5
   AND call_block_time < CURRENT_DATE
   AND call_block_time >= CURRENT_DATE - interval '30' day
   AND tokenId in (
                    SELECT varbinary_to_uint256(varbinary_substring("data",37,32))
                      FROM arbitrum.transactions
                     WHERE "from" = {{wallet_provider}}
                       AND varbinary_position(data,0x10480e13) = 1
                  )
GROUP BY date(call_block_time)

2. Balance Reporting

2.1 Total Balances of Clients

WITH 
    wallet_addresses AS (
        SELECT 
            varbinary_ltrim(varbinary_substring("data",5,32)) AS address
        FROM 
            arbitrum.transactions
        WHERE 
            "from" = {{wallet_provider}}
            AND varbinary_position(data,0x10480e13) = 1
    ),
    erc20_in AS ( 
        SELECT 
            tr.contract_address,
            SUM(COALESCE(CAST(tr.value AS DOUBLE), 0) / POW(10, COALESCE(tk.decimals, 18))) AS token_funded
        FROM 
            erc20_{{chain}}.evt_Transfer tr
        LEFT JOIN 
            tokens.erc20 tk ON tk.contract_address = tr.contract_address AND tk.blockchain = '{{chain}}'
        JOIN 
            wallet_addresses wa ON tr.to = wa.address
        WHERE 
            ({{blocknumber}} = 0 OR tr.evt_block_number <= {{blocknumber}})
        GROUP BY 
            tr.contract_address
    ),
    erc20_out AS (
        SELECT 
            tr.contract_address,
            SUM(COALESCE(CAST(tr.value AS DOUBLE), 0) / POW(10, COALESCE(tk.decimals, 18))) AS token_spent
        FROM 
            erc20_{{chain}}.evt_Transfer tr
        LEFT JOIN 
            tokens.erc20 tk ON tk.contract_address = tr.contract_address AND tk.blockchain = '{{chain}}'
        JOIN 
            wallet_addresses wa ON tr."from" = wa.address
        WHERE 
            ({{blocknumber}} = 0 OR tr.evt_block_number <= {{blocknumber}})
        GROUP BY 
            tr.contract_address
    ),
    erc20_balances AS (
        SELECT 
            tk.symbol AS symbol,
            erc20_in.contract_address,
            token_funded - token_spent AS balance
        FROM 
            erc20_in
        LEFT JOIN 
            erc20_out ON erc20_in.contract_address = erc20_out.contract_address
        LEFT JOIN 
            tokens.erc20 tk ON tk.contract_address = erc20_in.contract_address AND tk.blockchain = '{{chain}}'
        WHERE 
            token_funded - token_spent > 0
            AND erc20_in.contract_address IN (
                0xbE00f3db78688d9704BCb4e0a827aea3a9Cc0D62,
                0x2c5d06f591D0d8cd43Ac232c2B654475a142c7DA,
                0xd41F1f0cf89fD239ca4c1F8E8ADA46345c86b0a4,
                0x5fc17218196581864974574d715cFC7334794cBE
            )
    )

SELECT 
    bal.symbol,
    ROUND(bal.balance, 5) AS total_balances,
    p.price AS token_price
FROM 
    erc20_balances bal
LEFT JOIN 
    prices.usd_latest p ON p.contract_address = bal.contract_address
    AND p.blockchain = '{{chain}}'
WHERE 
    bal.balance > 0
    AND ('{{dust}}' = 'keep' OR bal.balance * p.price > 0.01)
ORDER BY 
    total_balances DESC NULLS LAST;

2.2 Top 10 clients with most Balances

WITH 
    wallet_addresses AS (
        SELECT 
            varbinary_ltrim(varbinary_substring("data",5,32)) AS address
        FROM 
            arbitrum.transactions
        WHERE 
            "from" = {{wallet_provider}}
            AND varbinary_position(data,0x10480e13) = 1
    ),
    received_amounts AS (
        SELECT 
            tk.symbol AS symbol,
            tr.contract_address,
            tr."to" AS address,
            COALESCE(SUM(CAST(tr.value AS DOUBLE) / POW(10, COALESCE(tk.decimals, 18))), 0) AS received,
            0 AS sent -- Add a default sent column
        FROM 
            erc20_arbitrum.evt_Transfer tr
        LEFT JOIN 
            tokens.erc20 tk ON tk.contract_address = tr.contract_address AND tk.blockchain = 'arbitrum'
        JOIN 
            wallet_addresses wa ON tr."to" = wa.address
        WHERE 
            ({{blocknumber}} = 0 OR tr.evt_block_number <= {{blocknumber}})
            AND tr.contract_address IN (
                0xbE00f3db78688d9704BCb4e0a827aea3a9Cc0D62,
                0x2c5d06f591D0d8cd43Ac232c2B654475a142c7DA,
                0xd41F1f0cf89fD239ca4c1F8E8ADA46345c86b0a4,
                0x5fc17218196581864974574d715cFC7334794cBE
            )
        GROUP BY 
            tr.contract_address, tk.symbol, tr."to"
    ),
    sent_amounts AS (
        SELECT 
            tk.symbol AS symbol,
            tr.contract_address,
            tr."from" AS address,
            0 AS received, -- Add a default received column
            COALESCE(SUM(CAST(tr.value AS DOUBLE) / POW(10, COALESCE(tk.decimals, 18))), 0) AS sent
        FROM 
            erc20_arbitrum.evt_Transfer tr
        LEFT JOIN 
            tokens.erc20 tk ON tk.contract_address = tr.contract_address AND tk.blockchain = 'arbitrum'
        JOIN 
            wallet_addresses wa ON tr."from" = wa.address
        WHERE 
            ({{blocknumber}} = 0 OR tr.evt_block_number <= {{blocknumber}})
            AND tr.contract_address IN (
                0xbE00f3db78688d9704BCb4e0a827aea3a9Cc0D62,
                0x2c5d06f591D0d8cd43Ac232c2B654475a142c7DA,
                0xd41F1f0cf89fD239ca4c1F8E8ADA46345c86b0a4,
                0x5fc17218196581864974574d715cFC7334794cBE
            )
        GROUP BY 
            tr.contract_address, tk.symbol, tr."from"
    ),
    all_amounts AS (
        SELECT 
            symbol,
            contract_address,
            address,
            COALESCE(SUM(received), 0) AS received,
            COALESCE(SUM(sent), 0) AS sent
        FROM 
            (
                SELECT * FROM received_amounts
                UNION ALL
                SELECT * FROM sent_amounts
            ) combined
        GROUP BY 
            symbol, contract_address, address
    ),
    ranked_amounts AS (
        SELECT 
            symbol,
            address,
            ROUND(received - sent, 5) AS total_balance,
            ROW_NUMBER() OVER (PARTITION BY symbol ORDER BY ROUND(received - sent, 5) DESC) AS rank
        FROM 
            all_amounts
    )
SELECT 
    symbol,
    address,
    total_balance
FROM 
    ranked_amounts
WHERE 
    rank <= 10
ORDER BY 
    symbol, total_balance DESC;

3. Transaction Reporting

3.1 Total transactional volume in last 24 hours

WITH 
    wallet_addresses AS (
        SELECT 
            varbinary_ltrim(varbinary_substring("data",5,32)) AS address
        FROM 
            arbitrum.transactions
        WHERE 
            "from" = {{wallet_provider}}
            AND varbinary_position(data,0x10480e13) = 1
    ),
    cumulative_volume AS (
        SELECT 
            tk.symbol AS symbol,
            tr.contract_address,
            SUM(CAST(tr.value AS DECIMAL) / POW(10, COALESCE(tk.decimals, 18))) AS total_volume
        FROM 
            erc20_arbitrum.evt_Transfer tr
        LEFT JOIN 
            tokens.erc20 tk ON tr.contract_address = tk.contract_address AND tk.blockchain = 'arbitrum'
        JOIN 
            wallet_addresses wa ON tr."from" = wa.address
        WHERE 
            tr.contract_address IN (
                0xbE00f3db78688d9704BCb4e0a827aea3a9Cc0D62,
                0x2c5d06f591D0d8cd43Ac232c2B654475a142c7DA,
                0xd41F1f0cf89fD239ca4c1F8E8ADA46345c86b0a4,
                0x5fc17218196581864974574d715cFC7334794cBE
            )
            AND tr.evt_block_time >= CURRENT_DATE - interval '1' day
        GROUP BY 
            tr.contract_address, tk.symbol
    )

SELECT 
    symbol,
    contract_address,
    ROUND(total_volume, 2) AS total_volume
FROM 
    cumulative_volume
ORDER BY 
    total_volume DESC;

3.2 Top 10 clients in transactional volume in last 24 hours

WITH 
    wallet_addresses AS (
        SELECT 
            varbinary_ltrim(varbinary_substring("data",5,32)) AS address
        FROM 
            arbitrum.transactions
        WHERE 
            "from" = {{wallet_provider}}
            AND varbinary_position(data,0x10480e13) = 1
    ),
    relevant_transfers AS (
        SELECT 
            CASE 
                WHEN tr."from" = wa.address THEN tr."from" 
                ELSE tr."to" 
            END AS address,
            tr.contract_address,
            tk.symbol,
            CAST(tr.value AS DECIMAL) / POW(10, COALESCE(tk.decimals, 18)) AS volume
        FROM 
            erc20_arbitrum.evt_Transfer tr
        JOIN 
            tokens.erc20 tk ON tr.contract_address = tk.contract_address AND tk.blockchain = 'arbitrum'
        INNER JOIN 
            wallet_addresses wa ON tr."from" = wa.address OR tr."to" = wa.address
        WHERE 
            tr.contract_address IN (
                0xbE00f3db78688d9704BCb4e0a827aea3a9Cc0D62,
                0x2c5d06f591D0d8cd43Ac232c2B654475a142c7DA,
                0xd41F1f0cf89fD239ca4c1F8E8ADA46345c86b0a4,
                0x5fc17218196581864974574d715cFC7334794cBE
            )
            AND tr.evt_block_time >= CURRENT_DATE - INTERVAL '1' day
    ),
    address_volumes AS (
        SELECT 
            contract_address,
            symbol,
            address,
            SUM(volume) AS total_volume
        FROM 
            relevant_transfers
        GROUP BY 
            contract_address, symbol, address
    ),
    ranked_addresses AS (
        SELECT 
            contract_address,
            symbol,
            address,
            total_volume,
            ROW_NUMBER() OVER (PARTITION BY contract_address ORDER BY total_volume DESC) AS rank
        FROM 
            address_volumes
    )

SELECT 
    symbol,
    address,
    ROUND(total_volume, 2) AS total_volume
FROM 
    ranked_addresses
WHERE 
    rank <= 10
ORDER BY 
    contract_address, rank;