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:

policy_id	term_id	term_sequence	loss_amount
P1	T1	1	100
P1	T2	2	50
P1	T3	3	25

Then the expected ultimate losses are:

policy_id	term_id	term_sequence	loss_amount	ultimate_loss
P1	T1	1	100	175
P1	T2	2	50	75
P1	T3	3	25	25

Calculate Ultimate Loss by Policy Term

Table policy_term_losses

column	type	constraints
policy_id	VARCHAR
term_id	VARCHAR
term_sequence	INTEGER
term_start_date	DATE
term_end_date	DATE
loss_amount	DECIMAL(18, 2)

Sample data:

loss_amount	policy_id	term_end_date	term_id	term_sequence	term_start_date
100	P1	2020-12-31	T1	1	2020-01-01
50	P1	2021-12-31	T2	2	2021-01-01
25	P1	2022-12-31	T3	3	2022-01-01

Expected output

For each row in policy_term_losses, return policy_id, term_id, term_sequence, loss_amount, and ultimate_loss (running sum from current term to last term within the same policy ordered by term_sequence).

loss_amount	policy_id	term_id	term_sequence	ultimate_loss
100	P1	T1	1	175
50	P1	T2	2	75
25	P1	T3	3	25

Hints

1. Use a window function: SUM(loss_amount) OVER (PARTITION BY policy_id ORDER BY term_sequence ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING).
2. Alternatively, use a correlated subquery summing loss_amount where policy_id matches and term_sequence >= current term_sequence.
3. Ensure ordering by policy_id and term_sequence so the output matches the sample exactly.