Staking Rewards 2/2025

ℹ️🌐 Governance
,
1

Staking Rewards 2/2025 and Reward Calc. Update

We now use this Dune SQL Query to track and calculate the following:

Position Tracking

  1. ordered_series: Creates an ordered sequence of trades, assigning row numbers to track first and last positions in each time period
  2. lp_number_position: Counts the number of positions in each time period
  3. last_pool_value_by_range & first_pool_value_by_range: Calculate position values at the beginning and end of each period

Performance Metrics

  1. accrued_fees: Sums up fees earned during each period
  2. gas_fee: Calculates average gas costs for the period
  3. adverse_selection_pnl: Computes position value changes (PnL) from price movements

Cumulative Results

  1. cumulative_log_pnl: Tracks cumulative logarithmic returns over time
  2. cumulative_pnl: Converts logarithmic returns to percentage, adds up fees and costs

This results in the output:

  • Cumulative percentage return on liquidity
  • Cumulative PnL excluding fees
  • Cumulative gas costs
  • Cumulative fees earned
  • Total cumulative PnL in USD

Performance-Based Distribution

  • Use the cumulative_pnl_usd field to reward LPs who have experienced losses or smaller gains
  • This is how we calculate a subsidy for rewards where LPs with negative PnL receive larger subsidies
  • Subsidy = (Base Amount) + (Negative PnL Adjustment Factor * Total Reward Pool)

Spreadsheet is here: FOLD Rewards 2/2025 - Google Drive

Here’s the data formatted as a markdown table:

