Analyze Medicaid Claims Denials

Introduction

30 Second Summary

When a healthcare provider submits a claim to Medicaid and it gets denied, patients wait longer for care and providers lose revenue. Finding the root cause of denial spikes is exactly the kind of problem senior Business Analysts solve at companies like Gainwell Technologies.

In this project, you will investigate a rising claims denial rate for a fictional Rhode Island Medicaid program, disprove the obvious hypothesis with SQL analysis, and produce a complete BA work package that could drive a real system enhancement.

What You'll Build

You will walk an interviewer through your screen, run a query that reveals newly enrolled providers are denied at 4x the rate of established ones, point to the exact batch processing handoff failure in your swimlane diagram, and present the Business Requirements Document that fixes it.

By the end of this project, you'll have:

• Working SQL queries using CTEs and window functions that prove the real root cause is a batch processing timing defect, not the eligibility gaps stakeholders initially assumed.
• A cross-functional swimlane diagram with 4 actor lanes that you can walk someone through to show exactly where the claims adjudication process breaks down.
• Three formal BA deliverables: a gap analysis quantifying $2.1M in rework costs, a stakeholder analysis mapping 5 groups with competing priorities, and a Business Requirements Document with downstream system impact analysis.
• Secret Mission: Build a Requirements Traceability Matrix linking every requirement to acceptance criteria and UAT test cases.

Are there any prerequisites?

You need intermediate SQL knowledge (SELECT, JOIN, GROUP BY, subqueries) and familiarity with Business Analysis fundamentals like requirements gathering and process documentation. No healthcare domain experience is required.

Before We Start

Before we dive into any data or tools, let's lock in what you are about to build and why it matters.

This project puts you in the role of a Senior Business Analyst investigating a real business problem: rising claims denials that delay provider reimbursements and disrupt care for Medicaid beneficiaries.

Set Up Your SQL Environment and Load Claims Data

You have defined the problem: rising Medicaid claims denials in the RI program. Now you need data to investigate.

DB Browser for SQLite is a visual SQL editor that runs locally with zero server configuration. In this step, you will install it, create a database, and load a synthetic claims dataset representing Q1-Q2 2026.

In this step, get ready to:

• Install DB Browser for SQLite 3.13.1.
• Create the medicaid_claims.db database with three tables.
• Load and verify 200 synthetic claims records.

Download and install DB Browser for SQLite

DB Browser for SQLite is a free, open-source tool for creating and editing SQLite databases. It gives you a spreadsheet-like interface plus a full SQL query editor.

• Press the Windows key to open your search bar.
• Type DB Browser and see if the application appears in the results.

✔️ I see DB Browser for SQLite version 3.13 or higher

• Open the application.
• Click Help in the menu bar, then click About.
• Confirm the version shown is 3.13.1 or higher.

You are ready to proceed to the next substep.

ⓧ I see an older version

If your version is older than 3.13.0, you should upgrade to get the latest fixes and features.

• Close DB Browser for SQLite if it is open.
• Open your browser and go to https://sqlitebrowser.org/dl/.
• Under the Windows section, download the Standard installer for 64-bit Windows (the file named DB.Browser.for.SQLite-v3.13.1-win64.msi).
• Run the .msi file and follow the installer prompts to complete the upgrade.
• Launch DB Browser for SQLite from the Start Menu after installation.

ⓧ I don't have it installed

• Open your browser and go to https://sqlitebrowser.org/dl/.
• Under the Windows section, download the Standard installer for 64-bit Windows (the file named DB.Browser.for.SQLite-v3.13.1-win64.msi).
• Run the .msi file and follow the installer prompts.
• Accept the default installation options and click Install.
• Once installation finishes, launch DB Browser for SQLite from the Start Menu.

Seeing a missing DLL error?

If you see an error about a missing DLL file, you may need to install the Microsoft Visual C++ Redistributable from Microsoft's download center. Search for "Visual C++ Redistributable" and install the latest version for your system.

Help me fix a DLL error when launching DB Browser for SQLite on Windows.

What is DB Browser for SQLite?

DB Browser for SQLite is a visual tool for working with SQLite database files. Unlike full database servers like PostgreSQL or MySQL, SQLite stores your entire database in a single local file. No server process, no network configuration.

This makes it ideal for analysis tasks where you want to query data without infrastructure overhead.

Create the database and load the schema

With DB Browser for SQLite open, you will create a new database file and define three tables that model the RI Medicaid claims processing system.

• Click File in the menu bar, then click New Database.
• In the save dialog, navigate to your Desktop.
• Name the file medicaid_claims.db and click Save.
• A "Edit table definition" dialog may appear. Click Cancel to dismiss it. You will create tables using SQL instead.
• Click the Execute SQL tab at the top of the window.

• Create the members and providers tables by pasting this SQL into the query editor and clicking the Execute button (the play icon):

-- Medicaid Claims Analysis Database Schema
-- Synthetic dataset for RI Medicaid Program Q1-Q2 2026

CREATE TABLE members (
    member_id TEXT PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    date_of_birth TEXT NOT NULL,
    eligibility_start TEXT NOT NULL,
    eligibility_end TEXT,
    aid_code TEXT NOT NULL,
    county TEXT NOT NULL
);

CREATE TABLE providers (
    provider_id TEXT PRIMARY KEY,
    provider_name TEXT NOT NULL,
    npi TEXT NOT NULL,
    specialty TEXT NOT NULL,
    enrollment_date TEXT NOT NULL,
    group_id TEXT,
    group_link_date TEXT,
    enrollment_status TEXT NOT NULL DEFAULT 'active'
);

What do these tables store?

• members holds Medicaid beneficiary data: demographics, eligibility dates, and county.
• providers holds provider enrollment data including the critical group_link_date field. This is the date the provider's billing group association was activated in the system.

• Clear the query editor, then create the claims table by pasting this SQL and clicking Execute:

CREATE TABLE claims (
    claim_id TEXT PRIMARY KEY,
    member_id TEXT NOT NULL,
    provider_id TEXT NOT NULL,
    service_date TEXT NOT NULL,
    submit_date TEXT NOT NULL,
    claim_type TEXT NOT NULL,
    procedure_code TEXT NOT NULL,
    billed_amount REAL NOT NULL,
    status TEXT NOT NULL,
    adjudication_date TEXT,
    denial_code TEXT,
    denial_category TEXT,
    FOREIGN KEY (member_id) REFERENCES members(member_id),
    FOREIGN KEY (provider_id) REFERENCES providers(provider_id)
);

What does the claims table track?

Each row represents one claim submitted by a provider for a member's service. The status column holds the adjudication result: paid, denied, or suspended. When denied, denial_code stores the standardized CARC code (like CO-16 for missing information).

• Click the Database Structure tab to verify your three tables appear: claims, members, providers.

Tables not showing up?

Make sure you clicked the Execute button (play icon) after pasting each SQL block. Check the status bar at the bottom for error messages. A common cause is a typo if you edited the SQL manually.

Help me debug why my CREATE TABLE statements didn't work in DB Browser for SQLite.

Load the seed data and verify row counts

Now you will populate the tables with synthetic data. This dataset models 45 Medicaid members, 20 healthcare providers (a mix of established and newly enrolled), and 200 claims spanning January through June 2026.

• Click the Execute SQL tab.
• Clear any existing SQL in the editor.
• Load the members data by pasting this SQL and clicking Execute:

-- Members (45 records)
INSERT INTO members VALUES ('M001','Maria','Santos','1985-03-12','2024-01-01',NULL,'A1','Providence');
INSERT INTO members VALUES ('M002','James','Wilson','1972-08-25','2023-06-15',NULL,'A1','Kent');
INSERT INTO members VALUES ('M003','Aisha','Brown','1990-11-03','2025-01-01',NULL,'A3','Providence');
INSERT INTO members VALUES ('M004','Robert','Chen','1968-04-19','2024-03-01','2026-02-28','A1','Washington');
INSERT INTO members VALUES ('M005','Lisa','Johnson','1995-07-30','2025-06-01',NULL,'A1','Newport');
INSERT INTO members VALUES ('M006','David','Kim','1980-01-14','2023-09-01',NULL,'A3','Providence');
INSERT INTO members VALUES ('M007','Sarah','Martinez','1977-12-08','2024-07-01',NULL,'A1','Kent');
INSERT INTO members VALUES ('M008','Michael','Thompson','1963-05-22','2022-01-01',NULL,'A1','Bristol');
INSERT INTO members VALUES ('M009','Jennifer','Davis','1988-09-17','2025-03-01',NULL,'A3','Providence');
INSERT INTO members VALUES ('M010','William','Garcia','1975-02-28','2024-11-01',NULL,'A1','Washington');
INSERT INTO members VALUES ('M011','Patricia','Lee','1992-06-11','2025-08-01',NULL,'A1','Kent');
INSERT INTO members VALUES ('M012','Thomas','Robinson','1970-10-05','2023-04-01',NULL,'A3','Providence');
INSERT INTO members VALUES ('M013','Nancy','Clark','1983-08-20','2024-02-01',NULL,'A1','Newport');
INSERT INTO members VALUES ('M014','Daniel','Lewis','1966-03-15','2022-06-01',NULL,'A1','Bristol');
INSERT INTO members VALUES ('M015','Karen','Walker','1991-01-27','2025-05-01',NULL,'A3','Providence');
INSERT INTO members VALUES ('M016','Christopher','Hall','1978-11-09','2024-09-01',NULL,'A1','Kent');
INSERT INTO members VALUES ('M017','Betty','Allen','1960-07-04','2021-01-01',NULL,'A1','Washington');
INSERT INTO members VALUES ('M018','Steven','Young','1987-04-23','2025-02-01',NULL,'A3','Providence');
INSERT INTO members VALUES ('M019','Dorothy','King','1973-12-30','2024-05-01',NULL,'A1','Newport');
INSERT INTO members VALUES ('M020','Andrew','Wright','1981-09-14','2024-08-01',NULL,'A1','Bristol');
INSERT INTO members VALUES ('M021','Sandra','Scott','1994-02-18','2025-07-01',NULL,'A3','Kent');
INSERT INTO members VALUES ('M022','Joshua','Green','1969-06-07','2023-01-01',NULL,'A1','Providence');
INSERT INTO members VALUES ('M023','Margaret','Adams','1986-10-25','2024-12-01',NULL,'A1','Washington');

• Clear the editor, then paste and execute this second batch of members:

