Top High-Cost Claims Query | Dataford Interview Questions

Optum Financial needs a monthly review of unusually expensive claims. Write a PostgreSQL query that identifies the top 10% of eligible claims for May 2025 based on total paid amount.

Requirements

Include claims whose claim_date falls from 2025-05-01 through 2025-05-31, using an inclusive lower bound and exclusive upper bound.
Include only claims with status PAID or PARTIALLY_PAID and matching member and plan records.
Aggregate multiple claim lines into one total paid amount per claim, treating missing line amounts as zero.
Use a window function to identify the highest-cost 10%. Include ties at the cutoff and order the final output by total paid amount descending, then claim ID.

Schema

claims

Column	Type	Description
claim_idPK	INT	Unique claim identifier
member_id	INT	Reference to the covered member
plan_id	INT	Reference to the benefit plan
claim_date	DATE	Claim submission date
status	VARCHAR(30)	Claim processing status

claim_lines

Column	Type	Description
claim_line_idPK	INT	Unique claim line identifier
claim_id	INT	Reference to the claim
paid_amount	DECIMAL(12,2)	Amount paid for the claim line

members

Column	Type	Description
member_idPK	INT	Unique member identifier
member_name	VARCHAR(100)	Member display name

plans

Column	Type	Description
plan_idPK	INT	Unique benefit plan identifier
plan_name	VARCHAR(100)	Benefit plan name

Your question is Top High-Cost Claims Query. Start with the requirements and the four tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem