Compounding Salary Promotions Over Time

This chapter explores how to calculate compounding salary promotions over time using advanced SQL techniques. We analyze why naive percentage additions fail in financial calculations and how to solve sequential, compounding updates using Recursive Common Table Expressions (CTEs) in SQL.

1. The Problem with Naive Percentage Additions

When calculating salary increases or decreases that happen sequentially over time, a common intuition is to simply sum up all the percentage changes and apply them all at once to the initial salary. However, financial mathematics does not work this way; percentage changes are multiplicative and compounding, not additive.

Real-Life Analogy & Mathematical Reality

Imagine an employee with an initial salary of $50,000 who receives a +10% increase, followed by another +10% increase, and then a -20% decrease.

• The Naive (Incorrect) Approach:
  • Sum the percentages: $10\% + 10\% - 20\% = 0\%$ total change.
  • Or, if we had a +10% and +20%, someone might mistakenly calculate: 10% + 20% = 30%.
  • Applying 30% directly to $50,000:

• Why this is wrong: It ignores the compounding effect where each promotion is applied to the new, updated salary, not the original starting salary.

• The Correct (Compounding) Approach:
  • Step 1: Apply the first 10% increase to $50,000:

• Step 2: Apply the next 20% increase (or decrease) to the new baseline of $55,000:

Notice the difference: $65,000 (naive) vs. $66,000 (compounding). To solve this in SQL, we cannot do a single aggregate sum; we must simulate the promotion timeline step-by-step using iteration.

2. Preparing the Promotion Timeline with Window Functions

To process promotions chronologically for each employee, we must first sequence them. If an employee has multiple promotions, we need to know whether a promotion is the 1st, 2nd, 3rd, or 4th in order.

We achieve this by utilizing the ROW_NUMBER() window function partitioned by employee_id and ordered by promotion_date.

SELECT 
    employee_id,
    promotion_date,
    percentage_change,
    ROW_NUMBER() OVER (
        PARTITION BY employee_id 
        ORDER BY promotion_date ASC
    ) AS promo_id
FROM promotion_table;

Flow Walkthrough: Sequence Generation

We wrap this query into a Common Table Expression (CTE) named promos so we can reference these sequential promotion IDs in our recursive logic.

3. Designing the Recursive CTE Architecture

Recursive Common Table Expressions (CTEs) allow a query to reference itself repeatedly until a condition is met. This makes them ideal for traversing sequential event streams like employee promotions over time.

A Recursive CTE consists of two main parts:

1. The Anchor Member: The initial starting point (Base Case). For our salary problem, this is the employee's initial joining salary before any promotions occur (promo_id = 0).
2. The Recursive Member: The looping mechanism that joins the recursive result set with our ordered promos table, incrementing through each promotion step-by-step.

4. Setting Up the Anchor Member (Base Case)

The anchor member establishes the baseline state for every employee before any promotion is applied. At promo_id = 0, the current salary equals the initial joining salary.

WITH promos AS (
    -- Step 1: Sequence the promotions
    SELECT 
        employee_id,
        promotion_date,
        percentage_change,
        ROW_NUMBER() OVER (
            PARTITION BY employee_id 
            ORDER BY promotion_date ASC
        ) AS promo_id
    FROM promotion_table
),
RCTE AS (
    -- Step 2: Define the Anchor Member (Initial State)
    SELECT 
        id,
        name,
        joining_salary AS initial_salary,
        joining_salary AS current_salary,
        0 AS promo_id
    FROM employees
    
    UNION ALL
    
    -- Step 3: Define the Recursive Member (Pending)
    -- (We will build this in the next section)
);

Understanding Anchor Columns

• id & name: Employee identifiers that remain static throughout all iterations.
• initial_salary: Preserves the original starting salary for reference.
• current_salary: Initialized to match joining_salary, but will be updated dynamically in each recursive step.
• promo_id: Set to 0 to signify that zero promotions have been applied yet.

5. Writing the Recursive Member (Iterating Through Promotions)

Now we must write the recursive portion of the CTE. In each iteration, the recursive query joins our running result set (RCTE) with the sequenced promos table where the promotion ID matches the next step (RCTE.promo_id + 1).

