📂Solana Queries

Most Popular Tokens Transferred In the Last Two Days

WITH
all_instructions AS (
      SELECT 
        transaction_id,
        parsed
    FROM nansen-query.raw_solana.transactions, UNNEST(instructions) 
    WHERE DATE(block_timestamp) > DATE_SUB(CURRENT_DATE(), INTERVAL 2 DAY)
    UNION ALL
    SELECT 
        transaction_id,
        parsed
    FROM nansen-query.raw_solana.transactions, UNNEST(inner_instructions) AS ii, UNNEST(ii.instructions)
    WHERE DATE(block_timestamp) > DATE_SUB(CURRENT_DATE(), INTERVAL 2 DAY)
),

token_transfers AS (
    SELECT
        JSON_EXTRACT_SCALAR(parsed, "$.info.authority") AS token_address,
        JSON_EXTRACT_SCALAR(parsed, "$.info.source") AS source,
        JSON_EXTRACT_SCALAR(parsed, "$.info.destination") AS destination,
        JSON_EXTRACT_SCALAR(parsed, "$.info.amount") AS amount
    FROM all_instructions
    WHERE JSON_EXTRACT_SCALAR(parsed, "$.type") = "transfer"
    AND JSON_EXTRACT_SCALAR(parsed, "$.info.authority") IS NOT NULL
)

SELECT
    token_address,
    COUNT(*) AS count
FROM
    token_transfers
GROUP BY token_address
ORDER BY count DESC

Recent token account deployments:

WITH
all_instructions AS (
    SELECT 
        block_timestamp,
        transaction_id,
        program_id,
        parsed
    FROM nansen-query.raw_solana.transactions, UNNEST(instructions) 
    WHERE DATE(block_timestamp) > DATE_SUB(CURRENT_DATE(), INTERVAL 2 DAY)
    UNION ALL
    SELECT 
        block_timestamp,
        transaction_id,
        program_id,
        parsed
    FROM nansen-query.raw_solana.transactions, UNNEST(inner_instructions) AS ii, UNNEST(ii.instructions)
    WHERE DATE(block_timestamp) > DATE_SUB(CURRENT_DATE(), INTERVAL 2 DAY)
),

token_accounts AS (
    SELECT
        block_timestamp,
        transaction_id,
        JSON_EXTRACT_SCALAR(parsed, "$.info.account") AS account_address
    FROM all_instructions
    WHERE
        JSON_EXTRACT_SCALAR(parsed, "$.type") = "initializeAccount"
        AND program_id = "TokenkegQfeZyiNwAJbNbGKPFXCWuBvf9Ss623VQ5DA"
)

SELECT
    transaction_id,
    block_timestamp,
    account_address
FROM
    token_accounts
ORDER BY
    block_timestamp DESC
LIMIT 1000

Mango Market Orders

Note that this is selecting from the blockchain-etl project, which is a hybrid dataset based on contribution from both Nansen and the community.

SELECT * 
FROM `blockchain-etl.solana_mango.MangoMarketsV3_event_FillLog` 
ORDER BY block_timestamp DESC
LIMIT 1000