ðãªã³ãã§ãŒã³åæã®ããã®ãã©ãããã©ãŒã . ãã§ãŒã³ã«å¯ŸããŠðSQLã§ã¯ãšãªãçºè¡ã§ãã.
Dune
- url: https://dune.com/
- Dune Analyticsã¯Duneã«2022/04ã«åç§°å€æŽ.
Basics
- raw table: ãã§ãŒã³ã®çããŒã¿.
- decorded table: åŸåŠçæžã¿ã®ãã®. Program/Funcitonããšãšã.
Dune API
Pricing
- free
- 2500credits
- analyst
- æé¡50ãã«ã®èª²éã§APIããã®queryå®è¡å¯èœ
- 4000credits
æ¯æ2500 creditsã®ç¡ææ ãã. 2500ãè¶ ããã10 creditsããšã«0.3ãã«=50åããã.
- execitions: 10 credits
- datapoints: 1 credits
datapoints
rowsãšcolsãŸãã¯ããŒã¿éããç®åºãããå€.
A datapoint can in most cases be thought of rows * columns with an additional limit of 100 avg bytes per cell in a set of results. This can be expressed as:
Datapoints = max(rows*columns, ceil(totalbytes/100))
https://dune.com/docs/api/faq/?h=datapoint#whats-a-datapoint
1000datapoints = 1 credits.
https://dune.com/docs/api/?h=credit#api-pricing
APIããã®ã¯ãšãªå®è¡ã¯ç¡æã§ã§ããªã
defaultã§performanceãmedium=10creditsæ¶è²»ãã. æ¯æ2500 creditsãç¡ææ ã§ãã.
You cannot run API executions on the free tier. Result returns an execution_id for the specified request.
ã¯ãšãªå®è¡ãUIããæåå®è¡ããŠãã®çµæãAPIã§ååŸãšãããã®ããã(èªååã¯ã§ããªã).
ref. https://dune.com/docs/api/api-reference/execute-queries/execute-query-id/
Topics
exportæ©èœ(csv/json)
課éããªããšUIããã®csv exportã¯ã§ããªã. APIã®ç¡æcreditsã®ç¯å²ã§query resultsãapiãã€ãã£ãŠjson/csvä¿å. ã¯ãšãªç»é¢ã®ãšãã£ã¿ã®å³äžã®APIãæŒããšurlãã§ãŠãã.
èªååããã«ã¯query_idãæå®ããŠAPIã§ã¯ãšãªãå®è¡ã, çµæãhttp getã§ååŸãã. SDKã§ãããããã©ç°¡åãªã®ã¯curlãšã. importData
颿°ã§Google Spreadsheetã«ãããã.
downloadã§ã¯datapoints ã§creditsãæ¶è²»ãã. credits=(rows * cols)/1000.
via curl
$ curl "https://api.dune.com/api/v1/query/<query_id>/results/csv?api_key=<api_key>" -o "output.csv"
APIå¶é
- 1åã®API callã§1GBãŸã§Downloadå¯èœ.
- There is currently a 1GB limit, but there is a chance we reduce this overall or based on varying paid plan types.
- defaultã§2500000 datapointsãŸã§ã®å¶éããã£ãŠãã. optionsã§å¶éãå€ãããšã¯ã§ãã.
- This api request would exceed your configured limits per request. Please visit your settings on dune.com and adjust your limits to continue usage.
Dune SQL
ããšããšDuneã¯Postgresãã€ãã£ãŠããã, æé©åã®ããã«ç¬èªDSLã§ããDune SQLãéçºãã.
æ®éã®SQLã§ããŸãåããªãã£ããããã¥ã¡ã³ãåç §.
- DuneSQL Overview - Dune Docs
- Syntax Differences - Dune Docs
- migration: https://dune.com/docs/migrations/#migration
- released: Introducing Dune SQL
ç¡æçãšèª²éã®æ¯èŒ
subscriptionsãšcreditsããã. subscriptionã¯400ãã«ãããªã®ã§å€§æµã¯creditsã§å¿ èŠãªã ã課é.
- https://dune.com/pricing
- https://dune.com/docs/learning/how-tos/credit-system-on-dune/#managing-your-credits
- ããã·ã¥ããŒãã¯ç¡æçã ãš1ã€ã®ã¿privateã§ããšã¯public. 課éãããšprivateãããã.
- ç¡ææ ã§ã®ã¯ãšãªã¯2åã§ã¿ã€ã ã¢ãŠããã.
Dune with Solana
https://dune.com/docs/data-tables/raw/solana/
Solana Analytics Starter Guide (Part 1): Solana Instructions
solana.transacitons
ref. https://dune.com/docs/data-tables/raw/solana/transactions/
- id: signaturesã®å é . signatureãšåãå€.
- balancesã¯ã«ã³ãåºåãã®æååã§å€ãã
- rawããŒã¿ã§æ å ±éãå€ãã®ã§çµã蟌ã¿ãå¿ èŠ. logãšããã¯ãã£ãŠã.
- solana.account_activityãåŠçãããçµæãªã®ã§ãã£ã¡ããããã.
solana.account_activity
ref. https://dune.com/docs/data-tables/raw/solana/account-activity/
- address: Signer
- token_balance_owner: associated account owner.
- documentã ãštoken_owner_addressãšãªã£ãŠã. 仿§å€æŽ?
- token_mint_address: mint address.
- block_timeã¯TIMESTAMPå, block_dateã¯DATEå.
- addressãæå®ãããšäžå¿ associated accountã®ååŒãå«ããŠé¢é£ããtxã¯ãã¹ãŠååŸã§ãã.
- ãªããªãã°SPL Tokensã®ååŒã®ææ°æã§5000 lamportsãæ¶è²»ãããã®ã§.
- ãã ã, token_balance_change, post_token_balanceã¯ç©ºãªã®ã§æçèšç®ã§ã¯äžåã.
- æçèšç®çšéã§ã¯ãªãååŒå±¥æŽã®ä¿åãç®çãªãã°txã®ãªã¹ãã¯ååŸã§ããã®ã§ææªããã§ããã.
- solana.transactionsããŒãã«ã§signerã§ããã.
- ãã transactionsããŒãã«ã¯æ å ±ãå€ãããäžã«rawããŒã¿ãªã®ã§, block_time, signature, signerçšåºŠã®SELECTããã.
- addressã§ã¯ãªãtoken_balance_ownerãæå®ãããšãšãŠãã¯ãšãªãé
ã.
- addressã«ã©ã ã«ã¯indexããªãããã®æé©åããªãããŠããå¯èœæ§ããã.
- tx_idãæå®ããŠããšãŠãé ã. tx_idã®çµã蟌ã¿ã¯solana.transactionsããŒãã«ã䜿ã£ãã»ãããã.
SOL transactions
確å®ç³åã®åž³ç°¿ä¿åçš.
- SOLã¯lamportã§æ°å€ã衚瀺ãããã®ã§decimals=9ã§å²ã.
- SOLã®å Žå㯠tx_succss = falseã§ããã©ã³ã¶ã¯ã·ã§ã³ææ°æãããã.
SELECT
block_time AS time,
balance_change / 1e9 AS sol_change,
post_balance / 1e9 AS sol_balance,
address,
tx_id
FROM
solana.account_activity
WHERE
(
address = ''
OR address = ''
OR address = ''
)
AND block_date > DATE '2023-11-21'
ORDER BY
1
SPL transactions
確å®ç³åã®åž³ç°¿ä¿åçš.
- tx_success = TRUEãæå®ããŠæåããtxã®ã¿çµã蟌ã¿.
- wSOLã®token_balance_changeãšSOLã®balance_change/1e9ãäžèŽãã.
- wSOL post_token_balanceãšSOL post_balance/1e9ãšã¯äžèŽããªã.
- addressã®éšåãtoken_owner_addressã«ãããšwallet addressã«çŽã¥ãassociated accountã®txããã¹ãŠååŸå¯èœ. ãã ãã¯ãšãªããããããŠå®çšçã§ãªã. addressã ãã¹ãŠåæããã®ããã.
SELECT
block_time AS time,
token_balance_change AS token_change,
token_mint_address AS mint_address,
token_balance_owner AS owner_address,
address AS token_address,
tx_id
FROM
solana.account_activity
WHERE
(
address = ''
OR address = ''
OR address = ''
OR address = ''
)
AND tx_success = TRUE
AND block_date BETWEEN DATE '2023-11-20' AND DATE '2023-11-26'
ORDER BY
1
2024ç
- æ¯æã®ããŒã¿éã ãšapi downloadã§ããªã(ãã¶ã8MBã®å¶é), 鱿¬¡ã«ãããŠdownload.
- creditæ¶è²»ã¯columnæ°ã«å¯ŸããŠèšç®ãããã®ã§å¯èœãªéãcolumnæ°ãåæž
- blocktime
- txid
- change
- SOL/USDCã§ãããŠããŠã³ããŒãããããšã§ã«ã©ã åæž. ãã¡ã€ã«åã§sol/usdcãåºå¥.
SOL
SELECT
block_time AS time,
-- token_balance_change AS change,
balance_change / 1e9 AS change,
tx_id
FROM
solana.account_activity
WHERE
(
-- address = 'hoge' -- SOL
-- address = 'hoge2' -- wSOL
address = 'hoge3' -- USDC
)
AND tx_success = TRUE
AND block_date BETWEEN DATE '2024-11-25' AND DATE '2024-12-01'
ORDER BY
1
USDC
SELECT
block_time AS time,
-- token_balance_change AS change,
balance_change / 1e9 AS change,
tx_id
FROM
solana.account_activity
WHERE
(
address = 'hoge' -- SOL
OR address = 'hoge2' -- wSOL
-- address = 'hoge3' -- USDC
)
AND tx_success = TRUE
AND block_date BETWEEN DATE '2024-11-25' AND DATE '2024-12-01'
ORDER BY
1
2025æ€èš
ç¡ææ ã®ç¯å²ã§Duneãã€ãã£ãŠããšã¯StakeTaxãã€ããäºåæµæŠç¥. æé¡49ãã«ã¯ã¡ãã£ãšé«ãããªâŠ
SELECT
block_time AS time,
balance_change AS sol_change,
token_balance_change AS spl_change, -- AS change,
-- token_mint_address AS mint_address,
tx_id
FROM
solana.account_activity
WHERE
tx_success = TRUE
AND address = '' -- SOL for jito tip
-- AND address = '' -- WSOL for arb
-- AND address = '' -- USDC for arb
AND block_date = DATE '2025-01-01'
-- AND block_date BETWEEN DATE '2025-01-01' AND DATE '2025-01-01'
ORDER BY
1
downloadããŒã¿åæžã®ããã«columnå³éžããŠãPython Scriptã§å€æã«ãã£ãŠè£ãæ¹é.
- address ã SOL/WSOL/USDCã®ïŒã€ã«åããããšã§ãtoken_mint_addressã¯äžèŠ.
- SOL aka. jito tipã®å Žå㯠balance_changeã« jito tip feeãå ¥ã.
- WSOL/USDC ã¯token_balance_changeã® arbå©çãå ¥ã.
Daily PnL
block_dateã20xx-xx-xxã®DATEåã«ãªã£ãŠãã. ãã¶ãã€ã³ããã¯ã¹åŠçããããŠããŠãã®ã«ã©ã ãæŽ»çšããã®ãé«é.
post_balance, post_token_balanceã¯æ¥ä»ããŸããå€å®ãç ©éã ã£ãã, 倧ããªè³éç§»åã§ããããªbarãã£ãŒãã«ãªããªãã®ã§å€ãã.
SELECT
DATE(block_time) AS date,
SUM(balance_change) / 1e9 AS sol_change
FROM solana.account_activity
WHERE
address = ''
GROUP BY
DATE(block_date)
SELECT
DATE(block_time) AS date,
SUM(token_balance_change) AS token_change
ssFROM solana.account_activity
WHERE
address = ''
AND tx_success = TRUE
GROUP BY
DATE(block_date)
Daily balance
https://dune.com/data/solana_utils.daily_balances
SELECT
*
FROM solana_utils.daily_balances
WHERE
address = TRY_CAST('wallet address' AS VARCHAR)
ORDER BY
1 DESC
Solana MEV Daily PnL for ã¯ãªãã¿ã¯ã
for ð¡ã¯ãªãã¿ã¯ãã«ã¹ã¿ã ãã¡ã€ã«
ãã®æ¹åŒã ãšDEXã«éãããªãã©ã³ã¹ãè³éç§»åã¯åé¡ãªããã©, CEX<->DEXã®ãããšãã ãšPnLã§ããããªããšã«ãªã£ãŠããŸã. ãã®å Žåã¯Transfer Txãé€å€ããå¿ èŠããããã®ã®, solana.account_activityã§filterã«äœ¿ããããªæ å ±ã¯tx_idããããããªãã®ã§, ééããidãèŠããŠããå¿ èŠããããã. ããæ¹æ³ãæ€èšäž.
SOL
SELECT
CONCAT(CAST(block_date AS VARCHAR), ' 23:59:59') AS Timestamp,
CASE
WHEN SUM(balance_change) >= 0 THEN 'BONUS'
ELSE 'LOSS'
END AS Action,
'solana' AS Source,
'SOL' AS Base,
SUM(balance_change) / 1e9 AS Volume,
'' AS Price,
'JPY' AS Counter,
0 AS Fee,
'JPY' AS FeeCcy,
'Solana MEV Daily SOL PnL' AS Comment
FROM
solana.account_activity
WHERE
(
address = ''
OR address = ''
OR address = ''
)
AND block_date BETWEEN DATE '2023-11-18' AND DATE '2023-11-26'
GROUP BY
block_date
ORDER BY
1
SPL
SELECT
CONCAT(CAST(block_date AS VARCHAR), ' 23:59:59') AS Timestamp,
CASE
WHEN SUM(token_balance_change) >= 0 THEN 'BONUS'
ELSE 'LOSS'
END AS Action,
'solana' AS Source,
CASE
WHEN token_mint_address = 'So11111111111111111111111111111111111111112'
THEN 'SOL'
WHEN token_mint_address = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v'
THEN 'USDC'
WHEN token_mint_address = 'Es9vMFrzaCERmJfrF4H2FYD4KCoNkY11McCe8BenwNYB'
THEN 'USDT'
ELSE token_mint_address
END AS Base,
SUM(token_balance_change) AS Volume,
'' AS Price,
'JPY' AS Counter,
0 AS Fee,
'JPY' AS FeeCcy,
'Solana MEV Daily SPL PnL' AS Comment
FROM solana.account_activity
WHERE
(
address = ''
OR address = ''
OR address = ''
OR address = ''
)
AND tx_success = TRUE
AND block_date BETWEEN TRY_CAST('2023-11-18' AS DATE) AND TRY_CAST('2023-11-26' AS DATE)
GROUP BY
block_date,
token_mint_address
ORDER BY
block_date
ðReferences
- Dune Analyticsã«å ¥éããŠã¿ãã | ããããã
- [8.17] 圢ã§èŠããDuneäœ¿ãæ¹è¬åº§<åçŽ> - by ta1suke
- 培åºå ¥éïŒå®å šåå¿è ã§ãåãããªã³ãã§ãŒã³åæãSQLã ãã§Web3åæ@Dune Analyticsã - Stir Lab
- Dune Analytics: A Guide for Complete Beginners â 0xPhillan