SQL: Reconcile Certification Records

HardSQL · PostgreSQL

Problem

Write a SQL query to reconcile duplicate or missing certification records and ensure data integrity.

Return every required certification, including those without a matching record. Also include certification records that do not correspond to a requirement. Treat multiple records for the same organization and certification as duplicates, and flag records with a missing certificate number as invalid.

Output

One row per required or unexpected organization and certification
Columns: organization_id, organization_name, certification_code, is_required, record_count, duplicate_count, integrity_status
Include statuses for VALID, MISSING, DUPLICATE, INVALID, and UNEXPECTED_RECORD
Order by organization_id, then certification_code

Schema

organizations

Column	Type	Description
org_idPK	INT	Organization identifier
organization_name	VARCHAR(150)	Organization display name

certification_requirements

Column	Type	Description
requirement_idPK	INT	Certification requirement identifier
org_id	INT	Required organization
certification_code	VARCHAR(30)	Required certification code
certification_name	VARCHAR(120)	Certification name

certification_records

Column	Type	Description
record_idPK	INT	Stored certification record identifier
org_id	INT	Organization associated with the record
certification_code	VARCHAR(30)	Stored certification code
certificate_number	VARCHAR(50)	Certificate reference number
issued_date	DATE	Certificate issue date
expiry_date	DATE	Certificate expiry date
status	VARCHAR(20)	Stored certification status

Tablesorganizationscertification_requirementscertification_records