Data catalog

raw_events (landing zone)
    │
    ▼
events (deduplicated + parsed)
    │
    ├──▶ users (wallet-identified profiles)
    ├──▶ anonymous_users (pre-wallet profiles)
    ├──▶ sessions (session-level engagement)
    ├──▶ sources (traffic attribution)
    ├──▶ revenue (financial metrics)
    └──▶ identities (anonymous ↔ wallet mapping)

wallet_profiles_events (blockchain data)
    │
    ├──▶ wallet_profiles_mv (global wallet profiles)
    ├──▶ wallet_profiles_chains_mv (per-chain data)
    ├──▶ wallet_profiles_chains_tokens_mv (token holdings)
    └──▶ wallet_profiles_chains_apps_mv (DeFi app usage)

SELECT *
FROM events
ORDER BY timestamp DESC
LIMIT 10

SELECT
    address,
    min(first_seen) AS first_seen,
    max(last_seen) AS last_seen,
    argMinMerge(first_utm_source) AS first_utm_source,
    argMaxMerge(last_utm_source) AS last_utm_source,
    uniqMerge(num_sessions) AS num_sessions,
    sum(revenue) AS revenue,
    sum(volume) AS volume,
    argMaxMerge(location) AS location,
    argMaxMerge(device) AS device,
    argMaxMerge(browser) AS browser
FROM users
GROUP BY address
LIMIT 10

SELECT AVG(latest_hit - first_hit) AS avg_session_length
FROM sessions

SELECT utm_campaign, SUM(revenue) AS total_revenue
FROM revenue
GROUP BY utm_campaign
ORDER BY total_revenue DESC

SELECT
    address,
    anonymous_id,
    session_id,
    minMerge(first_seen) AS connected_at
FROM identities
GROUP BY address, anonymous_id, session_id
ORDER BY connected_at DESC
LIMIT 20

SELECT
    address,
    chain_id,
    argMaxMerge(net_worth_usd) AS net_worth_usd,
    argMaxMerge(tx_count) AS tx_count,
    first_onchain,
    last_onchain
FROM wallet_profiles_chains_mv
GROUP BY address, chain_id, first_onchain, last_onchain
ORDER BY net_worth_usd DESC
LIMIT 50

events.anonymous_id  ──────▶  anonymous_users.anonymous_id
events.address       ──────▶  users.address
events.session_id    ──────▶  sessions.session_id
                               identities.session_id

identities.anonymous_id ───▶  anonymous_users.anonymous_id
identities.address     ────▶  users.address

users.address          ────▶  wallet_profiles_mv.address
                               wallet_profiles_chains_mv.address
                               wallet_profiles_chains_tokens_mv.address
                               wallet_profiles_chains_apps_mv.address

SELECT
    SUM(volume) * {{ alchemy.token_price('0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2', 1, 'usd') }} AS volume_usd
FROM revenue

SELECT
    SUM(revenue) * {{ alchemy.token_price_by_symbol('ETH', 'usd') }} AS revenue_usd
FROM revenue

SELECT
    SUM(revenue) * {{ pyth.token_price('ETH/USD') }} AS revenue_usd
FROM revenue

WHERE timestamp >= '{{date_from}}'

WHERE timestamp < '{{date_to}}'

SELECT
    toDate(timestamp) AS date,
    COUNT(*) AS events
FROM events
WHERE timestamp >= '{{date_from}}' AND timestamp < '{{date_to}}'
GROUP BY date
ORDER BY date

SELECT address, COUNT(*) AS event_count
FROM events
WHERE address != ‘’
GROUP BY address
ORDER BY event_count DESC
LIMIT 10

SELECT
    toDate(timestamp) AS date,
    COUNT(DISTINCT address) AS daily_active_users
FROM events
WHERE timestamp >= now() - INTERVAL 30 DAY
GROUP BY date
ORDER BY date DESC

SELECT AVG(latest_hit - first_hit) AS avg_session_length
FROM sessions

SELECT type, COUNT(*) AS event_count
FROM events
GROUP BY type
ORDER BY event_count DESC

SELECT utm_campaign, SUM(revenue) AS total_revenue
FROM revenue
GROUP BY utm_campaign
ORDER BY total_revenue DESC

SELECT location, COUNT(*) AS session_count
FROM sessions
GROUP BY location
ORDER BY session_count DESC
LIMIT 20

WITH top_browsers AS (
    SELECT browser
    FROM sessions
    GROUP BY browser
    ORDER BY COUNT(*) DESC
    LIMIT 10
)
SELECT device, browser, COUNT(*) AS session_count
FROM sessions
WHERE browser IN (SELECT browser FROM top_browsers)
GROUP BY device, browser
ORDER BY session_count DESC

SELECT
    u.address,
    min(u.first_seen) AS first_seen,
    max(u.last_seen) AS last_seen,
    argMaxMerge(u.location) AS location,
    wp.ens,
    wp.farcaster,
    wp.twitter,
    argMaxMerge(wp.net_worth_usd) AS net_worth_usd
FROM users u
LEFT JOIN wallet_profiles_mv wp ON u.address = wp.address
GROUP BY u.address, wp.ens, wp.farcaster, wp.twitter
ORDER BY net_worth_usd DESC
LIMIT 20

WITH user_locations AS (
    SELECT
        address,
        argMaxMerge(location) AS location
    FROM users
    GROUP BY address
),
app_balances AS (
    SELECT
        id,
        chain_id,
        argMaxMerge(name) AS name,
        argMaxMerge(url) AS url,
        argMaxMerge(balance_usd) AS balance_usd,
        address
    FROM wallet_profiles_chains_apps_mv
    WHERE address IN (
        SELECT address
        FROM user_locations
        WHERE location = ‘US’
    )
    GROUP BY id, chain_id, address
)
SELECT
    id,
    chain_id,
    any(name) AS name,
    any(url) AS url,
    sum(balance_usd) AS total_balance_usd,
    count(DISTINCT address) AS user_count
FROM app_balances
GROUP BY id, chain_id
ORDER BY total_balance_usd DESC
LIMIT 20

SELECT
    address,
    id,
    argMaxMerge(name) AS name,
    chain_id,
    argMaxMerge(balance_usd) AS balance_usd
FROM wallet_profiles_chains_apps_mv
GROUP BY address, chain_id, id
HAVING balance_usd > 500
ORDER BY balance_usd DESC
LIMIT 100

SELECT
    i.address,
    i.anonymous_id,
    i.session_id,
    minMerge(i.first_seen) AS connected_at
FROM identities i
GROUP BY i.address, i.anonymous_id, i.session_id
ORDER BY connected_at DESC
LIMIT 20