INSERT INTO members VALUES ('M024','Kevin','Baker','1976-05-16','2024-04-01',NULL,'A3','Newport');
INSERT INTO members VALUES ('M025','Ashley','Nelson','1993-08-09','2025-09-01',NULL,'A1','Bristol');
INSERT INTO members VALUES ('M026','Brian','Carter','1964-01-21','2022-03-01',NULL,'A1','Providence');
INSERT INTO members VALUES ('M027','Emily','Mitchell','1989-07-12','2025-04-01',NULL,'A3','Kent');
INSERT INTO members VALUES ('M028','Ronald','Perez','1971-11-28','2023-08-01',NULL,'A1','Washington');
INSERT INTO members VALUES ('M029','Amanda','Roberts','1984-03-06','2024-06-01',NULL,'A1','Newport');
INSERT INTO members VALUES ('M030','Gary','Turner','1967-09-19','2022-09-01',NULL,'A1','Bristol');
INSERT INTO members VALUES ('M031','Melissa','Phillips','1996-04-14','2025-10-01',NULL,'A3','Providence');
INSERT INTO members VALUES ('M032','Jeffrey','Campbell','1979-08-31','2024-10-01',NULL,'A1','Kent');
INSERT INTO members VALUES ('M033','Stephanie','Parker','1974-02-09','2023-11-01',NULL,'A1','Washington');
INSERT INTO members VALUES ('M034','Jacob','Evans','1982-06-26','2024-01-15',NULL,'A3','Newport');
INSERT INTO members VALUES ('M035','Rebecca','Edwards','1961-12-03','2021-06-01',NULL,'A1','Bristol');
INSERT INTO members VALUES ('M036','Ryan','Collins','1990-05-08','2025-01-15',NULL,'A1','Providence');
INSERT INTO members VALUES ('M037','Laura','Stewart','1977-10-21','2024-03-15',NULL,'A3','Kent');
INSERT INTO members VALUES ('M038','Nicholas','Morris','1985-01-17','2024-07-15',NULL,'A1','Washington');
INSERT INTO members VALUES ('M039','Heather','Rogers','1968-07-29','2023-02-01',NULL,'A1','Newport');
INSERT INTO members VALUES ('M040','Eric','Reed','1993-11-13','2025-06-15',NULL,'A3','Bristol');
INSERT INTO members VALUES ('M041','Angela','Cook','1980-04-05','2024-11-15',NULL,'A1','Providence');
INSERT INTO members VALUES ('M042','Stephen','Morgan','1972-09-22','2023-05-01',NULL,'A1','Kent');
INSERT INTO members VALUES ('M043','Nicole','Bell','1988-02-14','2025-03-15',NULL,'A3','Washington');
INSERT INTO members VALUES ('M044','Adam','Murphy','1965-08-18','2022-02-01',NULL,'A1','Newport');
INSERT INTO members VALUES ('M045','Rachel','Bailey','1991-12-27','2025-11-01',NULL,'A1','Bristol');

• Verify the members loaded by clearing the editor and running:

SELECT COUNT(*) FROM members;

You should see the result 45.

• Now load the providers data. Clear the editor, paste the following SQL, and click Execute:

-- Providers (20 records - mix of established and recently enrolled)
INSERT INTO providers VALUES ('P001','Providence Primary Care Associates','1234567890','Family Medicine','2020-03-15','GRP001','2020-03-15','active');
INSERT INTO providers VALUES ('P002','Kent County Medical Group','2345678901','Internal Medicine','2019-07-01','GRP002','2019-07-01','active');
INSERT INTO providers VALUES ('P003','RI Behavioral Health Services','3456789012','Psychiatry','2021-01-10','GRP003','2021-01-10','active');
INSERT INTO providers VALUES ('P004','Newport Community Health Center','4567890123','Family Medicine','2018-05-20','GRP004','2018-05-20','active');
INSERT INTO providers VALUES ('P005','Bristol Pediatric Associates','5678901234','Pediatrics','2022-09-01','GRP005','2022-09-01','active');
INSERT INTO providers VALUES ('P006','Washington County Orthopedics','6789012345','Orthopedics','2023-02-14','GRP006','2023-02-14','active');
INSERT INTO providers VALUES ('P007','Providence Urgent Care LLC','7890123456','Emergency Medicine','2024-11-01','GRP007','2024-11-01','active');
INSERT INTO providers VALUES ('P008','RI Cardiology Specialists','8901234567','Cardiology','2020-06-30','GRP008','2020-06-30','active');
INSERT INTO providers VALUES ('P009','East Bay Family Practice','9012345678','Family Medicine','2023-08-15','GRP009','2023-08-15','active');
INSERT INTO providers VALUES ('P010','Coastal Dermatology Associates','0123456789','Dermatology','2024-05-01','GRP010','2024-05-01','active');
-- Recently enrolled providers (these will have group_link_date AFTER enrollment_date)
INSERT INTO providers VALUES ('P011','New England Wellness Center','1112223334','Family Medicine','2026-01-15','GRP011','2026-01-22','active');
INSERT INTO providers VALUES ('P012','Providence Mental Health Associates','2223334445','Psychiatry','2026-02-01','GRP012','2026-02-08','active');
INSERT INTO providers VALUES ('P013','RI Telehealth Services','3334445556','Telemedicine','2026-02-10','GRP013','2026-02-17','active');
INSERT INTO providers VALUES ('P014','South County Medical Partners','4445556667','Internal Medicine','2026-03-01','GRP014','2026-03-10','active');
INSERT INTO providers VALUES ('P015','Blackstone Valley Health','5556667778','Family Medicine','2026-03-05','GRP015','2026-03-12','active');
INSERT INTO providers VALUES ('P016','Narragansett Bay Specialists','6667778889','Cardiology','2026-03-20','GRP016','2026-03-29','active');
INSERT INTO providers VALUES ('P017','Woonsocket Community Clinic','7778889990','Family Medicine','2026-04-01','GRP017','2026-04-08','active');
INSERT INTO providers VALUES ('P018','Cranston Family Medicine','8889990001','Family Medicine','2026-04-15','GRP018','2026-04-22','active');
INSERT INTO providers VALUES ('P019','Warwick Pediatric Group','9990001112','Pediatrics','2026-05-01','GRP019','2026-05-09','active');
INSERT INTO providers VALUES ('P020','East Providence Health Associates','0001112223','Internal Medicine','2026-05-10','GRP020','2026-05-17','active');

Why do some providers have different enrollment and group link dates?

Providers P001-P010 are established. Their enrollment_date and group_link_date match because they were enrolled years ago when the linkage ran immediately.

Providers P011-P020 enrolled in 2026. Their group_link_date is 7-9 days AFTER their enrollment_date. This gap is the clue you will later uncover as the root cause of denials.

• Verify the providers loaded by running:

SELECT COUNT(*) FROM providers;

You should see the result 20.

• Now load the claims data. This is the largest table. Clear the editor, paste the first batch, and click Execute:

-- Claims (200 records)
-- Established providers - mostly paid claims (low denial rate ~8%)
INSERT INTO claims VALUES ('C001','M001','P001','2026-01-05','2026-01-06','professional','99213',125.00,'paid','2026-01-08',NULL,NULL);
INSERT INTO claims VALUES ('C002','M002','P002','2026-01-07','2026-01-08','professional','99214',175.00,'paid','2026-01-10',NULL,NULL);
INSERT INTO claims VALUES ('C003','M003','P003','2026-01-10','2026-01-11','professional','90834',150.00,'paid','2026-01-13',NULL,NULL);
INSERT INTO claims VALUES ('C004','M004','P001','2026-01-12','2026-01-13','professional','99213',125.00,'denied','2026-01-15','CO-22','coordination_of_benefits');
INSERT INTO claims VALUES ('C005','M005','P004','2026-01-15','2026-01-16','professional','99213',125.00,'paid','2026-01-18',NULL,NULL);
INSERT INTO claims VALUES ('C006','M006','P005','2026-01-17','2026-01-18','professional','99392',200.00,'paid','2026-01-20',NULL,NULL);
INSERT INTO claims VALUES ('C007','M007','P006','2026-01-20','2026-01-21','professional','99213',125.00,'paid','2026-01-23',NULL,NULL);
INSERT INTO claims VALUES ('C008','M008','P007','2026-01-22','2026-01-23','professional','99214',175.00,'paid','2026-01-25',NULL,NULL);
INSERT INTO claims VALUES ('C009','M009','P008','2026-01-25','2026-01-26','professional','93000',85.00,'paid','2026-01-28',NULL,NULL);
INSERT INTO claims VALUES ('C010','M010','P009','2026-01-28','2026-01-29','professional','99213',125.00,'paid','2026-01-31',NULL,NULL);
INSERT INTO claims VALUES ('C011','M011','P001','2026-01-30','2026-01-31','professional','99214',175.00,'paid','2026-02-02',NULL,NULL);
INSERT INTO claims VALUES ('C012','M012','P002','2026-02-01','2026-02-02','professional','99213',125.00,'paid','2026-02-04',NULL,NULL);
INSERT INTO claims VALUES ('C013','M013','P003','2026-02-03','2026-02-04','professional','90837',200.00,'paid','2026-02-06',NULL,NULL);
INSERT INTO claims VALUES ('C014','M014','P004','2026-02-05','2026-02-06','professional','99214',175.00,'paid','2026-02-08',NULL,NULL);
INSERT INTO claims VALUES ('C015','M015','P005','2026-02-07','2026-02-08','professional','99393',225.00,'paid','2026-02-10',NULL,NULL);
INSERT INTO claims VALUES ('C016','M016','P006','2026-02-10','2026-02-11','professional','99213',125.00,'denied','2026-02-13','CO-29','timely_filing');
INSERT INTO claims VALUES ('C017','M017','P007','2026-02-12','2026-02-13','professional','99214',175.00,'paid','2026-02-15',NULL,NULL);
INSERT INTO claims VALUES ('C018','M018','P008','2026-02-14','2026-02-15','professional','93000',85.00,'paid','2026-02-17',NULL,NULL);
INSERT INTO claims VALUES ('C019','M019','P009','2026-02-17','2026-02-18','professional','99213',125.00,'paid','2026-02-20',NULL,NULL);
INSERT INTO claims VALUES ('C020','M020','P010','2026-02-19','2026-02-20','professional','99213',125.00,'paid','2026-02-22',NULL,NULL);
INSERT INTO claims VALUES ('C021','M021','P001','2026-02-21','2026-02-22','professional','99214',175.00,'paid','2026-02-24',NULL,NULL);
INSERT INTO claims VALUES ('C022','M022','P002','2026-02-24','2026-02-25','professional','99213',125.00,'paid','2026-02-27',NULL,NULL);
INSERT INTO claims VALUES ('C023','M023','P003','2026-02-26','2026-02-27','professional','90834',150.00,'denied','2026-03-01','CO-50','not_medically_necessary');
INSERT INTO claims VALUES ('C024','M024','P004','2026-02-28','2026-03-01','professional','99214',175.00,'paid','2026-03-03',NULL,NULL);
INSERT INTO claims VALUES ('C025','M025','P005','2026-03-01','2026-03-02','professional','99213',125.00,'paid','2026-03-04',NULL,NULL);
-- New providers start submitting in March - high denial rate
INSERT INTO claims VALUES ('C026','M001','P011','2026-01-16','2026-01-17','professional','99213',125.00,'denied','2026-01-19','CO-16','missing_invalid_info');
INSERT INTO claims VALUES ('C027','M003','P011','2026-01-18','2026-01-19','professional','99214',175.00,'denied','2026-01-21','CO-16','missing_invalid_info');

