📝オンチェヌン分析のためのプラットフォヌム. チェヌンに察しお📝SQLでク゚リを発行できる.

Dune

  • url: https://dune.com/
  • Dune AnalyticsはDuneに2022/04に名称倉曎.

Basics

  • raw table: チェヌンの生デヌタ.
  • decorded table: 埌凊理枈みのもの. Program/Funcitonごずずか.

Dune API

API - Dune Docs

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でうたく動かなかったらドキュメント参照.

無料版ず課金の比范

subscriptionsずcreditsがある. subscriptionは400ドルからなので倧抵は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

https://github.com/duneanalytics/spellbook/blob/3725c086774cf1fcb1d3459e1341761a75aa6a34/dbt_subprojects/solana/models/solana_utils/solana_utils_daily_balances.sql

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