Dune Query Workflow for RAIKU

Step 3: Hand off to the user

Tell Sylvain: "Copy-paste this prompt into Claude Code in VS Code. It will create the query on Dune, execute it, and save the results."

Step 4: After results arrive

Once the user has the CSV, then you can:

• Read and validate the results
• Merge into existing datasets
• Update the pipeline (build_program_database.py, etc.)

SQL Patterns That Work on Dune Free Tier

The efficient pattern (single-table, < 5 min)

WITH tx_with_program AS (
  SELECT
    fee,
    compute_units_consumed,
    instructions[1].executing_account AS program_id,
    success
  FROM solana.transactions
  WHERE block_date >= DATE '2026-02-04'
    AND block_date < DATE '2026-03-06'
    AND compute_units_consumed > 0
    AND fee > 0
    -- Optional: filter to specific programs
    AND instructions[1].executing_account IN ('addr1', 'addr2', ...)
)
SELECT
  program_id,
  COUNT(*) AS tx_count,
  SUM(CASE WHEN success THEN 1 ELSE 0 END) AS success_count,
  ROUND(SUM(5000.0) / 1e9, 4) AS base_fees_sol,
  ROUND(SUM(GREATEST(CAST(fee AS double) - 5000, 0)) / 1e9, 4) AS priority_fees_sol,
  ROUND(SUM(CAST(fee AS double)) / 1e9, 4) AS total_fees_sol,
  SUM(compute_units_consumed) AS total_cu,
  ROUND(AVG(CAST(compute_units_consumed AS double)), 0) AS avg_cu_per_tx
FROM tx_with_program
GROUP BY program_id
ORDER BY total_fees_sol DESC

Key points:

• Always use block_date partition pruning — without it, full table scan = timeout
• Use fixed dates (DATE '2026-02-04') not CURRENT_DATE - INTERVAL for reproducibility
• instructions[1].executing_account = first instruction's program (program attribution)
• Base fee = 5000 lamports per tx (simplified; real = 5000 × num_signatures, but 99%+ of txs have 1 signature)
• Priority fee = fee - required_signatures * 5000 (more accurate than cardinality(signatures))
• required_signatures (integer) is available natively — no need for cardinality(signatures)
• compute_units_consumed is the correct column name (not compute_units)

Patterns that TIMEOUT on free tier (avoid)

• JOINing solana.transactions with solana.instruction_calls (too many rows)
• JOINing with system_program_solana.system_program_call_transfer on > 48h windows
• Any query touching > 30 days of solana.transactions without program filtering
• Queries returning > 1000 rows (Dune free tier limit)

Jito tips per program — 48h window feasible

Per @ilemi query 4314734, JOINing system_program_solana.system_program_call_transfer with solana.transactions works on 48h windows. The decoded table column is lamports (not amount), and account_to for recipient. Table name is lowercase (system_program_call_transfer). See data/sql_query2_jito_tips_qc_48h.sql for the tested pattern. If timeout on 48h, reduce to 24h.

Credit budget

Community plan: 2500 credits/month. Billing period resets ~6th of each month. One 30d single-table scan with 531-program IN clause costs ~200-500 credits. The Jito JOIN query (48h) costs ~300-800 credits. Budget both queries before executing.

Dune MCP Tools (for Claude Code prompts)

Claude Code in VS Code has these Dune MCP tools:

• createDuneQuery(name, query_sql, is_private) — Creates a saved query
• executeQueryById(query_id, parameters) — Runs a query (async)
• getExecutionResults(execution_id) — Polls for results
• searchTables(search_term) — Explore table schemas

CSV Format Convention

All output CSVs must use:

• Semicolon delimiter (;)
• UTF-8 encoding
• Saved to data/raw/ in the raiku-revenue-model project

Project Context

This skill is part of the RAIKU revenue model project. Key files:

• config.py — API keys, paths, CSV settings
• data/raw/dune_program_fees_v2.csv — Canonical 30-day per-program data (500 programs)
• data/raw/dune_daily_program_fees.csv — Daily per-program data (20 days, 82 programs)
• data/mapping/program_categories.csv — 314 programs classified (aot/jit/both/potential/neither)
• 02_transform/build_program_database.py — Merges Dune + mapping → program_database.csv

Prompt Template for Claude Code

When writing a prompt for Claude Code, use this structure:

# [Query Name]

## SQL Query
[SQL in code block]

## Instructions
1. Create this query on Dune: `createDuneQuery("query-name", sql, true)`
2. Execute it: `executeQueryById(query_id)`
3. Poll for results: `getExecutionResults(execution_id)` — retry every 30s until complete
4. Save results to `data/raw/[filename].csv` with semicolon delimiter (`;`), UTF-8
5. Validate: [specific checks]

## Expected Output
- Columns: [list]
- Rows: ~[estimate]
- Period: [dates]