lp_owner pool_proportion subsidy_amount subsidy_percentage
0x66a9893cc07d91d95644aedd05d03f95e1dba8af 0.1070 267.53 10.70
0xf2614a233c7c3e7f08b1f887ba133a13f1eb2c55 0.0991 247.65 9.91
0x66a9893cc07d91d95644aedd05d03f95e1dba8af 0.0939 234.65 9.39
0x3fc91a3afd70395cd496c647d5a6cc9d4b2b7fad 0.0772 193.04 7.72
0x24902aa0cf0000a08c0ea0b003b0c0bf600000e0 0.0731 182.80 7.31 
WITH
    prices_usd AS (
        SELECT
            minute,
            price,
            decimals,
            contract_address
        FROM
            prices.usd
        WHERE
            blockchain = 'ethereum'
            AND minute > CAST('2021-05-01 00:00' AS timestamp)
            AND minute > CAST('{{Start Day}}' AS timestamp)
    ),
    pool_info AS (
        SELECT
            token0,
            token1,
            fee,
            tickSpacing
        FROM
            uniswap_v3_ethereum.Factory_evt_PoolCreated
        WHERE
            pool = {{Pool}}
    ),
    pool_info_with_price AS (
        SELECT
            t0.minute AS minute,
            token0,
            token1,
            fee,
            tickSpacing,
            t0.decimals AS t0_decimals,
            t0.price AS t0_price,
            t1.decimals AS t1_decimals,
            t1.price AS t1_price
        FROM
            pool_info
            LEFT JOIN prices_usd t0 ON (t0.contract_address = token0)
            LEFT JOIN prices_usd t1 ON (
                t1.contract_address = token1
                AND t0.minute = t1.minute
            )
    ),
    pool_trades_raw_data AS (
        SELECT
            evt_block_number,
            evt_block_time,
            evt_index,
            evt_tx_hash,
            token0,
            token1,
            fee,
            t0_decimals,
            t0_price,
            t1_decimals,
            t1_price,
            sender AS lp_owner,
            amount0 * pow(10, - t0_decimals) AS amount0,
            amount1 * pow(10, - t1_decimals) AS amount1,
            CAST(liquidity AS double) AS liquidity,
            pow((sqrtPriceX96 * pow(2, -96)), 2) AS pool_price,
            pow(1.0001, tick - mod(tick, tickSpacing)) AS price_low,
            pow(
                1.0001,
                tick - mod(tick, tickSpacing) + tickSpacing
            ) AS price_high,
            abs(
                amount0 * pow(10, - t0_decimals) / (amount1 * pow(10, - t1_decimals))
            ) AS effective_price,
            CASE
                WHEN CAST(amount1 AS double) > 0 THEN amount1 * fee / 1e6 * t1_price * pow(10, - t1_decimals)
                ELSE amount0 * fee / 1e6 * t0_price * pow(10, - t0_decimals)
            END AS fee_usd
        FROM
            uniswap_v3_ethereum.Pair_evt_Swap
            LEFT JOIN pool_info_with_price ON minute = DATE_TRUNC('minute', evt_block_time)
        WHERE
            contract_address = {{Pool}}
            AND amount0 * pow(10, - t0_decimals) != 0
            AND amount1 * pow(10, - t1_decimals) != 0
            AND evt_block_time > CAST('{{Start Day}}' AS timestamp)
    ),
    ordered_series AS (
        SELECT
            row_number() OVER (
                PARTITION BY
                    date_trunc('{{Position lenght}}', evt_block_time),
                    price_low,
                    liquidity
                ORDER BY
                    evt_block_time DESC
            ) AS row_number_desc,
            row_number() OVER (
                PARTITION BY
                    date_trunc('{{Position lenght}}', evt_block_time),
                    price_low,
                    liquidity
                ORDER BY
                    evt_block_time ASC
            ) AS row_number_asc,
            date_trunc('{{Position lenght}}', evt_block_time) AS dt,
            evt_block_time,
            evt_block_number,
            price_low,
            price_high,
            pool_price,
            liquidity,
            amount0,
            amount1,
            fee_usd,
            lp_owner,
            t0_decimals,
            t0_price,
            t1_decimals,
            t1_price
        FROM
            pool_trades_raw_data
        WHERE
            evt_block_time > CAST('{{Start Day}}' AS timestamp)
    ),
    lp_number_position AS (
        SELECT
            dt AS lp_dt,
            COUNT(*) AS n_position
        FROM
            ordered_series
        WHERE
            row_number_asc = 1
        GROUP BY
            1
    ),
    last_pool_value_by_range AS (
        SELECT
            dt AS end_position_dt,
            price_low AS last_price_low,
            price_high AS last_price_high,
            pool_price AS last_pool_price,
            (
                2 * SQRT(pool_price) - pool_price / SQRT(price_high) - SQRT(price_low)
            ) AS lp_position_value_end_unit_of_liq,
            t1_decimals,
            t1_price
        FROM
            ordered_series
        WHERE
            row_number_desc = 1
    ),
    first_pool_value_by_range AS (
        SELECT
            dt AS start_position_dt,
            price_high AS first_price_high,
            price_low AS first_price_low,
            pool_price AS first_pool_price,
            (
                2 * SQRT(pool_price) - pool_price / SQRT(price_high) - SQRT(price_low)
            ) AS lp_position_value_start_unit_of_liq,
            (
                2 * SQRT(pool_price) - pool_price / SQRT(price_high) - SQRT(price_low)
            ) * liquidity * pow(10, - t1_decimals) * t1_price AS lp_position_value_start_usd,
            n_position
        FROM
            ordered_series
            INNER JOIN lp_number_position ON dt = lp_dt
        WHERE
            row_number_asc = 1
    ),
    accrued_fees AS (
        SELECT
            date_trunc('{{Position lenght}}', dt) AS fees_dt,
            SUM(fee_usd) AS accrued_fees_usd
        FROM
            ordered_series
        GROUP BY
            1
    ),
    gas_fee AS (
        SELECT
            date_trunc('{{Position lenght}}', block_time) AS gas_dt,
            AVG(gas_price * pow(10, -18) * gas_used) AS avg_gas
        FROM
            ethereum.transactions
        WHERE
            block_time >= CAST('{{Start Day}}' AS timestamp)
        GROUP BY
            1
    ),
    adverse_selection_pnl AS (
        SELECT
            end_position_dt,
            avg_gas * t1_price AS avg_gas_usd,
            accrued_fees_usd,
            SUM(
                lp_position_value_start_usd * (
                    lp_position_value_end_unit_of_liq - lp_position_value_start_unit_of_liq
                ) / lp_position_value_start_unit_of_liq
            ) AS lp_value,
            SUM(
                (
                    lp_position_value_end_unit_of_liq - lp_position_value_start_unit_of_liq
                ) / lp_position_value_start_unit_of_liq
            ) AS lp_value_pc
        FROM
            last_pool_value_by_range
            INNER JOIN first_pool_value_by_range ON (
                first_price_low = last_price_low
                AND first_price_high = last_price_high
            )
            INNER JOIN accrued_fees ON fees_dt = end_position_dt
            INNER JOIN gas_fee ON gas_dt = end_position_dt
        WHERE
            date_diff(
                '{{Position lenght}}',
                start_position_dt,
                end_position_dt
            ) <= 1
            AND start_position_dt < end_position_dt
        GROUP BY
            1,
            2,
            3
    ),
    cumulative_log_pnl AS (
        SELECT
            adverse_selection_pnl.end_position_dt AS day,
            SUM(ln(1 + lp_value_pc)) OVER (
                ORDER BY
                    adverse_selection_pnl.end_position_dt ASC
            ) AS cumulative_lp_value_log,
            SUM(lp_value) OVER (
                ORDER BY
                    adverse_selection_pnl.end_position_dt ASC
            ) AS cumulative_lp_pnl_no_fees,
            SUM(accrued_fees_usd) OVER (
                ORDER BY
                    adverse_selection_pnl.end_position_dt ASC
            ) AS cumulative_accrued_fees_usd,
            SUM(avg_gas_usd) OVER (
                ORDER BY
                    adverse_selection_pnl.end_position_dt ASC
            ) AS cumulative_avg_gas_usd
        FROM
            adverse_selection_pnl
    ),
    cumulative_pnl AS (
        SELECT
            day,
            (EXP(cumulative_lp_value_log) - 1) * 100 AS cumulative_lp_value,
            cumulative_lp_pnl_no_fees,
            cumulative_avg_gas_usd,
            cumulative_accrued_fees_usd,
            cumulative_lp_pnl_no_fees + cumulative_accrued_fees_usd AS cumulative_pnl_usd
        FROM
            cumulative_log_pnl
    )
SELECT
    lp_owner,
    cumulative_pnl.*
FROM
    cumulative_pnl
    INNER JOIN ordered_series ON cumulative_pnl.day = date_trunc(
        '{{Position lenght}}',
        ordered_series.evt_block_time
    )
WHERE
    ordered_series.row_number_asc = 1;
1 Like