This repository manages SQL queries and AI prompts for analyzing data from the Stellar blockchain using Dune Analytics. It integrates with DuneQueryRepo for version control and synchronization with Dune's platform.
- Python 3.8+
- Dune Analytics account with Plus plan (for API access)
- Dune API key (generate here)
pip install -r requirements.txtOr manually:
pip install dune-client python-dotenv pyyamlCopy the example .env file and add your Dune API key:
cp configs/dune.env.example configs/.envEdit configs/.env:
DUNE_API_KEY=your_api_key_hereAdd your query IDs to configs/queries.yml:
queries:
- 5268612 # Defindex Vaults
- 5268613 # Add more query IDs heredune-stellar-analytics/
├── configs/
│ ├── .env # API credentials (git-ignored)
│ ├── dune.env.example # Example environment file
│ └── queries.yml # Query ID configuration
├── queries/
│ └── 5268612_vaults.sql # SQL query files (ID_name.sql format)
├── scripts/
│ └── run_query.py # Execute queries and display results
└── prompts/ # AI prompts for analysis
This project follows the DuneQueryRepo pattern for managing Dune queries with version control.
Download queries from Dune to your local repository:
python scripts/pull_from_dune.pyThis creates/updates SQL files in queries/ with the format {query_id}_{query_name}.sql.
Upload local query changes back to Dune:
python scripts/push_to_dune.pyImportant:
- You must own the queries or be part of the team that owns them
- Keep the
{query_id}_prefix in filenames - Add
-- already part of a query repocomment to managed queries
python scripts/run_query.pyRuns the first query in queries.yml (5268612 - DeFindex Vaults).
python scripts/run_any_query.pyShows an interactive menu to select and run any query from the dashboard.
Or run directly by query ID:
# Run query 5926839 (Latest USD TVL), show 5 results
python scripts/run_any_query.py 5926839 5
# Run query 5576346 (Aggregated Stats), show 10 results (default)
python scripts/run_any_query.py 5576346This repository includes all queries from the DeFindex Dashboard:
Extracts vault contract addresses over time from the Stellar network.
File: queries/5268612_defindex_vaults.sql
Returns: closed_at, vault
Extended vault information including names, assets, and symbols.
File: queries/5782251_defindex_vaults_with_more_info_(name,_assets,_symbol).sql
Returns: Vault metadata with asset details
Historical TVL data with daily granularity, filling gaps for continuous time series.
File: queries/5906479_defindex_vaults_tvl,_filled_days.sql
Returns: Daily TVL per vault with USD values
Most recent TVL data for each vault.
File: queries/5926821_defindex_latest_vaults_data.sql
Returns: Latest snapshot of vault TVL data
Aggregated total USD TVL across all vaults.
File: queries/5926839_defindex_latest_usd_tvl.sql
Returns: Single value - total protocol TVL in USD
Hourly TVL data filtered to show only periods with deposit/withdraw activity.
File: queries/5901637_defindex_tvl_only_hours_with_events.sql
Returns: Hourly TVL data with event timestamps
All deposit and withdraw events across DeFindex vaults.
File: queries/5900680_defindex_vaults_events.sql
Returns: Event log with vault, user, amount, and transaction details
Summary statistics including unique accounts and transaction counts per vault.
File: queries/5576346_defindex_aggregated_stats.sql
Returns: Per-vault and protocol-wide user and transaction metrics
📊 STELLAR_TABLES_REFERENCE.md - Referencia completa de tablas
- Campos detallados y tipos de datos
- Ejemplos de queries por tabla
- Patrones de JOIN entre tablas
- Mejores prácticas y casos de uso
- stellar.contract_data - Smart contract state data
- stellar.history_contract_events - Contract event logs
- stellar.history_transactions - Transaction history
- stellar.history_operations - Operation history
- Create Query on Dune: Build and test your query in the Dune web interface
- Add Query ID: Add the query ID to
configs/queries.yml - Pull Query: Run
pull_from_dune.pyto download the SQL - Version Control: Commit the SQL file to git
- Iterate Locally: Edit SQL files locally
- Push Updates: Run
push_to_dune.pyto sync changes back to Dune - Run Analysis: Execute queries via
run_query.pyor use AI to generate insights
Use prompts in the prompts/ directory with GPT or Claude to:
- Generate visualizations from query results
- Identify trends and patterns
- Create dashboards and reports
- Optimize query performance
- Fork the repository
- Create a feature branch
- Add/modify queries
- Submit a pull request
📘 DATA_FLOW.md - Documentación completa del flujo de datos
- Arquitectura de 5 niveles de queries
- Explicación detallada de cada query
- Dependencias y tablas materializadas
- Casos de uso y ejemplos de queries
⚡ QUICK_REFERENCE.md - Referencia rápida
- Jerarquía de queries en una tabla
- Patrones SQL comunes
- Snippets útiles para análisis
- Limitaciones conocidas