flipside

    💎✋ Diamond Hand Holders, MULTI DAY
    noanuman-1x1vZp💎✋ Diamond Hand Holders, MULTI DAY
    Updated 2024-10-15
    WITH days AS (
    SELECT
    ROW_NUMBER() OVER (ORDER BY NULL) - 1 AS day_number
    FROM
    TABLE(GENERATOR(ROWCOUNT => 2))
    ),

    combined_transfers_yesterday AS (
    SELECT
    user_address,
    block_timestamp,
    d.day_number,
    balance
    FROM
    ethereum.core.fact_token_balances tb
    LEFT JOIN days d
    ON tb.block_timestamp::DATE <= CURRENT_DATE() - d.day_number
    WHERE
    LOWER(tb.contract_address) = LOWER('0xaaeE1A9723aaDB7afA2810263653A34bA2C21C7a')
    ),

    ranked_transfers_yesterday AS (
    SELECT
    user_address,
    block_timestamp,
    balance,
    d.day_number, -- Include the day_date from the days CTE
    ROW_NUMBER() OVER (PARTITION BY d.day_number, user_address ORDER BY block_timestamp DESC) AS rn
    FROM combined_transfers_yesterday ct
    JOIN days d ON ct.day_number = d.day_number
    ),

    filtered_holders_yesterday AS (
    SELECT
    user_address,
    block_timestamp,
    QueryRunArchived: QueryRun has been archived