Get User Estimated Based Rewards

Simply fork this Dune table. Then, delete the existing query and replace with the following query below and <moxie-user-id> added as the desired user to search:

WITH
    multiplierData as (
        select
            contract_address,
            reward_multiplier,
            --min e.g. 1 USD to count the tx
            5 as reward_multiplier_min_usd,
            1 as hasRelayer
        from
            dune.moxieprotocol.result_allowlist_contracts_v_2
    ),
    txs as (
        SELECT
            DATE_TRUNC('day', f.block_time) AS timestamp,
            f.tx_hash,
            case
                when hasRelayer = 1 then txTransfer."from"
                else f.tx_from
            end as tx_from,
            tx_to,
            f.gas_used,
            f.tx_fee,
            f.tx_fee_usd,
            case
                when coalesce(usdTransfered, 0) >= coalesce(reward_multiplier_min_usd, 0) then coalesce(usdTransfered, 0)
                else 0
            end as usdTransfered,
            case
                when coalesce(usdTransfered, 0) >= coalesce(reward_multiplier_min_usd, 0) then coalesce(reward_multiplier, 0)
                else 0
            end as reward_multiplier,
            row_number() over (
                partition by
                    f.tx_hash
                order by
                    case
                        when coalesce(usdTransfered, 0) >= coalesce(reward_multiplier_min_usd, 0) then coalesce(reward_multiplier, 0)
                        else 0
                    end desc
            ) as prio
        FROM
            gas.fees f
            inner JOIN (multiplierData) wc ON f.tx_to = wc.contract_address
            -- and coalesce(usdTransfered,0)+ coalesce(et.value/1e18*p.price,0)  >= coalesce(reward_multiplier_min_usd, 0)
            inner join (
                select
                    tx_hash,
                    "from",
                    amount_usd usdTransfered,
                    row_number() over (
                        partition by
                            tx_hash
                        order by
                            t.evt_index
                    ) as rank
                from
                    tokens_base.transfers t
                where
                    DATE_TRUNC('day', t.block_time) = CURRENT_DATE
                    and coalesce(amount_usd, 0) > 0
            ) txTransfer on f.tx_hash = txTransfer.tx_hash
            and rank = 1
            inner join dune.moxieprotocol.wallets w on txTransfer."from" = w.wallet_address
            AND w.status = 'ACTIVE'
        where
            f.blockchain = 'base'
            and DATE_TRUNC('day', f.block_time) = CURRENT_DATE
            and coalesce(usdTransfered, 0) >= coalesce(reward_multiplier_min_usd, 0)
    ),
    gas_spent AS (
        SELECT
            timestamp,
            tx_from,
            SUM(gas_used * coalesce(reward_multiplier, 0)) AS total_gas_used,
            SUM(tx_fee) AS total_gas_spent_native,
            SUM(tx_fee_usd) AS total_gas_spent_usd,
            sum(usdTransfered) as usdTransfered,
            count(1) as cnt_tx
        from
            txs
        WHERE
            prio = 1
        GROUP BY
            1,
            tx_from
    ), user_score AS (
    SELECT
        w.user_id AS user_id,
        MAX(u.score) AS score,
        SUM(gas_spent.total_gas_spent_native) AS total_gas_spent_native,
        SUM(gas_spent.total_gas_spent_usd) AS total_gas_spent_usd,
        SUM(gas_spent.total_gas_used) AS total_gas_used,
        MAX(u.score) * SUM(gas_spent.total_gas_used) AS reward_contribution
    FROM
        dune.moxieprotocol.wallets w
    JOIN gas_spent ON gas_spent.tx_from = w.wallet_address
    JOIN dune.moxieprotocol.users_score_yesterday u ON w.user_id = u.user_id
    WHERE u.status = 'ACTIVE' AND w.status = 'ACTIVE' AND u.score > 0.0
    GROUP BY w.user_id
), total_score AS (
    SELECT
        SUM(reward_contribution) AS total_score
    FROM
        user_score
), total_pool AS (
    SELECT CAST(value AS UINT256) AS pool FROM dune.moxieprotocol.constants WHERE KEY = 'DAILY_MOXIE_ALLOCATION'
), rewards AS (
    SELECT
        u.*,
        (u.reward_contribution / t.total_score) * 100 AS percent_contribution,
        (u.reward_contribution / t.total_score) * (tp.pool) AS reward_amount
    FROM
        user_score u
    CROSS JOIN
        total_score t
    CROSS JOIN
        total_pool tp
) SELECT
    *,
    NOW() AS last_computed_at
FROM
    rewards
WHERE rewards.user_id='<moxie-user-id>'
ORDER BY
    reward_amount DESC;

Once that's done, simply click on the Run button to get the result.