Starter Queries

SELECT
  wallet_address,
  primary_chain,
  portfolio_value_usd,
  volume_last_90d,
  nft_collection_count,
  engagement_score,
  est_ltv
FROM user_onchain_metrics
WHERE portfolio_value_usd IS NOT NULL
ORDER BY portfolio_value_usd DESC, volume_last_90d DESC
LIMIT 50

SELECT
  c.email,
  c.wallet_address,
  uom.engagement_score,
  was.chain,
  was.transaction_count,
  was.active_days_30d,
  was.active_days_90d,
  was.last_activity_at
FROM contacts c
JOIN user_onchain_metrics uom
  ON uom.wallet_address = c.wallet_address
JOIN wallet_activity_summary was
  ON was.wallet_address = c.wallet_address
WHERE c.wallet_address IS NOT NULL
ORDER BY uom.engagement_score DESC, was.active_days_30d DESC
LIMIT 100

SELECT
  wallet_address,
  volume_last_90d,
  transaction_count_90d,
  est_ltv,
  portfolio_value_usd,
  engagement_score,
  last_enriched_at
FROM user_onchain_metrics
WHERE volume_last_90d > 0
ORDER BY est_ltv DESC, volume_last_90d DESC, engagement_score DESC
LIMIT 75

SELECT
  email,
  wallet_address,
  portfolio_value_usd,
  est_ltv,
  days_since_last_activity,
  email_opens,
  churn_risk
FROM contact_360
WHERE churn_risk IN ('high', 'medium')
  AND NOT suppressed
  AND portfolio_value_usd > 0
ORDER BY est_ltv DESC NULLS LAST, portfolio_value_usd DESC NULLS LAST
LIMIT 100

SELECT
  email,
  wallet_address,
  email_opens,
  email_clicks,
  days_since_last_activity,
  primary_chain
FROM contact_360
WHERE email_opens > 0
  AND NOT suppressed
  AND (last_activity_at IS NULL OR days_since_last_activity > 30)
ORDER BY email_clicks DESC, email_opens DESC
LIMIT 100

SELECT
  email,
  wallet_address,
  est_ltv,
  portfolio_value_usd,
  engagement_score,
  email_clicks,
  churn_risk
FROM contact_360
WHERE churn_risk = 'low'
  AND NOT suppressed
  AND est_ltv > 0
ORDER BY est_ltv DESC, engagement_score DESC
LIMIT 100