SQL Query for Max Value | Dataford Interview Questions

Your question is SQL Query for Max Value. Start with the requirements and the three tables on the right.

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

Problem

Alegeus needs to review the largest approved reimbursement claims across benefit categories. Write a PostgreSQL query that returns every claim tied for the maximum eligible amount in its category.

Requirements

Include only approved claims submitted during calendar year 2025.
Include claims belonging to active members and exclude rows with a missing claim amount.
Return all ties for the maximum claim amount in each category, along with the category, claim ID, member name, and maximum amount.
Order the results by category ID and claim ID.

Representative Data

The data includes tied maximums, denied claims, inactive members, unmatched members, null amounts, zero amounts, and claims outside the requested year.

Schema

benefit_categories

Column	Type	Description
category_idPK	INT	Benefit category identifier
category_name	VARCHAR(40)	Alegeus benefit category name

members

Column	Type	Description
member_idPK	INT	Member identifier
member_name	VARCHAR(100)	Member display name
is_active	BOOLEAN	Whether the member is currently active

claims

Column	Type	Description
claim_idPK	INT	Claim identifier
category_id	INT	Referenced benefit category
member_id	INT	Referenced member
claim_status	VARCHAR(20)	Claim processing status
submitted_at	DATE	Date the claim was submitted
claim_amount	NUMERIC(10,2)	Amount requested for reimbursement