Aggregate Query in SQL | Dataford Interview Questions

Write an aggregate query for data used in AIG Claims reporting.

Using the claims table, summarize claim records by claim_status. Include records with a missing status and treat missing claim amounts as zero when calculating totals.

Output

One row per claim status, including a row for NULL status.
Columns: claim_status, claim_count, and total_claim_amount.
Sort alphabetically by status, with NULL status last.

Schema

claims

Column	Type	Description
claim_idPK	INT	Unique claim identifier
claim_status	VARCHAR(30)	Current claim status
claim_amount	NUMERIC(12,2)	Claimed monetary amount

Your question is Aggregate Query in SQL. 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