Insurance Claim Funnel by Insurer

Hard
220.

Insurance Claim Funnel by Insurer

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

ColumnType
claim_idint
insurervarchar
claimed_amountint

Primary key: claim_id.

One row per claim. Every claim has a submitted event.

Table: ClaimEvent

ColumnTypeValues
claim_idint
stageenumsubmitted, query_raised, approved, rejected, paid
event_atdatetime

Primary key (together): claim_id, stage.

When the claim reached the stage.

Example

The rows must come back in the order shown.

Claim

claim_idinsurerclaimed_amount
1Star Health64000
2Star Health18000
3Star Health92000
4Niva Bupa45000
5Niva Bupa130000

ClaimEvent

claim_idstageevent_at
1submitted2025-02-01 10:00:00
1approved2025-02-03 12:00:00
1paid2025-02-08 10:00:00
2submitted2025-02-02 09:00:00
2query_raised2025-02-04 09:00:00
2approved2025-02-10 15:00:00
2paid2025-02-12 11:00:00
3submitted2025-02-05 16:00:00
3rejected2025-02-07 16:00:00
4submitted2025-02-06 08:30:00
3 more rows

Output

insurersubmittedapprovedpaidpaid_in_7_daysfast_pay_pct
Niva Bupa21100
Star Health322133.33
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.

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

ColumnType
claim_idint
insurervarchar
claimed_amountint

Primary key: claim_id.

One row per claim. Every claim has a submitted event.

Table: ClaimEvent

ColumnType
claim_idint
stageenum(submitted, query_raised, approved, rejected, paid)
event_atdatetime

Primary key: claim_id, stage.

When the claim reached the stage.

Examples

Example 1

Claim

claim_idinsurerclaimed_amount
1Star Health64000
2Star Health18000
3Star Health92000
4Niva Bupa45000
5Niva Bupa130000

ClaimEvent

claim_idstageevent_at
1submitted2025-02-01 10:00:00
1approved2025-02-03 12:00:00
1paid2025-02-08 10:00:00
2submitted2025-02-02 09:00:00
2query_raised2025-02-04 09:00:00
2approved2025-02-10 15:00:00
2paid2025-02-12 11:00:00
3submitted2025-02-05 16:00:00
3rejected2025-02-07 16:00:00
4submitted2025-02-06 08:30:00
4approved2025-02-07 08:30:00
4paid2025-02-13 08:30:01
5submitted2025-02-10 13:00:00

Output

insurersubmittedapprovedpaidpaid_in_7_daysfast_pay_pct
Niva Bupa21100
Star Health322133.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 insurer

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