SQL Schema Reference

-- Whales active in the last 90 days
SELECT wallet_address, portfolio_value_usd, transaction_count_90d
FROM user_onchain_metrics
WHERE portfolio_value_usd > 10000
ORDER BY portfolio_value_usd DESC
LIMIT 50

-- Churn-risk contacts who used to engage
SELECT email, wallet_address, churn_risk, days_since_last_activity, email_opens
FROM contact_360
WHERE churn_risk IN ('medium', 'high') AND email_opens > 0
ORDER BY days_since_last_activity DESC
LIMIT 100

-- Campaign performance, most recent activity first
SELECT name, sent, unique_opens, unique_clicks, open_rate, click_rate
FROM campaign_performance
ORDER BY last_event_at DESC
LIMIT 25

-- High-value wallets with no email engagement — an in-app push audience
SELECT c.wallet_address, m.portfolio_value_usd, m.engagement_score
FROM contacts c
JOIN user_onchain_metrics m
  ON lower(m.wallet_address) = lower(c.wallet_address)
JOIN audience_profiles p
  ON lower(p.wallet_address) = lower(c.wallet_address)
WHERE m.portfolio_value_usd > 5000
  AND p.inapp_eligible = true
ORDER BY m.portfolio_value_usd DESC
LIMIT 200

-- NFT holders of one collection, deduped to one row per wallet
SELECT wallet_address,
       count(*) AS items,
       max(floor_price_quote) AS best_floor
FROM nft_holdings
WHERE chain = 'base-mainnet'
  AND lower(contract_address) = lower('0xYourCollection')
GROUP BY wallet_address
ORDER BY items DESC
LIMIT 100

-- Clicked a campaign, then stayed active onchain
SELECT c.email, c.wallet_address, w.last_activity_at, w.transaction_count
FROM contacts c
JOIN delivery_events e
  ON e.recipient_hmac = c.email_hmac
JOIN wallet_activity_summary w
  ON lower(w.wallet_address) = lower(c.wallet_address)
WHERE e.event_type = 'email.click'
  AND w.last_activity_at > e.occurred_at
ORDER BY w.last_activity_at DESC
LIMIT 100