WITH promos AS (
    SELECT 
        employee_id,
        percentage_change,
        ROW_NUMBER() OVER (
            PARTITION BY employee_id 
            ORDER BY promotion_date ASC
        ) AS promo_id
    FROM promotion_table
),
RCTE AS (
    -- Anchor Member
    SELECT 
        id,
        name,
        joining_salary AS initial_salary,
        joining_salary AS current_salary,
        0 AS promo_id
    FROM employees

    UNION ALL

    -- Recursive Member
    SELECT 
        r.id,
        r.name,
        r.initial_salary,
        -- Apply compounding percentage change to current salary
        r.current_salary + (r.current_salary * p.percentage_change / 100.0) AS current_salary,
        p.promo_id
    FROM RCTE r
    JOIN promos p 
      ON r.id = p.employee_id 
     AND p.promo_id = r.promo_id + 1
)
SELECT * 
FROM RCTE;

6. Step-by-Step Execution Flow Walkthrough

Let's trace how the recursive engine processes an employee through multiple iterations:

Iteration 0 (Anchor Member):
   - Employee ID: 101, Name: Alice
   - initial_salary = $50,000
   - current_salary = $50,000
   - promo_id = 0

Iteration 1 (Joins promos where promo_id = 0 + 1 = 1):
   - Finds Promo #1 (e.g., +10% increase)
   - current_salary = $50,000 + ($50,000 * 10 / 100) = $55,000
   - promo_id = 1

Iteration 2 (Joins promos where promo_id = 1 + 1 = 2):
   - Finds Promo #2 (e.g., +20% increase)
   - current_salary = $55,000 + ($55,000 * 20 / 100) = $66,000
   - promo_id = 2

Iteration 3 (Joins promos where promo_id = 2 + 1 = 3):
   - No more promotions exist for Employee ID 101.
   - Recursion stops for this branch.

7. Extracting Final Current Salaries

Once the recursive CTE has processed all promotion steps for every employee, the final output contains rows for every intermediate step (Promo 0, Promo 1, Promo 2, etc.).

To get only the latest current salary for each employee, we wrap our recursive CTE in an outer query using a ranking function or grouping:

WITH promos AS (
    SELECT 
        employee_id,
        percentage_change,
        ROW_NUMBER() OVER (
            PARTITION BY employee_id 
            ORDER BY promotion_date ASC
        ) AS promo_id
    FROM promotion_table
),
RCTE AS (
    SELECT 
        id,
        name,
        joining_salary AS initial_salary,
        joining_salary AS current_salary,
        0 AS promo_id
    FROM employees

    UNION ALL

    SELECT 
        r.id,
        r.name,
        r.initial_salary,
        r.current_salary + (r.current_salary * p.percentage_change / 100.0),
        p.promo_id
    FROM RCTE r
    JOIN promos p 
      ON r.id = p.employee_id 
     AND p.promo_id = r.promo_id + 1
),
RankedSalaries AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY id 
               ORDER BY promo_id DESC
           ) as rn
    FROM RCTE
)
SELECT 
    id,
    name,
    initial_salary,
    current_salary AS final_salary
FROM RankedSalaries
WHERE rn = 1;

8. Summary and Key Takeaways

• Compounding vs. Additive: Never add percentages together directly (e.g., 10% + 20%); salary promotions compound sequentially where each update applies to the newly updated baseline.
• Role of Window Functions: Use ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY promotion_date ASC) to establish a clean sequential order (promo_id) for each employee's career progression.
• Recursive CTE Structure: Recursive CTEs require an Anchor Member (the baseline state at promo_id = 0) combined via UNION ALL with a Recursive Member that references RCTE.promo_id + 1.
• Static vs. Dynamic Columns: Employee identifiers, names, and initial salaries remain constant across recursive steps, while current_salary updates dynamically with each iteration.
• Handling Database Dialects: While standard SQL syntax applies across most modern relational databases (PostgreSQL, SQL Server, MySQL 8.0+), always verify recursive execution limits and syntax nuances for your specific database engine.
• Filtering Final Results: Because a recursive CTE returns every historical step, use an outer ranking query (ROW_NUMBER() PARTITION BY id ORDER BY promo_id DESC) to filter and extract only the final, current salary state.