Shrinking Insurance Policies
Problem
An insurer keeps a full history of policy changes in the table `policy_history`, one row per change, with the `change_id`, the `holder_id`, the `change_date`, the `change_kind` ('open', 'upgrade', 'reduce', or 'close'), the `tier_name`, and the `monthly_premium` in force after that change. The retention team wants to flag *shrinking policies*: holders whose policy is still in force (their most recent change is not a 'close'), who have reduced coverage at least once (at least one 'reduce' change), whose current monthly premium is below half of the highest premium they ever paid, and who have been a holder for at least 60 days (from their first to their latest change). Return the `holder_id` of each flagged holder. Order by their tenure in days descending, then by `holder_id` ascending.
Tables
Example rows — the live problem includes the full dataset.
| change_id | holder_id | change_date | change_kind | tier_name | monthly_premium |
|---|---|---|---|---|---|
| 1 | 100 | 2024-01-01 | open | platinum | 80 |
| 2 | 100 | 2024-02-15 | reduce | gold | 50 |
| 3 | 100 | 2024-03-20 | reduce | silver | 30 |
Expected output
Your answer should return 2 rows with the columns holder_id.
Starter code (SQL)
SELECT *
FROM policy_history;Solve this SQL question free
Write SQL and run it instantly in your browser — even on your phone. No signup needed to try.
Solution & explanation
Create a free account to unlock the optimal solution, a step-by-step explanation, and the hidden test cases that grade your answer.
Sign up free to unlock