freemartianFuel - Points
    Updated 2024-07-23
    with deposits AS (
    SELECT
    block_timestamp, tx_hash, from_address, amount_usd, 'ETH' AS symbol
    FROM ethereum.core.ez_native_transfers
    WHERE to_address = '0x19b5cc75846bf6286d599ec116536a333c4c2c14'
    AND origin_function_signature = '0xdf68e9d6'
    AND block_timestamp::date >= '2024-07-08'
    UNION
    SELECT
    block_timestamp, tx_hash, from_address, amount_usd, symbol
    FROM ethereum.core.ez_token_transfers
    WHERE to_address = '0x19b5cc75846bf6286d599ec116536a333c4c2c14'
    AND origin_function_signature = '0xdf68e9d6'
    AND block_timestamp::date >= '2024-07-08'
    ),
    points AS (
    SELECT
    tx_hash,
    from_address,
    amount_usd,
    DATEDIFF('day', block_timestamp, current_date) AS days,
    amount_usd*days*1.5 AS points
    FROM deposits
    )

    SELECT
    from_address,
    SUM(points) AS total_points,
    SUM(amount_usd) AS amount_usd,
    ROW_NUMBER() OVER (ORDER BY total_points DESC) AS rank
    FROM points
    GROUP BY 1
    ORDER BY 2 DESC

    Auto-refreshes every 1 hour
    QueryRunArchived: QueryRun has been archived