SQL Metrics by Business Unit

MediumSQL · PostgreSQL

Problem

Write SQL to analyze claim or operational data and summarize key metrics by business unit or time period.

Using the supplied claims and business_units tables, summarize records dated in 2025 by business unit and calendar month. Include claims whose business unit is not found in the reference table.

Output

One row per business unit and month
Columns: business_unit, claim_month, claim_count, total_incurred, and avg_processing_days
Include only periods containing at least one 2025 claim, ordered by business_unit, then claim_month

Output details

Order both business_unit and claim_month in ascending order.
Label claims whose business unit is missing from the reference table as Unassigned.
Use an inclusive lower bound of DATE '2025-01-01' and an exclusive upper bound of DATE '2026-01-01'.
Replace a null total_incurred sum with numeric 0.
Round avg_processing_days to two decimal places, leaving it NULL when no processing-day values exist.

Schema

claims

Column	Type	Description
claim_idPK	INT	Unique claim identifier
business_unit_id	INT	Reference to the responsible business unit
claim_date	DATE	Date the claim was recorded
incurred_amount	NUMERIC(12,2)	Amount incurred for the claim
processing_days	INT	Number of days used to process the claim

business_units

Column	Type	Description
unit_idPK	INT	Unique business unit identifier
unit_name	VARCHAR(100)	Business unit name

Tablesclaimsbusiness_units

Write SQL to analyze claim or operational data and summarize key metrics by business unit or time period.

Using the supplied claims and business_units tables, summarize records dated in 2025 by business unit and calendar month. Include claims whose business unit is not found in the reference table.

Output

One row per business unit and month
Columns: business_unit, claim_month, claim_count, total_incurred, and avg_processing_days
Include only periods containing at least one 2025 claim, ordered by business_unit, then claim_month

Output details

Order both business_unit and claim_month in ascending order.
Label claims whose business unit is missing from the reference table as Unassigned.
Use an inclusive lower bound of DATE '2025-01-01' and an exclusive upper bound of DATE '2026-01-01'.
Replace a null total_incurred sum with numeric 0.
Round avg_processing_days to two decimal places, leaving it NULL when no processing-day values exist.