Predicting Solana Wallet Values With BigQuery ML

• 20/1/2024
• One-minute read

BigQuery ML lets analytics teams build machine-learning models with SQL. This walkthrough demonstrates forecasting the 7‑day balance of high-value Solana wallets using public blockchain data—no data export required.

Steps

1. Create a Dataset: CREATE SCHEMA solana_prediction OPTIONS(location='us');
2. Select Top Wallets: Aggregate the public dataset bigquery-public-data.crypto_solana_mainnet_us.Accounts to find wallets with average balances above 1,000 SOL.
3. Build a Daily Timeseries: For each wallet, capture the last balance per day and compute helper features (lagged balances, moving averages).
4. Train the Model:

   CREATE OR REPLACE MODEL solana_prediction.wallet_forecast
   OPTIONS(model_type='linear_reg', input_label_cols=['target_balance_7d']) AS
   SELECT
     sol_balance,
     prev_day_balance,
     prev_week_balance,
     day_change,
     week_change,
     ma_7,
     ma_30,
     SAFE_DIVIDE(ma_7, ma_30) AS trend_indicator,
     target_balance_7d
   FROM solana_prediction.training_view
   WHERE prev_day_balance IS NOT NULL
     AND prev_week_balance IS NOT NULL;
   

5. Evaluate: ML.EVALUATE on holdout data to review RMSE and R².
6. Predict: ML.PREDICT to obtain forecasted balances for the next seven days.

Operational Tips

• Keep the model up to date with scheduled queries (CREATE SCHEDULE FOR RECURRING QUERY).
• Store sensitive outputs in private datasets; public blockchain data may still reveal trading strategies.
• Validate signals with domain experts before trading—regulations may apply.

Further Reading

• BigQuery ML Documentation
• Solana Public Dataset