PhonePe: Motor Policy Renewal Rate by Expiry Month
Motor insurance bought in the app runs for a fixed term. The insurance team measures retention by expiry month: of the policies that ended in a month, how…
- Difficulty: Medium
- Topics: Subqueries, Dates, Aggregation
- Dialect: MySQL
- Problem: #886
Problem statement
Motor insurance bought in the app runs for a fixed term. The insurance team measures retention by expiry month: of the policies that ended in a month, how many vehicles were covered again in time?
A policy counts as renewed when another policy for the same vehicle_no has a start_date from the old policy's end_date up to 30 days after it, both ends included (who bought it does not matter — a car may change hands). For each month of 2025 in which at least one policy ended, return expiry_month ('YYYY-MM'), expiring, renewed and renewal_pct = 100 × renewed ÷ expiring rounded to 2 decimals. Order the rows by expiry_month.
A practice scenario set at PhonePe: the tables and data are invented, and this is not a question from, or endorsed by, PhonePe.
Tables
Table: MotorPolicy
| Column | Type |
|---|---|
| policy_id | int |
| user_id | int |
| vehicle_no | varchar |
| start_date | date |
| end_date | date |
Primary key: policy_id.
One row per motor policy; cover runs from start_date to end_date.
Examples
Example 1
MotorPolicy
| policy_id | user_id | vehicle_no | start_date | end_date |
|---|---|---|---|---|
| 1 | 41 | KA01AB1234 | 2024-01-10 | 2025-01-09 |
| 2 | 41 | KA01AB1234 | 2025-01-10 | 2026-01-09 |
| 3 | 42 | MH12CD5678 | 2024-01-20 | 2025-01-19 |
| 4 | 43 | MH12CD5678 | 2025-02-18 | 2026-02-17 |
| 5 | 44 | TS09EF4321 | 2024-01-25 | 2025-01-24 |
| 6 | 44 | TS09EF4321 | 2025-02-24 | 2026-02-23 |
| 7 | 45 | DL3CGH0001 | 2024-03-05 | 2025-03-04 |
Output
| expiry_month | expiring | renewed | renewal_pct |
|---|---|---|---|
| 2025-01 | 3 | 2 | 66.67 |
| 2025-03 | 1 | 0 | 0 |
How to solve PhonePe: Motor Policy Renewal Rate by Expiry Month
Break it into a per-policy flag and a monthly roll-up. For each policy that ended in 2025, a correlated EXISTS looks for a different policy on the same vehicle whose start date falls in the window [end_date, end_date + 30 days] — DATE_ADD(p.end_date, INTERVAL 30 DAY) builds the far end and BETWEEN includes both. The vehicle, not the user, is the key: policy 4 in the example was bought by a new owner and still renews policy 3. Policy 6 started 31 days after policy 5 ended, one day too late.
The roll-up groups the flags by DATE_FORMAT(end_date, '%Y-%m'): COUNT(*) is how many expired, SUM(renewed) how many were renewed, and their ratio times 100, rounded to two places, is the rate. Computing the flag first matters — joining straight to follow-on policies could match two of them and count one renewal twice.
The alternative does exactly that join but defends against duplicates with COUNT(DISTINCT …) on the expiring policy. Either way the cost is dominated by the self-lookup per expiring policy, cheap with an index on (vehicle_no, start_date).
Reference solution (MySQL)
WITH flagged AS (
SELECT DATE_FORMAT(p.end_date, '%Y-%m') AS expiry_month,
CASE WHEN EXISTS (
SELECT 1 FROM MotorPolicy n
WHERE n.vehicle_no = p.vehicle_no AND n.policy_id <> p.policy_id
AND n.start_date BETWEEN p.end_date AND DATE_ADD(p.end_date, INTERVAL 30 DAY)
) THEN 1 ELSE 0 END AS renewed
FROM MotorPolicy p
WHERE p.end_date BETWEEN '2025-01-01' AND '2025-12-31'
)
SELECT expiry_month, COUNT(*) AS expiring, SUM(renewed) AS renewed,
ROUND(100 * SUM(renewed) / COUNT(*), 2) AS renewal_pct
FROM flagged
GROUP BY expiry_month
ORDER BY expiry_monthAnother way
SELECT DATE_FORMAT(p.end_date, '%Y-%m') AS expiry_month, COUNT(DISTINCT p.policy_id) AS expiring,
COUNT(DISTINCT CASE WHEN n.policy_id IS NOT NULL THEN p.policy_id END) AS renewed,
ROUND(100 * COUNT(DISTINCT CASE WHEN n.policy_id IS NOT NULL THEN p.policy_id END) / COUNT(DISTINCT p.policy_id), 2) AS renewal_pct
FROM MotorPolicy p
LEFT JOIN MotorPolicy n ON n.vehicle_no = p.vehicle_no AND n.policy_id <> p.policy_id
AND n.start_date >= p.end_date AND DATEDIFF(n.start_date, p.end_date) <= 30
WHERE YEAR(p.end_date) = 2025
GROUP BY expiry_month ORDER BY expiry_month← PhonePe: Open Share.Market Positions From the Order Book · PhonePe: Collect-Request Senders Declined More Often Than Not →