📂
Solana Queries
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
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
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
Last modified 6mo ago