flipside

    Holders for Yestday
    noanuman-1x1vZpHolders for Yestday
    Updated 2024-10-31
    WITH combined_transfers AS (
    SELECT
    user_address,
    block_timestamp,
    balance
    FROM
    ethereum.core.fact_token_balances
    WHERE
    LOWER(contract_address) = LOWER('0xaaeE1A9723aaDB7afA2810263653A34bA2C21C7a')
    AND block_timestamp :: DATE <= CURRENT_DATE() - 1
    ),

    ranked_transfers AS (
    -- Use ROW_NUMBER to rank transactions per user by block_timestamp
    SELECT
    user_address,
    block_timestamp,
    balance,
    ROW_NUMBER() OVER ( PARTITION BY user_address ORDER BY block_timestamp DESC) AS rn
    FROM combined_transfers
    ),

    -- Select only the last transaction (rank 1) for each user
    a AS (SELECT
    user_address,
    balance,
    block_timestamp,
    COUNT(DISTINCT user_address) OVER () AS total_unique_users
    FROM ranked_transfers
    WHERE rn = 1
    AND balance>0
    )

    SELECT *
    FROM ranked_transfers;
    QueryRunArchived: QueryRun has been archived