Your question is Find Duplicate Records. Start with the requirements and the two 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.
PenFed Credit Union needs to identify duplicate records in its application intake pipeline before downstream reporting and servicing processes consume them. Treat records with the same member, product, application date, and requested amount as duplicates. A NULL requested amount should match another NULL requested amount.
Write a PostgreSQL query that returns every record belonging to a duplicate group and enriches it with the member name when available.
member_id, product_code, application_date, and requested_amount as the duplicate key. Do not use application_id or application_status.penfed_members, using Unknown member for the missing name.member_id, product code, application date, and application ID.| Column | Type | Description |
|---|---|---|
| application_idPK | INTEGER | Unique application record identifier |
| member_id | INTEGER | PenFed member identifier |
| product_code | VARCHAR(20) | Requested financial product code |
| application_date | DATE | Date the application was received |
| requested_amount | NUMERIC(12,2) | Requested loan or credit amount |
| application_status | VARCHAR(20) | Current application intake status |
| Column | Type | Description |
|---|---|---|
| member_idPK | INTEGER | Unique PenFed member identifier |
| member_name | VARCHAR(100) | Member display name |