Duplicate Billing Query at a Physical Therapy Network
An SIU analyst at a regional health insurer receives a referral about a physical therapy network with 6 clinics. The claims warehouse has 14,200 paid professional claims for 2022 in a table CLAIMS with fields CLAIM_ID, MEMBER_ID, PROVIDER_NPI, SERVICE_DATE, CPT_CODE, MODIFIER_1, MODIFIER_2, BILLED_AMT, PAID_AMT, and DENIAL_CODE. The referral says 'same member, same date, same CPT, different NPI' is suspicious. A quick count returns 212 claim pairs matching MEMBER_ID, SERVICE_DATE and CPT_CODE with different PROVIDER_NPI; 47 pairs also have the same BILLED_AMT. The data includes claims from two legitimately overlapping clinics that share call coverage, and some bilateral procedures billed with modifier 50. The manager asks for a query and a recommendation on whether to open 212 investigations. The analyst has read-only access and five business days.
1. Write the core SQL (or precise pseudo-SQL) you would use to produce candidate duplicate pairs, and explain each filter and join choice. What would you change before running it?
Read the worked answer
I would generate unordered candidate pairs with a self-join, not count every match twice. Core query:
SELECT a.CLAIM_ID AS claim_a, b.CLAIM_ID AS claim_b,
a.MEMBER_ID, a.SERVICE_DATE, a.CPT_CODE,
a.PROVIDER_NPI AS npi_a, b.PROVIDER_NPI AS npi_b,
a.PAID_AMT AS paid_a, b.PAID_AMT AS paid_b
FROM CLAIMS a
JOIN CLAIMS b
ON a.MEMBER_ID = b.MEMBER_ID
AND a.SERVICE_DATE = b.SERVICE_DATE
AND a.CPT_CODE = b.CPT_CODE
AND a.PROVIDER_NPI <> b.PROVIDER_NPI
AND a.CLAIM_ID < b.CLAIM_ID
WHERE a.PAID_AMT > 0 AND b.PAID_AMT > 0
AND COALESCE(a.DENIAL_CODE,'') = ''
AND COALESCE(b.DENIAL_CODE,'') = ''
AND COALESCE(a.MODIFIER_1,'') NOT IN ('50','RT','LT')
AND COALESCE(b.MODIFIER_1,'') NOT IN ('50','RT','LT')
AND COALESCE(a.MODIFIER_2,'') NOT IN ('50','RT','LT')
AND COALESCE(b.MODIFIER_2,'') NOT IN ('50','RT','LT');
The member, date, and CPT join defines the duplication pattern. Different NPIs keep cross-provider pairs; claim-ID ordering removes mirror pairs. Paid and denial filters focus on money at risk. Modifier exclusions remove expected bilateral and laterality claims. Before running, I would add a same-tax-ID exclusion if provider enrollment data is available, because two NPIs can belong to one group. This query produces candidates, not proof.
2. The query returns 212 pairs. Interpret this number. What additional fields or external data would you request, and how would you triage?
Read the worked answer
212 pairs is a candidate count, not a fraud count. It could represent legitimate shared-care episodes, obvious duplicates, and a smaller unexplained subset. I would request provider tax ID, group affiliation, practice address, enrollment date, call-coverage agreements, member eligibility history, claim notes, and whether the member actually received two services. I would also compute rates per 1,000 claims by clinic and by NPI pair, not just raw counts. Triage should rank pairs by paid dollars, repeat members across dates, number of distinct members per NPI pair, same billed amount, and whether the two providers share an address or owner. I would sample 20 pairs and have a clinician review the records. Only after that would I convert pairs into provider-level referrals. The goal is to find patterns that cannot be explained by coordination of care.
3. Draft a three-sentence response to the manager who wants to open all 212 investigations.
Read the worked answer
The 212 pairs are candidate anomalies, not 212 fraud cases. I recommend we first validate the query against 20 sampled pairs and add tax-ID and modifier exclusions; based on that, we can prioritize a smaller list by paid dollars and repeat members for SIU review. Opening all 212 now would create avoidable provider-relations risk and likely overwhelm investigators with legitimate call-coverage cases.