Tracking Premium Changes Over Time

Premiums don’t always stay fixed after a policy is bound — especially in specialty insurance. As endorsements (change transactions) are issued to update coverage, event details, or limits, the premium typically changes too.

Understanding how and when these changes occur is key to analyzing underwriting performance, forecasting revenue, and making sense of customer behavior.

Let's review how to use the SUM() OVER window function to track premium changes over time — whether for a single policy or a list of policies. This helped me uncover patterns, catch anomalies, and answer key questions like:

• How much premium had we earned before a policy was cancelled?

• What was the financial impact of each endorsement?

Real-World Scenario

Let’s say you work with a table called policies, where each row represents a transaction — ADD, CHG, or CNL. Each record has a policy_id, policy_number, transaction_type, creation_date, and premium (total premium at that stage).

Here’s a simplified example:

policy_id	policy_number	insured_name	transaction_type	creation_date	premium
72183a12-6758	ABC100019	Ramirez Inc	ADD	2023-12-24	358
72183a12-6758	ABC100019	Ramirez Inc	CHG	2024-01-23	481
72183a12-6758	ABC100019	Ramirez Inc	CHG	2024-02-22	583

We want to analyze how the premium changed over time, but also track cumulative changes.

The Problem with Simple Aggregation

You could group by policy and just use MAX(premium) function or subtract the first from the last, but that wouldn’t show you how it evolved.

What I wanted was a running log:

• What the premium was at each point

• How much had accumulated up to that transaction

  
  

Enter SUM() OVER with Partitioning

Here’s the basic pattern:

SELECT
  policy_number,
  transaction_type,
  creation_date,
  premium,
  SUM(premium) OVER (
    PARTITION BY policy_number
    ORDER BY creation_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cumulative_premium
FROM policies;

The SUM() OVER clause calculates a running total. The PARTITION BY groups results per policy. The ORDER BY ensures we're calculating in chronological order. But the most important part — and one that often confuses people — is this:

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

This line explicitly defines the window frame. It means: "start at the first row of the partition and continue up to the current row." Without this, SQL may use a default window that doesn't behave like a true running total.

But there’s a catch.

The premium column in our model already reflects the total premium at that version, not the delta. To properly track changes, we first need to calculate the difference from the previous version.

Step 1: Calculate Premium Deltas

WITH ordered_policies AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY policy_number
      ORDER BY creation_date
    ) AS rn,
    LAG(premium) OVER (
      PARTITION BY policy_number
      ORDER BY creation_date
    ) AS prev_premium
  FROM policies
)
SELECT *,
  CASE
    WHEN transaction_type = 'ADD' THEN premium
    WHEN transaction_type = 'CHG' THEN premium - COALESCE(prev_premium, 0)
    WHEN transaction_type = 'CNL' THEN -COALESCE(prev_premium, 0)
    ELSE 0
  END AS premium_delta
FROM ordered_policies;

This now gives you the actual change at each step. Here, LAG() is critical. It lets us pull the value of the previous row — based on ORDER BY creation_date — so we can compare the current and prior premium values.

Step 2: Aggregate Over Time

Now wrap that result in another CTE and apply SUM() to track cumulative changes:

WITH ordered_policies AS (...),
     deltas AS (
  SELECT *,
    CASE
      WHEN transaction_type = 'ADD' THEN premium
      WHEN transaction_type = 'CHG' THEN premium - COALESCE(prev_premium, 0)
      WHEN transaction_type = 'CNL' THEN -COALESCE(prev_premium, 0)
      ELSE 0
    END AS premium_delta
  FROM ordered_policies
)
SELECT *,
  SUM(premium_delta) OVER (
    PARTITION BY policy_number
    ORDER BY creation_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cumulative_premium
FROM deltas;

This is exactly what I needed — a row-by-row breakdown of how a policy’s premium changed, and a running total. It helped illuminate how a policy evolved over time instead of only showing a before-and-after snapshot.

creation_date	transaction_type	premium	prev_premium	premium_delta	cumulative_premium
2023-12-24	ADD	358	(null)	358	358
2024-01-23	CHG	481	358	123	481
2024-02-22	CHG	583	481	102	583

Practical Use Cases

I used this logic in a few key places:

• Trend Reports: Showing how many policies increased or decreased in value after endorsements

• Cancellation Analysis: Highlighting when policies were fully refunded or partially canceled

• Audit Logs: Giving underwriters a step-by-step view of what changed and when

• Revenue Forecasting: Modeling future income by simulating premium deltas

It also became the basis for several dashboards — because stakeholders didn’t just want the final number. They wanted to see the path.

Takeaways

This technique was a turning point for me. It helped me think in terms of event sequences instead of just snapshots.

It also reinforced my key takeaways:

• Use LAG() to compare each row with the prior transaction

• Use CASE to assign delta logic for ADD, CHG, and CNL

• Use SUM() OVER (...) with ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW to build cumulative change over time