Cashless Claims Settled per Insurer per Month

Medium
209.

Cashless Claims Settled per Insurer per Month

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

ColumnTypeValues
claim_idint
insurervarchar
submitted_ondate
statusenumsubmitted, approved, rejected, settled
settled_amountint

Primary key: claim_id.

Amounts in rupees. settled_amount is NULL unless status is settled.

Example

The rows must come back in the order shown.

Claim

claim_idinsurersubmitted_onstatussettled_amount
1Star Health2025-01-04settled64000
2Star Health2025-01-19rejectedNULL
3Star Health2025-01-31settled22500
4Star Health2025-02-01approvedNULL
5HDFC Ergo2025-01-12settled118000
6HDFC Ergo2025-03-03submittedNULL
7Niva Bupa2025-02-14settled41000
8Niva Bupa2025-02-27settled9500

Output

insurerclaim_monthclaimssettled_claimssettled_amount
HDFC Ergo2025-0111118000
HDFC Ergo2025-03100
Niva Bupa2025-022250500
Star Health2025-013286500
Star Health2025-02100
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.

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

ColumnType
claim_idint
insurervarchar
submitted_ondate
statusenum(submitted, approved, rejected, settled)
settled_amountint

Primary key: claim_id.

Amounts in rupees. settled_amount is NULL unless status is settled.

Examples

Example 1

Claim

claim_idinsurersubmitted_onstatussettled_amount
1Star Health2025-01-04settled64000
2Star Health2025-01-19rejectedNULL
3Star Health2025-01-31settled22500
4Star Health2025-02-01approvedNULL
5HDFC Ergo2025-01-12settled118000
6HDFC Ergo2025-03-03submittedNULL
7Niva Bupa2025-02-14settled41000
8Niva Bupa2025-02-27settled9500

Output

insurerclaim_monthclaimssettled_claimssettled_amount
HDFC Ergo2025-0111118000
HDFC Ergo2025-03100
Niva Bupa2025-022250500
Star Health2025-013286500
Star Health2025-02100

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_month

Another 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, 2

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