• Clear the editor, paste the next batch of claims, and click Execute:

INSERT INTO claims VALUES ('C028','M006','P011','2026-01-20','2026-01-21','professional','99213',125.00,'denied','2026-01-23','CO-16','missing_invalid_info');
INSERT INTO claims VALUES ('C029','M009','P011','2026-01-23','2026-01-23','professional','99214',175.00,'paid','2026-01-25',NULL,NULL);
INSERT INTO claims VALUES ('C030','M012','P011','2026-01-25','2026-01-26','professional','99213',125.00,'paid','2026-01-28',NULL,NULL);
INSERT INTO claims VALUES ('C031','M002','P012','2026-02-02','2026-02-03','professional','90834',150.00,'denied','2026-02-05','CO-16','missing_invalid_info');
INSERT INTO claims VALUES ('C032','M005','P012','2026-02-04','2026-02-05','professional','90837',200.00,'denied','2026-02-07','CO-16','missing_invalid_info');
INSERT INTO claims VALUES ('C033','M008','P012','2026-02-06','2026-02-07','professional','90834',150.00,'denied','2026-02-09','CO-16','missing_invalid_info');
INSERT INTO claims VALUES ('C034','M011','P012','2026-02-09','2026-02-10','professional','90837',200.00,'paid','2026-02-12',NULL,NULL);
INSERT INTO claims VALUES ('C035','M014','P012','2026-02-11','2026-02-12','professional','90834',150.00,'paid','2026-02-14',NULL,NULL);
INSERT INTO claims VALUES ('C036','M004','P013','2026-02-11','2026-02-12','professional','99213',125.00,'denied','2026-02-14','CO-16','missing_invalid_info');
INSERT INTO claims VALUES ('C037','M007','P013','2026-02-13','2026-02-14','professional','99214',175.00,'denied','2026-02-16','CO-16','missing_invalid_info');
INSERT INTO claims VALUES ('C038','M010','P013','2026-02-15','2026-02-16','professional','99213',125.00,'denied','2026-02-18','CO-16','missing_invalid_info');
INSERT INTO claims VALUES ('C039','M013','P013','2026-02-18','2026-02-19','professional','99214',175.00,'paid','2026-02-21',NULL,NULL);
INSERT INTO claims VALUES ('C040','M016','P013','2026-02-20','2026-02-21','professional','99213',125.00,'paid','2026-02-23',NULL,NULL);
INSERT INTO claims VALUES ('C041','M015','P014','2026-03-02','2026-03-03','professional','99213',125.00,'denied','2026-03-05','CO-16','missing_invalid_info');
INSERT INTO claims VALUES ('C042','M018','P014','2026-03-04','2026-03-05','professional','99214',175.00,'denied','2026-03-07','CO-16','missing_invalid_info');
INSERT INTO claims VALUES ('C043','M021','P014','2026-03-06','2026-03-07','professional','99213',125.00,'denied','2026-03-09','CO-16','missing_invalid_info');
INSERT INTO claims VALUES ('C044','M024','P014','2026-03-09','2026-03-10','professional','99214',175.00,'denied','2026-03-12','CO-16','missing_invalid_info');
INSERT INTO claims VALUES ('C045','M027','P014','2026-03-11','2026-03-12','professional','99213',125.00,'paid','2026-03-14',NULL,NULL);
INSERT INTO claims VALUES ('C046','M019','P015','2026-03-06','2026-03-07','professional','99214',175.00,'denied','2026-03-09','CO-16','missing_invalid_info');
INSERT INTO claims VALUES ('C047','M022','P015','2026-03-08','2026-03-09','professional','99213',125.00,'denied','2026-03-11','CO-16','missing_invalid_info');
INSERT INTO claims VALUES ('C048','M025','P015','2026-03-10','2026-03-11','professional','99214',175.00,'denied','2026-03-13','CO-16','missing_invalid_info');
INSERT INTO claims VALUES ('C049','M028','P015','2026-03-13','2026-03-14','professional','99213',125.00,'paid','2026-03-16',NULL,NULL);
INSERT INTO claims VALUES ('C050','M031','P015','2026-03-15','2026-03-16','professional','99214',175.00,'paid','2026-03-18',NULL,NULL);
INSERT INTO claims VALUES ('C051','M020','P016','2026-03-21','2026-03-22','professional','93000',85.00,'denied','2026-03-24','CO-16','missing_invalid_info');
INSERT INTO claims VALUES ('C052','M023','P016','2026-03-23','2026-03-24','professional','93010',95.00,'denied','2026-03-26','CO-16','missing_invalid_info');
INSERT INTO claims VALUES ('C053','M026','P016','2026-03-25','2026-03-26','professional','93000',85.00,'denied','2026-03-28','CO-16','missing_invalid_info');
INSERT INTO claims VALUES ('C054','M029','P016','2026-03-28','2026-03-29','professional','93010',95.00,'denied','2026-03-31','CO-16','missing_invalid_info');
INSERT INTO claims VALUES ('C055','M032','P016','2026-03-30','2026-03-31','professional','93000',85.00,'paid','2026-04-02',NULL,NULL);
INSERT INTO claims VALUES ('C056','M030','P017','2026-04-02','2026-04-03','professional','99213',125.00,'denied','2026-04-05','CO-16','missing_invalid_info');

• Clear the editor, paste the next batch, and click Execute:

INSERT INTO claims VALUES ('C057','M033','P017','2026-04-04','2026-04-05','professional','99214',175.00,'denied','2026-04-07','CO-16','missing_invalid_info');
INSERT INTO claims VALUES ('C058','M036','P017','2026-04-06','2026-04-07','professional','99213',125.00,'denied','2026-04-09','CO-16','missing_invalid_info');
INSERT INTO claims VALUES ('C059','M039','P017','2026-04-09','2026-04-10','professional','99214',175.00,'paid','2026-04-12',NULL,NULL);
INSERT INTO claims VALUES ('C060','M042','P017','2026-04-11','2026-04-12','professional','99213',125.00,'paid','2026-04-14',NULL,NULL);
INSERT INTO claims VALUES ('C061','M034','P018','2026-04-16','2026-04-17','professional','99213',125.00,'denied','2026-04-19','CO-16','missing_invalid_info');
INSERT INTO claims VALUES ('C062','M037','P018','2026-04-18','2026-04-19','professional','99214',175.00,'denied','2026-04-21','CO-16','missing_invalid_info');
INSERT INTO claims VALUES ('C063','M040','P018','2026-04-20','2026-04-21','professional','99213',125.00,'denied','2026-04-23','CO-16','missing_invalid_info');
INSERT INTO claims VALUES ('C064','M043','P018','2026-04-23','2026-04-24','professional','99214',175.00,'paid','2026-04-26',NULL,NULL);
INSERT INTO claims VALUES ('C065','M001','P018','2026-04-25','2026-04-26','professional','99213',125.00,'paid','2026-04-28',NULL,NULL);
INSERT INTO claims VALUES ('C066','M035','P019','2026-05-02','2026-05-03','professional','99392',200.00,'denied','2026-05-05','CO-16','missing_invalid_info');
INSERT INTO claims VALUES ('C067','M038','P019','2026-05-04','2026-05-05','professional','99393',225.00,'denied','2026-05-07','CO-16','missing_invalid_info');
INSERT INTO claims VALUES ('C068','M041','P019','2026-05-06','2026-05-07','professional','99392',200.00,'denied','2026-05-09','CO-16','missing_invalid_info');
INSERT INTO claims VALUES ('C069','M044','P019','2026-05-10','2026-05-11','professional','99393',225.00,'paid','2026-05-13',NULL,NULL);
INSERT INTO claims VALUES ('C070','M002','P019','2026-05-12','2026-05-13','professional','99392',200.00,'paid','2026-05-15',NULL,NULL);
INSERT INTO claims VALUES ('C071','M036','P020','2026-05-11','2026-05-12','professional','99213',125.00,'denied','2026-05-14','CO-16','missing_invalid_info');
INSERT INTO claims VALUES ('C072','M039','P020','2026-05-13','2026-05-14','professional','99214',175.00,'denied','2026-05-16','CO-16','missing_invalid_info');
INSERT INTO claims VALUES ('C073','M042','P020','2026-05-15','2026-05-16','professional','99213',125.00,'denied','2026-05-18','CO-16','missing_invalid_info');
INSERT INTO claims VALUES ('C074','M045','P020','2026-05-18','2026-05-19','professional','99214',175.00,'paid','2026-05-21',NULL,NULL);
INSERT INTO claims VALUES ('C075','M003','P020','2026-05-20','2026-05-21','professional','99213',125.00,'paid','2026-05-23',NULL,NULL);
-- Additional established provider claims (continuing normal pattern)
INSERT INTO claims VALUES ('C076','M004','P001','2026-03-05','2026-03-06','professional','99213',125.00,'paid','2026-03-08',NULL,NULL);
INSERT INTO claims VALUES ('C077','M007','P002','2026-03-08','2026-03-09','professional','99214',175.00,'paid','2026-03-11',NULL,NULL);
INSERT INTO claims VALUES ('C078','M010','P003','2026-03-10','2026-03-11','professional','90834',150.00,'paid','2026-03-13',NULL,NULL);
INSERT INTO claims VALUES ('C079','M013','P004','2026-03-12','2026-03-13','professional','99213',125.00,'paid','2026-03-15',NULL,NULL);
INSERT INTO claims VALUES ('C080','M016','P005','2026-03-15','2026-03-16','professional','99392',200.00,'paid','2026-03-18',NULL,NULL);
INSERT INTO claims VALUES ('C081','M019','P006','2026-03-17','2026-03-18','professional','99213',125.00,'paid','2026-03-20',NULL,NULL);
INSERT INTO claims VALUES ('C082','M022','P007','2026-03-20','2026-03-21','professional','99214',175.00,'paid','2026-03-23',NULL,NULL);
INSERT INTO claims VALUES ('C083','M025','P008','2026-03-22','2026-03-23','professional','93000',85.00,'paid','2026-03-25',NULL,NULL);
INSERT INTO claims VALUES ('C084','M028','P009','2026-03-25','2026-03-26','professional','99213',125.00,'paid','2026-03-28',NULL,NULL);
INSERT INTO claims VALUES ('C085','M031','P010','2026-03-27','2026-03-28','professional','99214',175.00,'paid','2026-03-30',NULL,NULL);

• Clear the editor, paste the next batch, and click Execute:

