Calculate Ultimate Loss by Policy Term
Company: Plymouth Rock Assurance Corporation
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
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:
```sql
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 |
Overview: This question evaluates a candidate's ability to perform sequence-aware cumulative aggregation and partitioned data manipulation, assessing competency in calculating running totals (ultimate loss) across policy-term records.
Write a SQL query that returns, for each policy term, the ultimate_loss defined as the sum of loss_amount for the current term and all subsequent terms (greater or equal term_sequence) of the same policy.
Tables
policy_term_losses(policy_id VARCHAR, term_id VARCHAR, term_sequence INTEGER, term_start_date DATE, term_end_date DATE, loss_amount DECIMAL(18, 2))
Hints
- Use a window function: SUM(loss_amount) OVER (PARTITION BY policy_id ORDER BY term_sequence ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING).
- Alternatively, use a correlated subquery summing loss_amount where policy_id matches and term_sequence >= current term_sequence.