PhonePe: In-App Insurance Premium by Product Line in 2025

Easy
876.

PhonePe: In-App Insurance Premium by Product Line in 2025

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

ColumnTypeValues
policy_idint
user_idint
product_lineenummotor, health, term_life, travel
premiumint
purchased_ondate
statusenumactive, expired, cancelled_free_look

Primary key: policy_id.

One row per policy sold in the app; premium is the amount paid in rupees.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

InsurancePolicy

policy_iduser_idproduct_linepremiumpurchased_onstatus
121motor64002025-01-01active
222health118002025-03-14active
323health92002025-06-02cancelled_free_look
421travel4502025-04-20expired
524motor71002024-12-31active
625motor53002025-12-31active
726term_life145002026-01-01active

Output

product_linepoliciestotal_premium
health111800
motor211700
travel1450
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
policy_idint
user_idint
product_lineenum(motor, health, term_life, travel)
premiumint
purchased_ondate
statusenum(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_iduser_idproduct_linepremiumpurchased_onstatus
121motor64002025-01-01active
222health118002025-03-14active
323health92002025-06-02cancelled_free_look
421travel4502025-04-20expired
524motor71002024-12-31active
625motor53002025-12-31active
726term_life145002026-01-01active

Output

product_linepoliciestotal_premium
health111800
motor211700
travel1450

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_line

Another 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 →