ERC-8004 Events

extra_fields:clientAddress::STRING    -- rater address
extra_fields:value::INT               -- feedback score
extra_fields:tag1::STRING             -- primary tag

SELECT
    COUNT(DISTINCT CASE WHEN event_name = 'Registered'
          THEN chain || '-' || agent_id END) AS total_agents,
    COUNT(CASE WHEN event_name = 'NewFeedback' THEN 1 END) AS total_feedback,
    COUNT(DISTINCT CASE WHEN event_name = 'NewFeedback'
          THEN extra_fields:clientAddress::STRING END) AS unique_raters,
    ROUND(AVG(CASE WHEN event_name = 'NewFeedback'
        THEN extra_fields:value::FLOAT
             / NULLIF(POW(10, COALESCE(extra_fields:valueDecimals::INT, 0)), 0)
        END), 1) AS avg_feedback_score,
    ROUND(COUNT(CASE WHEN event_name = 'FeedbackRevoked' THEN 1 END) * 100.0
          / NULLIF(COUNT(CASE WHEN event_name = 'NewFeedback' THEN 1 END), 0), 3)
        AS revocation_rate_pct
FROM crosschain.agents.erc8004_events;

SELECT
    DATE_TRUNC('day', block_timestamp)::DATE AS dt,
    chain,
    COUNT(DISTINCT agent_id) AS agents_registered
FROM crosschain.agents.erc8004_events
WHERE event_name = 'Registered'
GROUP BY ALL
ORDER BY dt;

SELECT
    chain,
    COUNT(DISTINCT CASE WHEN event_name = 'Registered' THEN agent_id END) AS agents,
    COUNT(CASE WHEN event_name = 'NewFeedback' THEN 1 END) AS feedback_count,
    COUNT(DISTINCT CASE WHEN event_name = 'NewFeedback'
          THEN extra_fields:clientAddress::STRING END) AS unique_raters,
    ROUND(AVG(CASE WHEN event_name = 'NewFeedback'
          THEN extra_fields:value::FLOAT END), 1) AS avg_score
FROM crosschain.agents.erc8004_events
GROUP BY chain
ORDER BY agents DESC;

WITH scores AS (
    SELECT extra_fields:value::INT AS score
    FROM crosschain.agents.erc8004_events
    WHERE event_name = 'NewFeedback' AND extra_fields:value IS NOT NULL
)
SELECT
    CASE
        WHEN score BETWEEN 0 AND 10 THEN '0-10'
        WHEN score BETWEEN 11 AND 20 THEN '11-20'
        WHEN score BETWEEN 21 AND 30 THEN '21-30'
        WHEN score BETWEEN 31 AND 40 THEN '31-40'
        WHEN score BETWEEN 41 AND 50 THEN '41-50'
        WHEN score BETWEEN 51 AND 60 THEN '51-60'
        WHEN score BETWEEN 61 AND 70 THEN '61-70'
        WHEN score BETWEEN 71 AND 80 THEN '71-80'
        WHEN score BETWEEN 81 AND 90 THEN '81-90'
        WHEN score BETWEEN 91 AND 100 THEN '91-100'
    END AS score_range,
    COUNT(*) AS feedbacks,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) AS pct_of_total
FROM scores
GROUP BY ALL
ORDER BY score_range;

WITH agent_stats AS (
    SELECT
        agent_id,
        chain,
        COUNT(CASE WHEN event_name = 'NewFeedback' THEN 1 END) AS feedback_count,
        AVG(CASE WHEN event_name = 'NewFeedback'
            THEN extra_fields:value::FLOAT END) AS avg_score,
        COUNT(CASE WHEN event_name = 'ResponseAppended' THEN 1 END) AS response_count,
        MIN(CASE WHEN event_name = 'Registered'
            THEN block_timestamp END) AS registered_at
    FROM crosschain.agents.erc8004_events
    GROUP BY agent_id, chain
    HAVING feedback_count > 0
)
SELECT
    agent_id, chain, feedback_count,
    ROUND(avg_score, 1) AS avg_score,
    response_count,
    registered_at::DATE AS registered_date
FROM agent_stats
ORDER BY feedback_count DESC
LIMIT 25;

SELECT
    agent_id, chain,
    extra_fields:clientAddress::STRING AS rater,
    extra_fields:value::INT AS score,
    extra_fields:tag1::STRING AS tag1,
    TRY_PARSE_JSON(TRY_BASE64_DECODE_STRING(
        SPLIT_PART(extra_fields:feedbackURI::STRING, ',', 2)
    )):comment::STRING AS comment,
    block_timestamp::DATE AS feedback_date
FROM crosschain.agents.erc8004_events
WHERE event_name = 'NewFeedback'
  AND extra_fields:feedbackURI::STRING LIKE 'data:application/json;base64,%'
ORDER BY block_timestamp DESC
LIMIT 50;

SELECT
    DATE_TRUNC('day', block_timestamp)::DATE AS dt,
    COUNT(CASE WHEN event_name = 'NewFeedback' THEN 1 END) AS new_feedback,
    COUNT(CASE WHEN event_name = 'FeedbackRevoked' THEN 1 END) AS revoked,
    COUNT(CASE WHEN event_name = 'ResponseAppended' THEN 1 END) AS responses,
    COUNT(DISTINCT CASE WHEN event_name = 'NewFeedback'
          THEN extra_fields:clientAddress::STRING END) AS unique_raters
FROM crosschain.agents.erc8004_events
WHERE event_name IN ('NewFeedback', 'FeedbackRevoked', 'ResponseAppended')
GROUP BY ALL
ORDER BY dt;