Insurance Claim Funnel by Insurer — SQL Problem
Every cashless claim moves through stages logged in ClaimEvent: submitted, possibly query_raised, then approved or rejected, and paid after approval.
- Difficulty: Hard
- Topics: Conditional Logic, Aggregation, Joins
- Dialect: MySQL
- Problem: #220
Problem statement
Every cashless claim moves through stages logged in ClaimEvent: submitted, possibly query_raised, then approved or rejected, and paid after approval. A stage is logged at most once per claim. The hospital's CFO wants each insurer's funnel.
For every insurer with at least one claim, return insurer, submitted, approved, paid (claims that reached each stage), paid_in_7_days (paid no later than 7 × 24 hours after submission) and fast_pay_pct = 100 × paid_in_7_days ÷ submitted, rounded to 2 decimals. Order by insurer.
Tables
Table: Claim
| Column | Type |
|---|---|
| claim_id | int |
| insurer | varchar |
| claimed_amount | int |
Primary key: claim_id.
One row per claim. Every claim has a submitted event.
Table: ClaimEvent
| Column | Type |
|---|---|
| claim_id | int |
| stage | enum(submitted, query_raised, approved, rejected, paid) |
| event_at | datetime |
Primary key: claim_id, stage.
When the claim reached the stage.
Examples
Example 1
Claim
| claim_id | insurer | claimed_amount |
|---|---|---|
| 1 | Star Health | 64000 |
| 2 | Star Health | 18000 |
| 3 | Star Health | 92000 |
| 4 | Niva Bupa | 45000 |
| 5 | Niva Bupa | 130000 |
ClaimEvent
| claim_id | stage | event_at |
|---|---|---|
| 1 | submitted | 2025-02-01 10:00:00 |
| 1 | approved | 2025-02-03 12:00:00 |
| 1 | paid | 2025-02-08 10:00:00 |
| 2 | submitted | 2025-02-02 09:00:00 |
| 2 | query_raised | 2025-02-04 09:00:00 |
| 2 | approved | 2025-02-10 15:00:00 |
| 2 | paid | 2025-02-12 11:00:00 |
| 3 | submitted | 2025-02-05 16:00:00 |
| 3 | rejected | 2025-02-07 16:00:00 |
| 4 | submitted | 2025-02-06 08:30:00 |
| 4 | approved | 2025-02-07 08:30:00 |
| 4 | paid | 2025-02-13 08:30:01 |
| 5 | submitted | 2025-02-10 13:00:00 |
Output
| insurer | submitted | approved | paid | paid_in_7_days | fast_pay_pct |
|---|---|---|---|---|---|
| Niva Bupa | 2 | 1 | 1 | 0 | 0 |
| Star Health | 3 | 2 | 2 | 1 | 33.33 |
How to solve Insurance Claim Funnel by Insurer
The events are long-format — one row per (claim, stage) — while the funnel wants one row per insurer with a column per stage. The usual route is two steps. First pivot per claim with conditional aggregation: grouping the events by claim, MAX(CASE WHEN stage = 'submitted' THEN event_at END) is the submission time, the same for paid, and a 0/1 MAX for approval. Stages that never happened come out NULL (or 0).
Then aggregate the claim rows per insurer. COUNT(column) counts non-NULL values, so COUNT(paid_at) is the paid claims. The 7-day test is TIMESTAMPDIFF(SECOND, submitted_at, paid_at) <= 604800 — exact to the second, so a payment exactly seven days later counts and one second more does not; for an unpaid claim the difference is NULL and the CASE yields 0. The rate divides by the submitted count and is rounded to 2 decimals.
The alternative joins ClaimEvent once per stage — an inner join for the mandatory submission, LEFT JOINs for the optional stages — which works because each stage occurs at most once per claim, so no join multiplies rows.
Reference solution (MySQL)
WITH per_claim AS (
SELECT c.claim_id, c.insurer,
MAX(CASE WHEN e.stage = 'submitted' THEN e.event_at END) AS submitted_at,
MAX(CASE WHEN e.stage = 'approved' THEN 1 ELSE 0 END) AS was_approved,
MAX(CASE WHEN e.stage = 'paid' THEN e.event_at END) AS paid_at
FROM Claim c
JOIN ClaimEvent e ON e.claim_id = c.claim_id
GROUP BY c.claim_id, c.insurer
)
SELECT insurer,
COUNT(submitted_at) AS submitted,
SUM(was_approved) AS approved,
COUNT(paid_at) AS paid,
SUM(CASE WHEN TIMESTAMPDIFF(SECOND, submitted_at, paid_at) <= 604800 THEN 1 ELSE 0 END) AS paid_in_7_days,
ROUND(100 * SUM(CASE WHEN TIMESTAMPDIFF(SECOND, submitted_at, paid_at) <= 604800 THEN 1 ELSE 0 END) / COUNT(submitted_at), 2) AS fast_pay_pct
FROM per_claim
GROUP BY insurer
ORDER BY insurerAnother way
SELECT c.insurer,
COUNT(s.claim_id) AS submitted,
COUNT(a.claim_id) AS approved,
COUNT(p.claim_id) AS paid,
SUM(IF(p.event_at <= DATE_ADD(s.event_at, INTERVAL 7 DAY), 1, 0)) AS paid_in_7_days,
ROUND(SUM(IF(p.event_at <= DATE_ADD(s.event_at, INTERVAL 7 DAY), 1, 0)) * 100 / COUNT(s.claim_id), 2) AS fast_pay_pct
FROM Claim c
JOIN ClaimEvent s ON s.claim_id = c.claim_id AND s.stage = 'submitted'
LEFT JOIN ClaimEvent a ON a.claim_id = c.claim_id AND a.stage = 'approved'
LEFT JOIN ClaimEvent p ON p.claim_id = c.claim_id AND p.stage = 'paid'
GROUP BY c.insurer
ORDER BY c.insurer← OPD New-Patient Retention by Monthly Cohort · Double-Booked Doctor Appointment Slots →