Solana On-Chain Analytics. Week 2

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