SQL Aggregation Query | Dataford Interview Questions

Cotiviti's claims analytics team needs a summary of approved claims processed during 2025. Write a PostgreSQL query using the claims table to report claim volume and total allowed amount by provider specialty.

Requirements

Include only claims with claim_status = 'Approved' and a service_date on or after January 1, 2025.
Return one row per provider specialty with the approved claim count and total allowed amount, ordered from highest to lowest total.

Schema

claims

Column	Type	Description
claim_idPK	INT	Unique claim identifier
provider_specialty	VARCHAR(80)	Specialty associated with the servicing provider
claim_status	VARCHAR(20)	Claim processing status
service_date	DATE	Date of service
allowed_amount	NUMERIC(12,2)	Amount allowed for the claim

Your question is SQL Aggregation Query. Start with the requirements and the one table 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

Requirements

Include only claims with claim_status = 'Approved' and a service_date on or after January 1, 2025.
Return one row per provider specialty with the approved claim count and total allowed amount, ordered from highest to lowest total.

Schema

claims

Column	Type	Description
claim_idPK	INT	Unique claim identifier
provider_specialty	VARCHAR(80)	Specialty associated with the servicing provider
claim_status	VARCHAR(20)	Claim processing status
service_date	DATE	Date of service
allowed_amount	NUMERIC(12,2)	Amount allowed for the claim

Your question is SQL Aggregation Query. Start with the requirements and the one table 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.