Cashless Claims Settled per Insurer per Month — SQL Problem
The TPA desk tracks cashless claims by the month they were submitted.
- Difficulty: Medium
- Topics: Dates, Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #209
Problem statement
The TPA desk tracks cashless claims by the month they were submitted. A claim is settled once the insurer has paid it (status settled); only then is settled_amount filled in.
For every insurer and submission month that has at least one claim, return insurer, claim_month (as 'YYYY-MM'), claims (all claims submitted that month), settled_claims and settled_amount (the total paid on settled claims, 0 when none settled). Order by insurer, then claim_month.
Tables
Table: Claim
| Column | Type |
|---|---|
| claim_id | int |
| insurer | varchar |
| submitted_on | date |
| status | enum(submitted, approved, rejected, settled) |
| settled_amount | int |
Primary key: claim_id.
Amounts in rupees. settled_amount is NULL unless status is settled.
Examples
Example 1
Claim
| claim_id | insurer | submitted_on | status | settled_amount |
|---|---|---|---|---|
| 1 | Star Health | 2025-01-04 | settled | 64000 |
| 2 | Star Health | 2025-01-19 | rejected | NULL |
| 3 | Star Health | 2025-01-31 | settled | 22500 |
| 4 | Star Health | 2025-02-01 | approved | NULL |
| 5 | HDFC Ergo | 2025-01-12 | settled | 118000 |
| 6 | HDFC Ergo | 2025-03-03 | submitted | NULL |
| 7 | Niva Bupa | 2025-02-14 | settled | 41000 |
| 8 | Niva Bupa | 2025-02-27 | settled | 9500 |
Output
| insurer | claim_month | claims | settled_claims | settled_amount |
|---|---|---|---|---|
| HDFC Ergo | 2025-01 | 1 | 1 | 118000 |
| HDFC Ergo | 2025-03 | 1 | 0 | 0 |
| Niva Bupa | 2025-02 | 2 | 2 | 50500 |
| Star Health | 2025-01 | 3 | 2 | 86500 |
| Star Health | 2025-02 | 1 | 0 | 0 |
How to solve Cashless Claims Settled per Insurer per Month
The grouping key is two parts: the insurer and the submission month. DATE_FORMAT(submitted_on, '%Y-%m') maps every date to its month label (the first seven characters of the date say the same thing), and grouping by both gives one row per insurer per month that had claims.
All three numbers come from the same group with conditional aggregation. COUNT(*) counts every claim. SUM(CASE WHEN status = 'settled' THEN 1 ELSE 0 END) counts only the settled ones. The paid total is SUM of the amount restricted to settled claims; because a month with no settled claim sums nothing but NULLs, the result is NULL, and COALESCE(…, 0) turns it into the 0 the statement asks for.
Since settled_amount is filled only for settled claims, COUNT(settled_amount) and SUM(settled_amount) are shortcuts that rely on that rule; the CASE form states the rule explicitly and survives data where it does not hold. One pass and one group per insurer-month.
Reference solution (MySQL)
SELECT insurer,
DATE_FORMAT(submitted_on, '%Y-%m') AS claim_month,
COUNT(*) AS claims,
SUM(CASE WHEN status = 'settled' THEN 1 ELSE 0 END) AS settled_claims,
COALESCE(SUM(CASE WHEN status = 'settled' THEN settled_amount END), 0) AS settled_amount
FROM Claim
GROUP BY insurer, DATE_FORMAT(submitted_on, '%Y-%m')
ORDER BY insurer, claim_monthAnother way
SELECT insurer, LEFT(submitted_on, 7) AS claim_month, COUNT(claim_id) AS claims, COUNT(settled_amount) AS settled_claims, IFNULL(SUM(settled_amount), 0) AS settled_amount FROM Claim GROUP BY insurer, LEFT(submitted_on, 7) ORDER BY 1, 2Another way
SELECT insurer, CONCAT(YEAR(submitted_on), '-', LPAD(MONTH(submitted_on), 2, '0')) AS claim_month, COUNT(*) AS claims, SUM(IF(status = 'settled', 1, 0)) AS settled_claims, SUM(IF(status = 'settled', settled_amount, 0)) AS settled_amount FROM Claim GROUP BY 1, 2 ORDER BY insurer, claim_month← Most Prescribed Drug in Each Department · Patients Seen Across Several Specialties →