rw2023Total claimed, avg, mode
    Updated 2023-12-27
    WITH TimeAdjusted AS (
    SELECT
    GET_PATH(DECODED_LOG, 'account')::STRING AS address,
    TRY_TO_NUMBER(GET_PATH(DECODED_LOG, 'amount')::STRING) / POW(10, 18) AS amount
    FROM
    ethereum.core.fact_decoded_event_logs
    WHERE
    EVENT_NAME = 'Claimed'
    AND LOWER(CONTRACT_ADDRESS) = LOWER('0x090d4613473dee047c3f2706764f49e0821d256e')
    ),
    AddressAmounts AS (
    SELECT
    address,
    SUM(amount) AS total_amount -- Monto total reclamado por dirección
    FROM
    TimeAdjusted
    GROUP BY
    address
    ),
    AggregatedData AS (
    SELECT
    SUM(total_amount) / COUNT(*) AS average_claimed, -- Media de la cantidad total reclamada
    COUNT(*) AS total_addresses, -- Total de direcciones únicas
    SUM(total_amount) AS total_claimed_uni -- Total claimed UNI tokens
    FROM
    AddressAmounts
    ),
    ModeCalculation AS (
    SELECT
    total_amount,
    COUNT(*) AS frequency
    FROM
    AddressAmounts
    GROUP BY
    total_amount
    ),
    QueryRunArchived: QueryRun has been archived