INSERT INTO claims VALUES ('C086','M034','P001','2026-03-30','2026-03-31','professional','99213',125.00,'paid','2026-04-02',NULL,NULL);
INSERT INTO claims VALUES ('C087','M037','P002','2026-04-01','2026-04-02','professional','99214',175.00,'paid','2026-04-04',NULL,NULL);
INSERT INTO claims VALUES ('C088','M040','P003','2026-04-03','2026-04-04','professional','90837',200.00,'paid','2026-04-06',NULL,NULL);
INSERT INTO claims VALUES ('C089','M043','P004','2026-04-07','2026-04-08','professional','99213',125.00,'paid','2026-04-10',NULL,NULL);
INSERT INTO claims VALUES ('C090','M001','P005','2026-04-10','2026-04-11','professional','99393',225.00,'paid','2026-04-13',NULL,NULL);
INSERT INTO claims VALUES ('C091','M005','P006','2026-04-12','2026-04-13','professional','99213',125.00,'denied','2026-04-15','CO-18','duplicate_claim');
INSERT INTO claims VALUES ('C092','M008','P007','2026-04-15','2026-04-16','professional','99214',175.00,'paid','2026-04-18',NULL,NULL);
INSERT INTO claims VALUES ('C093','M011','P008','2026-04-17','2026-04-18','professional','93000',85.00,'paid','2026-04-20',NULL,NULL);
INSERT INTO claims VALUES ('C094','M014','P009','2026-04-20','2026-04-21','professional','99213',125.00,'paid','2026-04-23',NULL,NULL);
INSERT INTO claims VALUES ('C095','M017','P010','2026-04-22','2026-04-23','professional','99214',175.00,'paid','2026-04-25',NULL,NULL);
INSERT INTO claims VALUES ('C096','M020','P001','2026-04-25','2026-04-26','professional','99213',125.00,'paid','2026-04-28',NULL,NULL);
INSERT INTO claims VALUES ('C097','M023','P002','2026-04-28','2026-04-29','professional','99214',175.00,'paid','2026-05-01',NULL,NULL);
INSERT INTO claims VALUES ('C098','M026','P003','2026-05-01','2026-05-02','professional','90834',150.00,'paid','2026-05-04',NULL,NULL);
INSERT INTO claims VALUES ('C099','M029','P004','2026-05-03','2026-05-04','professional','99213',125.00,'paid','2026-05-06',NULL,NULL);
INSERT INTO claims VALUES ('C100','M032','P005','2026-05-06','2026-05-07','professional','99392',200.00,'denied','2026-05-09','CO-15','authorization_missing');
INSERT INTO claims VALUES ('C101','M035','P006','2026-05-08','2026-05-09','professional','99213',125.00,'paid','2026-05-11',NULL,NULL);
INSERT INTO claims VALUES ('C102','M038','P007','2026-05-10','2026-05-11','professional','99214',175.00,'paid','2026-05-13',NULL,NULL);
INSERT INTO claims VALUES ('C103','M041','P008','2026-05-13','2026-05-14','professional','93000',85.00,'paid','2026-05-16',NULL,NULL);
INSERT INTO claims VALUES ('C104','M044','P009','2026-05-15','2026-05-16','professional','99213',125.00,'paid','2026-05-18',NULL,NULL);
INSERT INTO claims VALUES ('C105','M002','P010','2026-05-18','2026-05-19','professional','99214',175.00,'paid','2026-05-21',NULL,NULL);
INSERT INTO claims VALUES ('C106','M006','P001','2026-05-20','2026-05-21','professional','99213',125.00,'paid','2026-05-23',NULL,NULL);
INSERT INTO claims VALUES ('C107','M009','P002','2026-05-22','2026-05-23','professional','99214',175.00,'paid','2026-05-25',NULL,NULL);
INSERT INTO claims VALUES ('C108','M012','P003','2026-05-25','2026-05-26','professional','90834',150.00,'denied','2026-05-28','CO-50','not_medically_necessary');
INSERT INTO claims VALUES ('C109','M015','P004','2026-05-27','2026-05-28','professional','99213',125.00,'paid','2026-05-30',NULL,NULL);
INSERT INTO claims VALUES ('C110','M018','P005','2026-05-30','2026-05-31','professional','99393',225.00,'paid','2026-06-02',NULL,NULL);
INSERT INTO claims VALUES ('C111','M021','P006','2026-06-01','2026-06-02','professional','99213',125.00,'paid','2026-06-04',NULL,NULL);
INSERT INTO claims VALUES ('C112','M024','P007','2026-06-03','2026-06-04','professional','99214',175.00,'paid','2026-06-06',NULL,NULL);
INSERT INTO claims VALUES ('C113','M027','P008','2026-06-05','2026-06-06','professional','93000',85.00,'paid','2026-06-08',NULL,NULL);
INSERT INTO claims VALUES ('C114','M030','P009','2026-06-08','2026-06-09','professional','99213',125.00,'paid','2026-06-11',NULL,NULL);

• Clear the editor, paste the next batch, and click Execute:

INSERT INTO claims VALUES ('C115','M033','P010','2026-06-10','2026-06-11','professional','99214',175.00,'paid','2026-06-13',NULL,NULL);
INSERT INTO claims VALUES ('C116','M036','P001','2026-06-12','2026-06-13','professional','99213',125.00,'paid','2026-06-15',NULL,NULL);
INSERT INTO claims VALUES ('C117','M039','P002','2026-06-15','2026-06-16','professional','99214',175.00,'denied','2026-06-18','CO-22','coordination_of_benefits');
INSERT INTO claims VALUES ('C118','M042','P003','2026-06-17','2026-06-18','professional','90837',200.00,'paid','2026-06-20',NULL,NULL);
INSERT INTO claims VALUES ('C119','M045','P004','2026-06-20','2026-06-21','professional','99213',125.00,'paid','2026-06-23',NULL,NULL);
INSERT INTO claims VALUES ('C120','M001','P006','2026-06-22','2026-06-23','professional','99214',175.00,'paid','2026-06-25',NULL,NULL);
-- More new provider claims to reinforce the pattern
INSERT INTO claims VALUES ('C121','M005','P011','2026-01-24','2026-01-25','professional','99213',125.00,'paid','2026-01-27',NULL,NULL);
INSERT INTO claims VALUES ('C122','M008','P011','2026-01-27','2026-01-28','professional','99214',175.00,'paid','2026-01-30',NULL,NULL);
INSERT INTO claims VALUES ('C123','M015','P012','2026-02-12','2026-02-13','professional','90834',150.00,'paid','2026-02-15',NULL,NULL);
INSERT INTO claims VALUES ('C124','M018','P012','2026-02-15','2026-02-16','professional','90837',200.00,'paid','2026-02-18',NULL,NULL);
INSERT INTO claims VALUES ('C125','M019','P013','2026-02-22','2026-02-23','professional','99213',125.00,'paid','2026-02-25',NULL,NULL);
INSERT INTO claims VALUES ('C126','M022','P013','2026-02-25','2026-02-26','professional','99214',175.00,'paid','2026-02-28',NULL,NULL);
INSERT INTO claims VALUES ('C127','M030','P014','2026-03-14','2026-03-15','professional','99213',125.00,'paid','2026-03-17',NULL,NULL);
INSERT INTO claims VALUES ('C128','M033','P014','2026-03-17','2026-03-18','professional','99214',175.00,'paid','2026-03-20',NULL,NULL);
INSERT INTO claims VALUES ('C129','M031','P015','2026-03-18','2026-03-19','professional','99213',125.00,'paid','2026-03-21',NULL,NULL);
INSERT INTO claims VALUES ('C130','M034','P015','2026-03-20','2026-03-21','professional','99214',175.00,'paid','2026-03-23',NULL,NULL);
INSERT INTO claims VALUES ('C131','M035','P016','2026-04-01','2026-04-02','professional','93000',85.00,'paid','2026-04-04',NULL,NULL);
INSERT INTO claims VALUES ('C132','M038','P016','2026-04-03','2026-04-04','professional','93010',95.00,'paid','2026-04-06',NULL,NULL);
INSERT INTO claims VALUES ('C133','M039','P017','2026-04-14','2026-04-15','professional','99213',125.00,'paid','2026-04-17',NULL,NULL);
INSERT INTO claims VALUES ('C134','M042','P017','2026-04-17','2026-04-18','professional','99214',175.00,'paid','2026-04-20',NULL,NULL);
INSERT INTO claims VALUES ('C135','M043','P018','2026-04-28','2026-04-29','professional','99213',125.00,'paid','2026-05-01',NULL,NULL);
INSERT INTO claims VALUES ('C136','M001','P018','2026-04-30','2026-05-01','professional','99214',175.00,'paid','2026-05-03',NULL,NULL);
INSERT INTO claims VALUES ('C137','M002','P019','2026-05-14','2026-05-15','professional','99392',200.00,'paid','2026-05-17',NULL,NULL);
INSERT INTO claims VALUES ('C138','M005','P019','2026-05-16','2026-05-17','professional','99393',225.00,'paid','2026-05-19',NULL,NULL);
INSERT INTO claims VALUES ('C139','M008','P020','2026-05-22','2026-05-23','professional','99213',125.00,'paid','2026-05-25',NULL,NULL);
INSERT INTO claims VALUES ('C140','M011','P020','2026-05-25','2026-05-26','professional','99214',175.00,'paid','2026-05-28',NULL,NULL);
-- Additional established provider claims for volume
INSERT INTO claims VALUES ('C141','M002','P001','2026-03-03','2026-03-04','professional','99213',125.00,'paid','2026-03-06',NULL,NULL);
INSERT INTO claims VALUES ('C142','M005','P002','2026-03-06','2026-03-07','professional','99214',175.00,'paid','2026-03-09',NULL,NULL);
INSERT INTO claims VALUES ('C143','M008','P004','2026-03-14','2026-03-15','professional','99213',125.00,'paid','2026-03-17',NULL,NULL);

• Clear the editor, paste the next batch, and click Execute:

