Calculate Ultimate Loss by Policy Term

You work with insurance policy-term loss data. For a given policy term, its ultimate loss is defined as the sum of losses from the current term and all future terms of the same policy, ordered by term sequence.

Assume the following table:

CREATE TABLE policy_term_losses (
 policy_id VARCHAR, -- Unique identifier for a policy
 term_id VARCHAR, -- Unique identifier for a policy term
 term_sequence INTEGER, -- Sequence number of the term within the policy; larger means later term
 term_start_date DATE,
 term_end_date DATE,
 loss_amount DECIMAL(18, 2) -- Loss incurred during this policy term
);

Assumptions:

• Each term_id belongs to exactly one policy_id.
• term_sequence determines the chronological order of terms within a policy.
• There is at most one row per (policy_id, term_sequence).
• loss_amount can be zero, positive, or negative due to adjustments.
• No timezone conversion is required because the calculation is based on term sequence, not timestamps.

Task: Write a SQL query that returns the ultimate loss for each policy term.

Required output columns:

• policy_id
• term_id
• term_sequence
• loss_amount
• ultimate_loss, defined as the sum of loss_amount for the current row and all rows from the same policy_id with term_sequence >= current term_sequence.

Example: If policy P1 has term losses by sequence:

Then the expected ultimate losses are: