PhonePe: In-App Insurance Premium by Product Line in 2025
Users can buy motor, health, term life and travel cover inside the app.
- Difficulty: Easy
- Topics: Aggregation, Dates
- Dialect: MySQL
- Problem: #876
Problem statement
Users can buy motor, health, term life and travel cover inside the app. The insurance business team wants the 2025 sales by product line, leaving out policies cancelled in the free-look period, since their premium was refunded.
For policies purchased in calendar year 2025 whose status is not cancelled_free_look, return product_line, policies (how many) and total_premium (their premium summed). Product lines with no such policy do not appear. Return the rows in any order.
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: InsurancePolicy
| Column | Type |
|---|---|
| policy_id | int |
| user_id | int |
| product_line | enum(motor, health, term_life, travel) |
| premium | int |
| purchased_on | date |
| status | enum(active, expired, cancelled_free_look) |
Primary key: policy_id.
One row per policy sold in the app; premium is the amount paid in rupees.
Examples
Example 1
InsurancePolicy
| policy_id | user_id | product_line | premium | purchased_on | status |
|---|---|---|---|---|---|
| 1 | 21 | motor | 6400 | 2025-01-01 | active |
| 2 | 22 | health | 11800 | 2025-03-14 | active |
| 3 | 23 | health | 9200 | 2025-06-02 | cancelled_free_look |
| 4 | 21 | travel | 450 | 2025-04-20 | expired |
| 5 | 24 | motor | 7100 | 2024-12-31 | active |
| 6 | 25 | motor | 5300 | 2025-12-31 | active |
| 7 | 26 | term_life | 14500 | 2026-01-01 | active |
Output
| product_line | policies | total_premium |
|---|---|---|
| health | 1 | 11800 |
| motor | 2 | 11700 |
| travel | 1 | 450 |
How to solve PhonePe: In-App Insurance Premium by Product Line in 2025
The report is one group per product line, so the shape is WHERE → GROUP BY product_line → COUNT(*) and SUM(premium). The two exclusions are row-level facts and belong in WHERE: a policy bought outside 2025 is not part of the year, and a policy cancelled during free look had its premium refunded, so it is neither a sale nor revenue.
The year test must include both ends: BETWEEN '2025-01-01' AND '2025-12-31' does that for a date column, and YEAR(purchased_on) = 2025 says the same thing, as the alternative shows (though a function on the column stops an index range scan). A product line whose only 2025 policies were cancelled loses all its rows in WHERE, forms no group, and is left out, as the statement asks.
Writing the status test as IN ('active', 'expired') is equivalent while the enum has three values; <> keeps working if a new status is added later. The query is a single scan and a small aggregate.
Reference solution (MySQL)
SELECT product_line, COUNT(*) AS policies, SUM(premium) AS total_premium
FROM InsurancePolicy
WHERE purchased_on BETWEEN '2025-01-01' AND '2025-12-31'
AND status <> 'cancelled_free_look'
GROUP BY product_lineAnother way
SELECT product_line, COUNT(policy_id) AS policies, SUM(premium) AS total_premium FROM InsurancePolicy WHERE YEAR(purchased_on) = 2025 AND status IN ('active', 'expired') GROUP BY product_line← PhonePe: Users With Three or More Active Linked Bank Accounts · PhonePe: Masked UPI IDs on Payment Receipts →