INSERT INTO claims VALUES ('C144','M011','P005','2026-03-19','2026-03-20','professional','99392',200.00,'paid','2026-03-22',NULL,NULL);
INSERT INTO claims VALUES ('C145','M014','P006','2026-03-22','2026-03-23','professional','99213',125.00,'paid','2026-03-25',NULL,NULL);
INSERT INTO claims VALUES ('C146','M017','P007','2026-03-25','2026-03-26','professional','99214',175.00,'paid','2026-03-28',NULL,NULL);
INSERT INTO claims VALUES ('C147','M020','P008','2026-03-28','2026-03-29','professional','93000',85.00,'paid','2026-03-31',NULL,NULL);
INSERT INTO claims VALUES ('C148','M023','P009','2026-03-30','2026-03-31','professional','99213',125.00,'paid','2026-04-02',NULL,NULL);
INSERT INTO claims VALUES ('C149','M026','P010','2026-04-02','2026-04-03','professional','99214',175.00,'paid','2026-04-05',NULL,NULL);
INSERT INTO claims VALUES ('C150','M029','P001','2026-04-05','2026-04-06','professional','99213',125.00,'paid','2026-04-08',NULL,NULL);
INSERT INTO claims VALUES ('C151','M032','P002','2026-04-08','2026-04-09','professional','99214',175.00,'paid','2026-04-11',NULL,NULL);
INSERT INTO claims VALUES ('C152','M035','P003','2026-04-10','2026-04-11','professional','90834',150.00,'paid','2026-04-13',NULL,NULL);
INSERT INTO claims VALUES ('C153','M038','P004','2026-04-14','2026-04-15','professional','99213',125.00,'paid','2026-04-17',NULL,NULL);
INSERT INTO claims VALUES ('C154','M041','P005','2026-04-17','2026-04-18','professional','99393',225.00,'paid','2026-04-20',NULL,NULL);
INSERT INTO claims VALUES ('C155','M044','P006','2026-04-19','2026-04-20','professional','99213',125.00,'paid','2026-04-22',NULL,NULL);
INSERT INTO claims VALUES ('C156','M002','P007','2026-04-22','2026-04-23','professional','99214',175.00,'paid','2026-04-25',NULL,NULL);
INSERT INTO claims VALUES ('C157','M006','P008','2026-04-25','2026-04-26','professional','93000',85.00,'paid','2026-04-28',NULL,NULL);
INSERT INTO claims VALUES ('C158','M009','P009','2026-04-28','2026-04-29','professional','99213',125.00,'paid','2026-05-01',NULL,NULL);
INSERT INTO claims VALUES ('C159','M012','P010','2026-05-01','2026-05-02','professional','99214',175.00,'paid','2026-05-04',NULL,NULL);
INSERT INTO claims VALUES ('C160','M015','P001','2026-05-04','2026-05-05','professional','99213',125.00,'paid','2026-05-07',NULL,NULL);
INSERT INTO claims VALUES ('C161','M018','P002','2026-05-07','2026-05-08','professional','99214',175.00,'paid','2026-05-10',NULL,NULL);
INSERT INTO claims VALUES ('C162','M021','P003','2026-05-09','2026-05-10','professional','90837',200.00,'paid','2026-05-12',NULL,NULL);
INSERT INTO claims VALUES ('C163','M024','P004','2026-05-12','2026-05-13','professional','99213',125.00,'paid','2026-05-15',NULL,NULL);
INSERT INTO claims VALUES ('C164','M027','P005','2026-05-15','2026-05-16','professional','99392',200.00,'paid','2026-05-18',NULL,NULL);
INSERT INTO claims VALUES ('C165','M030','P006','2026-05-18','2026-05-19','professional','99213',125.00,'paid','2026-05-21',NULL,NULL);
INSERT INTO claims VALUES ('C166','M033','P007','2026-05-20','2026-05-21','professional','99214',175.00,'paid','2026-05-23',NULL,NULL);
INSERT INTO claims VALUES ('C167','M036','P008','2026-05-22','2026-05-23','professional','93000',85.00,'paid','2026-05-25',NULL,NULL);
INSERT INTO claims VALUES ('C168','M039','P009','2026-05-25','2026-05-26','professional','99213',125.00,'paid','2026-05-28',NULL,NULL);
INSERT INTO claims VALUES ('C169','M042','P010','2026-05-28','2026-05-29','professional','99214',175.00,'paid','2026-05-31',NULL,NULL);
INSERT INTO claims VALUES ('C170','M045','P001','2026-05-30','2026-05-31','professional','99213',125.00,'paid','2026-06-02',NULL,NULL);
INSERT INTO claims VALUES ('C171','M003','P002','2026-06-02','2026-06-03','professional','99214',175.00,'paid','2026-06-05',NULL,NULL);
INSERT INTO claims VALUES ('C172','M007','P003','2026-06-04','2026-06-05','professional','90834',150.00,'paid','2026-06-07',NULL,NULL);

• Clear the editor, paste the final batch of claims, and click Execute:

INSERT INTO claims VALUES ('C173','M010','P004','2026-06-07','2026-06-08','professional','99213',125.00,'paid','2026-06-10',NULL,NULL);
INSERT INTO claims VALUES ('C174','M013','P005','2026-06-09','2026-06-10','professional','99393',225.00,'paid','2026-06-12',NULL,NULL);
INSERT INTO claims VALUES ('C175','M016','P006','2026-06-12','2026-06-13','professional','99213',125.00,'paid','2026-06-15',NULL,NULL);
INSERT INTO claims VALUES ('C176','M019','P007','2026-06-14','2026-06-15','professional','99214',175.00,'paid','2026-06-17',NULL,NULL);
INSERT INTO claims VALUES ('C177','M022','P008','2026-06-17','2026-06-18','professional','93000',85.00,'paid','2026-06-20',NULL,NULL);
INSERT INTO claims VALUES ('C178','M025','P009','2026-06-19','2026-06-20','professional','99213',125.00,'paid','2026-06-22',NULL,NULL);
INSERT INTO claims VALUES ('C179','M028','P010','2026-06-22','2026-06-23','professional','99214',175.00,'paid','2026-06-25',NULL,NULL);
INSERT INTO claims VALUES ('C180','M031','P001','2026-06-24','2026-06-25','professional','99213',125.00,'paid','2026-06-27',NULL,NULL);
-- A few more denials from established providers (normal background rate)
INSERT INTO claims VALUES ('C181','M034','P002','2026-04-14','2026-04-15','professional','99214',175.00,'denied','2026-04-17','CO-22','coordination_of_benefits');
INSERT INTO claims VALUES ('C182','M037','P004','2026-04-21','2026-04-22','professional','99213',125.00,'denied','2026-04-24','CO-15','authorization_missing');
INSERT INTO claims VALUES ('C183','M040','P006','2026-05-05','2026-05-06','professional','99213',125.00,'denied','2026-05-08','CO-197','precert_absent');
INSERT INTO claims VALUES ('C184','M043','P008','2026-05-19','2026-05-20','professional','93000',85.00,'denied','2026-05-22','CO-18','duplicate_claim');
INSERT INTO claims VALUES ('C185','M001','P010','2026-06-02','2026-06-03','professional','99213',125.00,'denied','2026-06-05','CO-29','timely_filing');
INSERT INTO claims VALUES ('C186','M004','P003','2026-06-09','2026-06-10','professional','90834',150.00,'denied','2026-06-12','CO-50','not_medically_necessary');
INSERT INTO claims VALUES ('C187','M007','P005','2026-06-16','2026-06-17','professional','99392',200.00,'denied','2026-06-19','CO-22','coordination_of_benefits');
INSERT INTO claims VALUES ('C188','M010','P007','2026-06-23','2026-06-24','professional','99214',175.00,'denied','2026-06-26','CO-18','duplicate_claim');
-- Fill remaining to reach 200
INSERT INTO claims VALUES ('C189','M013','P001','2026-06-25','2026-06-26','professional','99213',125.00,'paid','2026-06-28',NULL,NULL);
INSERT INTO claims VALUES ('C190','M016','P002','2026-06-27','2026-06-28','professional','99214',175.00,'paid','2026-06-30',NULL,NULL);
INSERT INTO claims VALUES ('C191','M019','P004','2026-06-28','2026-06-29','professional','99213',125.00,'paid','2026-07-01',NULL,NULL);
INSERT INTO claims VALUES ('C192','M022','P005','2026-06-29','2026-06-30','professional','99393',225.00,'paid','2026-07-02',NULL,NULL);
INSERT INTO claims VALUES ('C193','M025','P006','2026-06-30','2026-07-01','professional','99213',125.00,'paid','2026-07-03',NULL,NULL);
INSERT INTO claims VALUES ('C194','M028','P003','2026-06-05','2026-06-06','professional','90834',150.00,'paid','2026-06-08',NULL,NULL);
INSERT INTO claims VALUES ('C195','M031','P009','2026-06-10','2026-06-11','professional','99213',125.00,'paid','2026-06-13',NULL,NULL);
INSERT INTO claims VALUES ('C196','M034','P007','2026-06-13','2026-06-14','professional','99214',175.00,'paid','2026-06-16',NULL,NULL);
INSERT INTO claims VALUES ('C197','M037','P008','2026-06-18','2026-06-19','professional','93010',95.00,'paid','2026-06-21',NULL,NULL);
INSERT INTO claims VALUES ('C198','M040','P009','2026-06-21','2026-06-22','professional','99213',125.00,'paid','2026-06-24',NULL,NULL);
INSERT INTO claims VALUES ('C199','M043','P010','2026-06-24','2026-06-25','professional','99214',175.00,'paid','2026-06-27',NULL,NULL);
INSERT INTO claims VALUES ('C200','M045','P002','2026-06-26','2026-06-27','professional','99213',125.00,'paid','2026-06-29',NULL,NULL);

Before you run the final verification, what do you expect the total claims count to be?

• Verify the full claims load by clearing the editor and running:

SELECT COUNT(*) FROM claims;

You should see 200. This confirms all claims records loaded successfully.

• Click the Browse Data tab at the top of DB Browser for SQLite.
• Use the Table dropdown to switch between claims, members, and providers.
• Scroll through the claims table and notice the status column shows a mix of paid and denied values.

What should I notice in the data?

As you browse, you will see that denied claims have values in the denial_code and denial_category columns. Paid claims have NULL in those fields. You will use these patterns in the next step to investigate what is driving denials.

Seeing fewer than 200 claims?

If your count is less than 200, one of the claims batches may not have executed properly. Run SELECT MAX(claim_id) FROM claims; to find the last claim that loaded. Re-paste the batch that starts after that claim ID.

Help me figure out which claims batch failed to load in DB Browser for SQLite.

Your database is ready for investigation. Next up, you will query this data to understand denial patterns and form your initial hypothesis about what is causing the spike.

Run Initial SQL Investigation and Form Your Hypothesis

Your database is loaded and verified. Now it is time to act like the Senior BA on this case: stakeholders at the state agency believe eligibility verification gaps are driving the rising denial rate.

Your job is to validate or challenge that assumption with data. In this step, you will write three SQL queries that reveal denial patterns and form an initial hypothesis about the root cause.

In this step, get ready to:

• Query the overall claims status breakdown and calculate the denial rate.
• Break down denials by CARC code to identify the top denial drivers.
• Analyze the monthly trend and form your initial hypothesis.

Query the overall claims status breakdown

Every investigation starts with the big picture. Before drilling into denial codes, you need to know what proportion of claims are being denied overall.

• Switch to the Execute SQL tab in DB Browser for SQLite.
• Type the following query into the SQL editor to see how claims break down by status:

-- Query 1: Claims status summary
SELECT
    status,
    COUNT(*) AS claim_count,
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM claims), 1) AS percentage
FROM claims
GROUP BY status
ORDER BY claim_count DESC;

