Solana On-Chain Analytics. Week 2

analysis.txt

1. 5Q544fKrFoe6tsEbD7S8EmxGTJYAKtTVhAW5Q5pge4j1 Raydium Authority V4. Swaping assets.
2. ChGA1Wbh9WN8MDiQ4ggA5PzBspS2Z6QheyaxdVo3XdW6 $IMG. Sending rewards.
3. 4VztozdDL8yFPLdpUpMNT3Hf7xMZ2qPGAMretExTvZ78 Kamino. Lending assets.
4. Dx8iy2o46sK1DzWbEcznqSKeLbLVeu7otkibA3WohGAj Kamino. Reserve account.
5. 62qc2CNXwrYqQScmEdiZFFAnJR262PxWEuNQtxfafNgV pump.fun. Collecting fees.
6. 7LCZckF6XXGQ1hDY6HFXBKWAtiUgL9QY5vj1C4Bn1Qjj Jito Staking.
7. GpMZbSM2GgvTKHJirzeGfMFoaZ8UR2X7F4v8vHTvxFbL Raydium Vault
8. 5XFdrMqisKzNBvx68GYR8ULuXkaDefLAHTojVYyrhKt9 Fluxbeam.
9. MfDuWeqSHEqTFVYZ7LoexgAK9dxk7cy4DFJWjWMGVWa Wintermute. Trading.
10. 28KqHiudrpzfVkVWQ1jztQ2Aarf4W3CvTitjWEqTCkpA Pool?

query.dune.sql

WITH
    transfers AS (
        SELECT
            from_owner,
            from_token_account,
            to_owner,
            to_token_account,
            amount,
            action,
            block_time
        FROM
            tokens_solana.transfers
        WHERE
            symbol = 'SOL'
            AND amount >= 1000000
            AND DATE_TRUNC('day', block_time) >= DATE_TRUNC('day', CURRENT_DATE - INTERVAL '7' day)
            AND action = 'transfer'
    )
SELECT
    '+',
    address,
    COALESCE(COUNT(*), 0) AS total_transaction_count,
    COALESCE(COUNT(*) FILTER(WHERE type = 'outgoing'), 0) AS outgoing_transaction_count,
    COALESCE(COUNT(*) FILTER(WHERE type = 'incoming'), 0) AS incoming_transaction_count,
    COALESCE(SUM(amount) FILTER(WHERE type = 'outgoing') / 1e9, 0) AS total_outgoing_volume,
    COALESCE(SUM(amount) FILTER(WHERE type = 'incoming') / 1e9, 0) AS total_incoming_volume
FROM
    (
        SELECT
            from_owner AS address,
            amount,
            'outgoing' AS type
        FROM
            transfers
        UNION ALL
        SELECT
            to_owner AS address,
            amount,
            'incoming' AS type
        FROM
            transfers
    )
WHERE address != ''
GROUP BY
    address
ORDER BY
    total_transaction_count DESC
LIMIT
    50

result.csv