• Highlight the query text you just typed, then press F5 (or Ctrl+Return) to execute only the selected statement.

What does this query do?

The GROUP BY status clause buckets every claim into its status value (paid, denied).

The percentage column uses a correlated subquery to divide each group's count by the total number of claims. This gives you the denial rate as a single number you can report to stakeholders.

You should see two rows in the results pane: one for paid claims (the majority) and one for denied claims showing the overall denial rate.

Seeing an error instead of results?

• Make sure you highlighted only the Query 1 text before pressing F5. If nothing is selected, DB Browser executes all text in the editor.
• Check for a missing semicolon at the end of the statement.
• Confirm that medicaid_claims.db is still open in the title bar. If not, reopen it via File > Open Database.

Help me troubleshoot my SQL query in DB Browser for SQLite

Break down denials by CARC code

You know the overall denial rate. Now you need to find out which specific denial codes are driving the problem. In Medicaid claims adjudication, every denial carries a standardized CARC (Claim Adjustment Reason Code) that explains why it was denied.

• Add a blank line below your first query in the SQL editor, then type this denial breakdown query:

-- Query 2: Denial breakdown by code
SELECT
    denial_code,
    denial_category,
    COUNT(*) AS denial_count,
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM claims WHERE status = 'denied'), 1) AS pct_of_denials,
    ROUND(SUM(billed_amount), 2) AS total_denied_amount
FROM claims
WHERE status = 'denied'
GROUP BY denial_code, denial_category
ORDER BY denial_count DESC;

• Highlight only this new query, then press F5 to execute it.

What does this query do?

This filters to denied claims only, then groups them by denial_code and denial_category. The denial_category column in the claims table provides the human-readable description for each code.

The pct_of_denials column shows what percentage of all denials each code accounts for. The total_denied_amount shows the dollar impact.

Look at the top row in your results. CO-16 (missing_invalid_info) accounts for the highest denial count, with CO-22 (coordination_of_benefits) in second place.

What are CARC codes CO-16 and CO-22?

CO-16 means the claim lacks information or has a submission/billing error. CO-22 means another payer may be responsible under coordination of benefits rules.

At first glance, CO-16 dominating denials suggests that providers are submitting claims with missing information. A natural assumption: maybe member eligibility data is not being verified properly before claims are submitted.

Query returns zero rows?

• Confirm you included the WHERE status = 'denied' filter. Without it, the query groups all claims including paid ones.
• Check that 'denied' is lowercase and in single quotes. SQL string comparisons are case-sensitive in SQLite by default.

Help me fix my denial breakdown query

Identify the monthly trend and form your hypothesis

You know CO-16 is the biggest driver. Now you need to understand the timeline. If denials spiked at a specific point, that points to a triggering event rather than a chronic issue.

Before you run this next query, think about what you expect to see. If eligibility gaps are the cause, what would the monthly pattern look like?

• Add a blank line below your second query, then type this monthly trend query:

-- Query 3: Monthly denial trend
SELECT
    strftime('%Y-%m', submit_date) AS month,
    COUNT(*) AS total_claims,
    SUM(CASE WHEN status = 'denied' THEN 1 ELSE 0 END) AS denied_claims,
    ROUND(SUM(CASE WHEN status = 'denied' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS denial_rate_pct
FROM claims
GROUP BY strftime('%Y-%m', submit_date)
ORDER BY month;

• Highlight only the Query 3 text, then press F5 to execute it.

What does this query do?

The strftime('%Y-%m', submit_date) function extracts the year-month from each claim's submission date, letting you see trends month by month.

The CASE WHEN expression counts denied claims conditionally within each month. Dividing by total claims gives you the denial rate as a percentage per month.

Look at the results. Denial rates jump sharply starting in March 2026 and remain elevated through June. This pattern matches a triggering event rather than a slow degradation.

What does this tell you?

January and February show moderate denial rates. March onward shows a clear spike. Combined with CO-16 dominating the denials, a reasonable initial hypothesis forms: a recent policy change or system update disrupted eligibility verification, causing providers to submit claims with missing information.

Your hypothesis at this point: eligibility verification gaps, possibly from a recent policy change, are causing the spike in denials.

• Save your SQL work by clicking the Save SQL file button in the toolbar (or press Ctrl+Shift+S).
• Save the file as initial_analysis.sql in the same folder as your medicaid_claims.db file.

Add the file header comment

Scroll to the very top of your SQL editor and add a comment line: -- Step 2: Initial SQL Investigation. This documents which step the queries belong to, making your work easier to navigate later.

✔️ Awesome, I've got everything!

Great. Confirm you have saved initial_analysis.sql with all three queries.

ⓧ I'd like to double check the full code

Your initial_analysis.sql file should contain these three query blocks in order. Scroll through your editor and confirm each is present:

• The file header comment: -- Step 2: Initial SQL Investigation.
• Query 1 (Claims status summary): a SELECT grouping by status with a percentage column.
• Query 2 (Denial breakdown by code): a SELECT filtering WHERE status = 'denied', grouping by denial_code and denial_category.
• Query 3 (Monthly denial trend): a SELECT using strftime('%Y-%m', submit_date) with a CASE WHEN for denied count.

You now have data pointing to CO-16 as the top denial driver and a clear March 2026 spike. Your hypothesis says eligibility gaps are the culprit. In the next step, you will model the full claims adjudication process as a cross-functional swimlane diagram to see where handoff failures could create this pattern.

Model the Current-State Claims Adjudication Process

Your SQL investigation points at CO-16 as the top denial code and a spike beginning in March 2026. Those are useful signals, but they don't tell you where in the process the failure occurs.

To find the failure point, you need to see the full cross-functional flow from patient visit to provider payment. In this step, you'll build a swimlane diagram in draw.io that maps every handoff between actors in the claims adjudication process. Handoffs are where failures hide.

In this step, get ready to:

• Set up a cross-functional swimlane diagram with 4 actor lanes.
• Map the end-to-end claims adjudication process from service delivery to payment.
• Annotate the critical handoff failure point that you'll prove with data in the next step.

Open draw.io and create swim lanes

A swimlane diagram assigns each actor to a horizontal band. Every process step lives inside the lane of the actor responsible for it. When a connector crosses from one lane to another, that's a handoff.

• Open your browser and navigate to https://app.diagrams.net/.
• When draw.io asks where to store your diagram, select Device so the file saves locally.
• In the template dialog, select the Swimlane Diagram template from the Basic template collection, then click Create.

You should see a canvas with a default swimlane pool containing 2 lanes. You'll expand this to 4 lanes.

• Click on one of the existing swimlane rows to select it.
• In the Arrange tab of the format panel on the right, click Insert Row After twice to add two more lanes (totaling 4).
• Double-click the label area of each lane and name them, top to bottom: Member, Provider/Billing, MCO, State Medicaid Agency.

Your canvas should now show 4 clearly labeled horizontal swim lanes.

Why swimlanes instead of a linear flowchart?

A linear flowchart shows the order of steps but hides who owns each step. A cross-functional swimlane diagram forces you to assign every step to a responsible actor.

When a connector crosses lanes, you immediately see a handoff. Handoff points are where timing mismatches, data gaps, and miscommunication cause process failures. Junior BAs draw linear flows. Senior BAs draw cross-functional models that expose responsibility gaps.

What is an MCO?

An MCO (Managed Care Organization) is a health plan that contracts with the state to administer Medicaid benefits. The MCO receives claims from providers, runs pre-payment edits, and makes the pay/deny decision on behalf of the state.

Map the end-to-end claims adjudication flow

Now you'll populate the lanes with process steps, working from left to right. Each step is a rectangle placed in the lane of the responsible actor. Decision points are diamond shapes.

• Click More Shapes at the bottom of the left panel, enable the Flowchart shape library, then click Apply.
• Drag a rectangle from the shape library into the Member lane. Double-click it and label it Receives medical service.
• Add the following process steps in the Provider/Billing lane (one rectangle per step): Provider enrolls in network, Verifies member eligibility, Submits claim (EDI 837).
• In the MCO lane, add the pre-payment edit steps: Enrollment check, Duplicate check, Authorization check, Coding validation.
• Add a diamond (decision) shape in the MCO lane and label it Pass all edits?. Draw a connector labeled Yes to a rectangle Adjudication decision, and a connector labeled No to a rectangle Deny claim.
• Add a second diamond in the MCO lane after adjudication: Approve or deny?. Route Approve to Generate remittance advice (ERA 835) and Deny to Send denial notification.
• In the State Medicaid Agency lane, add: Provider enrollment activation, Billing group association (batch), Audit and compliance review.
• Back in the Provider/Billing lane, add: Receives payment/denial and a third diamond Accept or appeal? with a path leading to Submit appeal that routes back into the MCO lane.

• Draw connectors between shapes to show the flow. Hover over a shape until you see blue arrows, then drag from one shape to the next.
• Make sure connectors cross lane boundaries where handoffs occur (for example, from Provider enrollment activation in the State Agency lane to Billing group association (batch) in the same lane, and then across to the MCO Enrollment check).

Step back and look at your diagram. You should have at least 12 process steps across the 4 lanes and 3 diamond decision points.

Quick shape alignment

Select multiple shapes and use the blue guide lines that appear to help you space and align them evenly within each lane.

Shapes not staying in their lane?

When you drag a shape into a lane, the lane outline turns purple to indicate it will contain the shape. If you drop a shape outside the purple zone, it floats independently. Drag it back into the lane until you see the purple highlight, then release.

help me fix shapes that won't stay attached to their swimlane in draw.io

Annotate the batch processing failure and export

The critical insight from your earlier SQL work is that denials spiked when new providers started billing. The handoff between the State Agency activating a provider's enrollment and the MCO's billing group association batch is where the timing gap lives.

• Locate the connector between Provider enrollment activation and Billing group association (batch) in the State Medicaid Agency lane.
• Drag a text shape (from the General library) near this connector.
• Type the annotation: Batch runs weekly. Gap exists between enrollment and group linkage..
• Select the text shape. In the Style tab of the format panel, change the font color to red.
• Optionally draw a red dashed connector from the annotation to the handoff connector so it visually highlights the gap.

Your diagram now tells the story: the weekly batch creates a window where new providers have an active enrollment but no billing group linkage. Claims submitted during that window hit the MCO's enrollment check and auto-deny with CO-16.

• Export your diagram as a PNG by clicking File in the menu bar.
• Select Export As, then PNG.
• In the export dialog, leave the default settings and click Export.
• Save the file as claims_adjudication_swimlane.png to your Desktop.

Before you check the final result, ask yourself: does your diagram have 4 swim lanes, at least 12 process steps, 3 decision diamonds, and the red annotation on the batch processing handoff?

Open the exported PNG in your image viewer. You should see a cross-functional swimlane diagram with 4 horizontal lanes (Member, Provider/Billing, MCO, State Medicaid Agency), process steps flowing left to right, decision diamonds branching the flow, and a red annotation calling out the batch processing gap.

Diagram looks cramped or cut off in the export?

If your shapes are overlapping or the export cuts off edges, try re-exporting with a higher Zoom setting (200%) in the export dialog. You can also resize lanes by dragging their borders in the editor before exporting.

help me fix my draw.io export that looks cramped

Your process model is complete. You now have a visual map showing exactly where the batch processing gap sits in the overall flow. Next, you'll use advanced SQL to prove this handoff failure is the true root cause of the denial spike.

Advanced SQL Deep-Dive Reveals the Real Root Cause

Your initial investigation pointed to eligibility gaps as the primary driver of denials. A junior BA might stop there and recommend an eligibility verification enhancement. But a senior BA challenges assumptions with data.

In this step, you will use CTEs and SQL window functions to cross-reference denial timing with provider enrollment dates. The results will force you to revise your hypothesis entirely.

In this step, get ready to:

• Write a CTE that segments providers into enrollment-age cohorts and calculates denial rates per cohort.
• Use window functions to identify top providers by denial volume and track their month-over-month trend.
• Write a definitive linkage-gap analysis proving the real root cause is batch processing timing.

Write the provider enrollment age CTE

If eligibility gaps truly cause most denials, the denial rate should be roughly even across all providers regardless of how long they have been enrolled. A CTE lets you calculate each provider's "enrollment age" at the time of each claim, then group results into cohorts.

• In DB Browser for SQLite, click the Execute SQL tab.
• Clear any existing SQL in the editor pane. Paste the following CTE definition that calculates days since enrollment for every claim:

-- Step 4: Advanced SQL Deep-Dive
-- Query 1: CTE - Provider enrollment age cohort analysis
WITH provider_claim_age AS (
    SELECT
        c.claim_id,
        c.provider_id,
        p.provider_name,
        c.status,
        c.denial_code,
        c.billed_amount,
        julianday(c.submit_date) - julianday(p.enrollment_date) AS days_since_enrollment,
        CASE
            WHEN julianday(c.submit_date) - julianday(p.enrollment_date) <= 30 THEN '0-30 days'
            WHEN julianday(c.submit_date) - julianday(p.enrollment_date) <= 60 THEN '31-60 days'
            WHEN julianday(c.submit_date) - julianday(p.enrollment_date) <= 90 THEN '61-90 days'
            ELSE '90+ days'
        END AS enrollment_cohort
    FROM claims c
    JOIN providers p ON c.provider_id = p.provider_id
)

• Directly below the closing parenthesis (no blank line between), paste the SELECT that aggregates denial rates by cohort:

SELECT
    enrollment_cohort,
    COUNT(*) AS total_claims,
    SUM(CASE WHEN status = 'denied' THEN 1 ELSE 0 END) AS denied_claims,
    ROUND(SUM(CASE WHEN status = 'denied' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS denial_rate_pct,
    ROUND(SUM(CASE WHEN status = 'denied' THEN billed_amount ELSE 0 END), 2) AS denied_amount
FROM provider_claim_age
GROUP BY enrollment_cohort
ORDER BY
    CASE enrollment_cohort
        WHEN '0-30 days' THEN 1
        WHEN '31-60 days' THEN 2
        WHEN '61-90 days' THEN 3
        ELSE 4
    END;

What does this query do?

The WITH clause creates a temporary result set called provider_claim_age. For every claim, it calculates how many days the provider had been enrolled when that claim was submitted.

• julianday() converts dates to numbers so you can subtract them to get days between two dates.
• The CASE expression assigns each claim to a cohort (0-30, 31-60, 61-90, or 90+ days) based on the provider's enrollment age.
• The final SELECT aggregates denial rates per cohort so you can compare new providers against established ones.

Before you run this query, think about what you expect. If eligibility gaps are the main problem, should new providers have a dramatically different denial rate than established ones?

• Click the Execute button (the play icon) or press Ctrl+Enter to run the query.

You should see four rows in the results grid. The 0-30 days and 31-60 days cohorts show a denial rate around 34%, while the 90+ days cohort shows roughly 8%. That is a massive gap that eligibility issues alone cannot explain.

Why does this challenge your hypothesis?

If eligibility verification gaps were the root cause, denials would be distributed evenly across all providers. Instead, newly enrolled providers are denied at 4x the rate of established ones. Something specific to new provider onboarding is causing these denials.

Seeing zero rows or an error?

• Make sure you pasted both parts (the WITH clause and the SELECT) together as one continuous query with no blank line between them.
• Check that the closing parenthesis ) of the CTE appears on its own line immediately before the SELECT keyword.

Help me debug my CTE enrollment age query in DB Browser for SQLite

Identify top denial providers with window functions

The cohort analysis tells you that new providers have high denial rates. Now you need to see which specific providers are driving the problem and whether their denial rates are changing over time. Window functions let you rank providers and compare month-over-month trends in a single query.

• Open a new line below your first query (leave a blank line for readability). Paste the first CTE that calculates monthly denial rates per provider:

-- Query 2: Window function - Top providers by denial volume with month-over-month trend
WITH monthly_provider_denials AS (
    SELECT
        c.provider_id,
        p.provider_name,
        p.enrollment_date,
        strftime('%Y-%m', c.submit_date) AS month,
        COUNT(*) AS total_claims,
        SUM(CASE WHEN c.status = 'denied' THEN 1 ELSE 0 END) AS denied_claims,
        ROUND(SUM(CASE WHEN c.status = 'denied' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS denial_rate
    FROM claims c
    JOIN providers p ON c.provider_id = p.provider_id
    GROUP BY c.provider_id, p.provider_name, p.enrollment_date, strftime('%Y-%m', c.submit_date)
),

• Directly below the comma, paste the second CTE and the final SELECT that applies window functions:

ranked_providers AS (
    SELECT
        provider_id,
        provider_name,
        enrollment_date,
        month,
        total_claims,
        denied_claims,
        denial_rate,
        LAG(denial_rate) OVER (PARTITION BY provider_id ORDER BY month) AS prev_month_rate,
        RANK() OVER (ORDER BY SUM(denied_claims) OVER (PARTITION BY provider_id) DESC) AS denial_rank
    FROM monthly_provider_denials
)
SELECT
    provider_id,
    provider_name,
    enrollment_date,
    month,
    denied_claims,
    denial_rate,
    prev_month_rate,
    ROUND(denial_rate - COALESCE(prev_month_rate, 0), 1) AS rate_change
FROM ranked_providers
WHERE denial_rank <= 10
ORDER BY provider_id, month;

What do LAG and RANK do here?

• LAG(denial_rate) OVER (PARTITION BY provider_id ORDER BY month) looks at each provider's denial rate from the previous month. This lets you calculate month-over-month change.
• RANK() OVER (ORDER BY SUM(denied_claims)...DESC) ranks providers by their total denial volume across all months. The WHERE clause filters to the top 10.
• COALESCE(prev_month_rate, 0) handles the first month for each provider (where there is no previous month) by treating it as zero.

• Select only the Query 2 text (from the -- Query 2 comment through the final semicolon).
• Click Execute to run just this query.

Scan the enrollment_date column in the results. Every provider in the top 10 by denial volume was enrolled in 2026. All of them are recently enrolled providers. This confirms the cohort pattern is not a fluke.

Query returns no results or a syntax error?

• Ensure the comma after the first CTE's closing parenthesis is present. Two CTEs in a WITH clause are separated by a comma, not the word WITH again.
• If you selected text to run, make sure you selected from WITH through the final semicolon. Partial selections will fail.

Help me fix my window function query with LAG and RANK in SQLite

Prove the batch processing timing defect

You now know that new providers have disproportionate denials and that all top-denial providers enrolled recently. The final question: why? Look at the providers table. Each provider has both an enrollment_date and a group_link_date. The gap between these two dates is the window where claims auto-deny.

• Below your second query, paste the definitive linkage-gap analysis:

-- Query 3: Definitive proof - Group linkage timing analysis
WITH linkage_gap_analysis AS (
    SELECT
        c.claim_id,
        c.provider_id,
        p.provider_name,
        c.submit_date,
        p.enrollment_date,
        p.group_link_date,
        c.status,
        c.denial_code,
        c.billed_amount,
        julianday(c.submit_date) - julianday(p.enrollment_date) AS days_after_enrollment,
        julianday(p.group_link_date) - julianday(p.enrollment_date) AS days_to_linkage,
        CASE
            WHEN c.submit_date < p.group_link_date THEN 'before_linkage'
            ELSE 'after_linkage'
        END AS claim_vs_linkage
    FROM claims c
    JOIN providers p ON c.provider_id = p.provider_id
    WHERE p.enrollment_date >= '2026-01-01'
)
SELECT
    claim_vs_linkage,
    COUNT(*) AS total_claims,
    SUM(CASE WHEN status = 'denied' AND denial_code = 'CO-16' THEN 1 ELSE 0 END) AS co16_denials,
    ROUND(SUM(CASE WHEN status = 'denied' AND denial_code = 'CO-16' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS co16_denial_rate,
    ROUND(SUM(CASE WHEN status = 'denied' THEN billed_amount ELSE 0 END), 2) AS denied_amount
FROM linkage_gap_analysis
GROUP BY claim_vs_linkage;

What does this query prove?

• The CTE tags every claim from a new provider (enrolled in 2026) as either before_linkage or after_linkage based on whether the claim was submitted before or after the provider's billing group was linked.
• The final SELECT counts CO-16 denials in each group. If the batch timing gap causes denials, almost all CO-16 denials should fall in the before_linkage bucket.

Before you run this, predict: what percentage of CO-16 denials from new providers do you think occurred before the billing group linkage batch ran?

• Select the Query 3 text and click Execute.

The results show two rows. The before_linkage row shows that 87% of CO-16 denials from new providers occurred when claims were submitted before the weekly batch had linked their billing group. The after_linkage row shows near-zero CO-16 denials.

What is the revised root cause?

The root cause is NOT eligibility verification gaps. It is a batch processing timing defect. The weekly batch that links new providers to their billing group NPI runs too infrequently, creating a 1-7 day window where claims auto-deny with code CO-16.

Your revised hypothesis: providers enrolled within 60 days have a 34% denial rate (vs. 8% for established providers) because the system has no real-time validation of billing group association at claim submission time.

• Save your SQL file as advanced_analysis.sql by copying all three queries from the Execute SQL tab into a text file.

Seeing NULL in the co16_denial_rate column?

• Make sure the WHERE clause filters to p.enrollment_date >= '2026-01-01'. Without this filter, established providers (who have no linkage gap) dilute the results.
• Check that denial_code = 'CO-16' uses the exact string from the data, including the hyphen.

Help me debug my linkage gap analysis query in SQLite

✔️ Awesome, I've got everything!

Great. Confirm you have saved advanced_analysis.sql with all three queries.

ⓧ I'd like to double check the full code

Your advanced_analysis.sql file should contain the following three queries in order. Scroll up and confirm each one is present:

• Query 1: The provider_claim_age CTE with the enrollment cohort CASE expression and the aggregating SELECT with the custom ORDER BY.
• Query 2: The monthly_provider_denials CTE, the ranked_providers CTE with LAG and RANK window functions, and the final SELECT filtering to the top 10.
• Query 3: The linkage_gap_analysis CTE filtering to providers enrolled since 2026-01-01, comparing submit_date against group_link_date, and the final SELECT grouping by claim_vs_linkage.

You have just done what separates a senior BA from a junior one. You challenged the obvious answer, used advanced SQL to prove a different root cause, and can now quantify the problem precisely. Next up, you will translate these findings into formal BA deliverables that drive action.

Produce the Senior BA Work Package

Your SQL analysis has proven the root cause: a weekly batch processing timing defect is generating 34% denial rates for newly enrolled providers. That finding now needs to travel beyond your database.

A project manager needs to hand requirements to a development team. A compliance officer needs to review the proposed changes. A state agency client needs to approve the budget. Data alone does not drive any of those actions. In this step, you will translate your SQL findings into three formal BA deliverables that speak to each of those audiences.

In this step, get ready to:

• Create a Gap Analysis document with quantified current-state vs. future-state metrics.
• Create a Stakeholder Analysis matrix mapping 5 groups with competing priorities.
• Create a Business Requirements Document with functional requirements and a downstream system impact section.

Create the Gap Analysis document

A gap analysis quantifies the distance between where the system is today and where it needs to be. It gives decision-makers a crisp before/after comparison with dollar figures attached.

• Open Google Docs (or Microsoft Word) and create a new document.
• Title the document Gap Analysis.
• Add a heading called Current State and write a paragraph covering these three data points from your SQL analysis:

• Provider-to-billing-group linkage runs as a weekly batch process.
• New providers (enrolled within 60 days) experience a 34% claims denial rate.
• Annualized rework cost across manual re-submissions, provider calls, and staff processing time is $2.1M.

• Add a heading called Future State and document the target:

• Real-time or daily batch processing for provider group linkage.
• Target denial rate for new providers: 8% (matching established provider baseline).
• Projected annual savings: $1.7M from eliminated rework.

• Add a heading called Gap Description and write one clear sentence: the system lacks real-time provider-to-billing-group linkage validation at claim submission time.
• Save the document.

Your completed document has three sections with specific dollar figures tied directly to your SQL query results.

Why quantify both cost and savings?

The $2.1M current cost justifies the project to finance teams. The $1.7M projected savings (the difference after accounting for ongoing baseline denials) gives leadership an ROI figure for prioritization against competing initiatives.

Create the Stakeholder Analysis matrix

A stakeholder analysis maps who cares about this change, what they care about, and how much power they have to block or accelerate it. Competing priorities between groups are where real-world projects stall.

• Create a new Google Doc (or Word document) and title it Stakeholder Analysis.
• Create a table with 5 columns: Stakeholder Group, Interest/Priority, Influence Level, Impact of Change, Communication Approach.
• Add 5 rows, one per stakeholder group. Fill each row using the details below.

Here are the 5 stakeholder groups and their priorities:

• State Medicaid Agency — Interest: compliance with federal timely-filing rules and faster adjudication. Influence: High. Impact: fewer regulatory flags from CMS. Communication: formal briefings with data-backed recommendations.
• Provider community — Interest: fewer denials and faster payment. Influence: High. Impact: reduced administrative burden and improved cash flow. Communication: provider bulletins and webinars explaining timeline for fix.
• MCO operations — Interest: reduced manual review workload. Influence: Medium. Impact: fewer appeals and re-submissions to process. Communication: operational impact summaries in sprint reviews.
• IT/Development team — Interest: clear requirements and a reasonable implementation timeline. Influence: Medium. Impact: must modify the batch scheduler and add real-time validation logic. Communication: BRD walkthroughs and technical design sessions.
• Compliance/Audit — Interest: no false approvals and a complete audit trail. Influence: Medium. Impact: new validation must still log all decisions for audit. Communication: compliance review meetings with requirement sign-off.

• Save the document.

Your matrix should now show 5 rows with distinct, sometimes conflicting priorities. The Provider community wants speed. Compliance/Audit wants caution. That tension is exactly what a senior BA surfaces.

Why do competing priorities matter?

A junior BA writes requirements in a vacuum. A senior BA anticipates that the Provider community will push for an immediate fix while Compliance will demand extensive UAT. Documenting both priorities upfront prevents surprise scope battles during implementation.

Create the Business Requirements Document

The Business Requirements Document is the centerpiece of your work package. It tells a development team exactly what to build, a compliance officer what constraints apply, and a state client what outcomes to expect.

• Create a new Google Doc (or Word document) and title it Business Requirements Document.
• Add a heading called Executive Summary and write 2-3 sentences tying your findings to the business problem: rising claims denials traced to a batch processing timing defect, 34% new-provider denial rate, $2.1M annual rework cost.
• Add a heading called Functional Requirements.

Write at least 5 requirements using the "shall" convention. Each requirement gets a unique ID, a description, and acceptance criteria. Use these as your starting requirements:

• REQ-001: The system shall validate provider billing group association in real-time at claim submission.
• REQ-002: The system shall return a clear error code (CO-16 with resolution instructions) when a provider lacks billing group linkage.
• REQ-003: The system shall process provider-to-billing-group linkage at minimum daily (replacing the current weekly batch).
• REQ-004: The system shall display provider group linkage status on the Provider Portal within 1 business day of enrollment.
• REQ-005: The system shall log all real-time validation decisions to the audit trail with timestamp, provider ID, and result.

• For each requirement, add an Acceptance Criteria line that describes how a tester would verify it passed.
• Add a heading called Non-Functional Requirements and include: validation must complete in under 2 seconds, the system must maintain 99.9% uptime during batch transitions, and audit logs must be retained for 7 years.

Now add the section that separates a senior BA from a junior BA.

• Add a heading called Downstream System Impact Analysis.
• Under it, create three sub-headings and write 2-3 sentences for each:

• EDI Batch Processing — will receive fewer invalid claims because real-time validation catches missing group linkage before the claim enters the EDI pipeline.
• Provider Portal — must be updated to display group linkage status so providers can self-check before submitting claims.
• Financial Management — projected $1.7M annual savings from reduced rework, fewer manual re-adjudications, and lower call center volume.

• Save the document.

What makes downstream impact analysis senior-level?

Junior BAs stop at the requirements list. Senior BAs trace each requirement forward to see which other systems it touches. Adding real-time validation affects the EDI pipeline's error volume, the Provider Portal's UI, and the financial forecasting models. Documenting these connections prevents "surprise" scope expansion during development.

Review your BRD and confirm it contains: an Executive Summary, at least 5 functional requirements with acceptance criteria, non-functional requirements, and three downstream system impacts.

Missing acceptance criteria for a requirement?

A good acceptance criterion answers: "How would a tester prove this requirement is met?" For REQ-001, the acceptance criterion might be: "Submit a claim for a provider with no group linkage. System returns CO-16 within 2 seconds. Submit a claim for a provider with active group linkage. System accepts the claim."

If you are unsure how to write acceptance criteria for a specific requirement, help me write acceptance criteria for my BRD requirements.

You now have a complete, portfolio-ready BA work package: a Gap Analysis with dollar figures, a Stakeholder Analysis that maps political dynamics, and a BRD that a development team could pick up tomorrow. Next up, challenge yourself with the Secret Mission to prove every requirement is testable and traceable.

Secret mission

Build a Requirements Traceability Matrix

Senior BAs at companies like Gainwell Technologies do not just write requirements. They prove every requirement can be tested, trace it back to a business need, and forward to a UAT test case. Can you build a bidirectional Requirements Traceability Matrix that leaves zero orphaned requirements?

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally and uses free web tools, so there are no ongoing costs. Decide whether to keep your resources as portfolio pieces, or delete them to free up disk space.

Resources you used:

• DB Browser for SQLite (installed application).
• medicaid_claims.db database file (local).
• SQL script files: schema.sql, seed_data.sql, initial_analysis.sql, advanced_analysis.sql, traceability_matrix_template.sql (local).
• claims_adjudication_swimlane.png exported diagram (local).
• Google Docs documents (Business Requirements Document, Gap Analysis, Stakeholder Analysis) and Google Sheets spreadsheet (Requirements Traceability Matrix).

Keep everything running

No action needed. Choose this if you want to keep your BA work package as a portfolio piece or revisit the SQL analysis later.

• Your medicaid_claims.db file, SQL scripts, and exported diagram remain on your local machine wherever you saved them.
• Your Google Docs and Google Sheets documents remain in your Google Drive.
• DB Browser for SQLite stays installed and ready for future SQL projects.
• Consider combining your diagram, SQL screenshots, and BA documents into a single PDF for interview demonstrations.

Pause - I'll come back to this later

Nothing to pause. All resources are either local files or free web-based documents with no running processes or active services.

• Close DB Browser for SQLite if it is still open.
• Close any open draw.io browser tabs.
• Your files and documents will be exactly where you left them when you return.

Delete - I don't want to use this again

Remove all project resources and start fresh. Follow each step below to fully clean up.

• Delete your local project files (medicaid_claims.db, all .sql files, and claims_adjudication_swimlane.png) by locating them in File Explorer and pressing Delete, or right-clicking and selecting Delete.
• Uninstall DB Browser for SQLite: open Settings (press the Windows key, type Settings), select Apps, then Installed apps. Find DB Browser for SQLite in the list, click the three-dot menu, and select Uninstall.
• Delete your Google Docs documents: open Google Drive at drive.google.com. Right-click each document (Business Requirements Document, Gap Analysis, Stakeholder Analysis) and select Move to trash.
• Delete your Google Sheets spreadsheet: in Google Drive, right-click the Requirements Traceability Matrix and select Move to trash.
• Empty your Google Drive trash: click Trash in the left sidebar, then click Empty trash to permanently remove the files.

Nice Work!

Nice Work!

You just completed a senior-level BA work package for a Medicaid claims denial investigation. You can now walk an interviewer through a data-driven root cause analysis, a cross-functional process model, and formal BA deliverables that translate findings into actionable system requirements.

You've learned how to:

• Use CTEs and window functions to disprove an initial hypothesis and identify a batch processing timing defect as the true root cause of rising claims denials.
• Create a cross-functional swimlane diagram with 4 actor lanes exposing handoff failures in the claims adjudication workflow.
• Produce a complete BA work package including a gap analysis quantifying $2.1M in rework cost, a stakeholder analysis mapping 5 groups with competing priorities, and a Business Requirements Document with downstream system impact analysis.
• Secret Mission: Built a Requirements Traceability Matrix proving bidirectional coverage from business needs through test cases.

Ready to quiz yourself?