Duplicate Credit Applications | Dataford Interview Questions

Write a query utilizing window functions like ROW_NUMBER() or DENSE_RANK() to identify duplicate credit applications submitted within the same hour for CRIF.

Use applicant_id and product_type to define a duplicate group. Exclude records without a submission timestamp.

Output

One row per application belonging to a duplicate group
Include application_id, applicant_id, product_type, application_hour, duplicate_count, and duplicate_sequence
Order by applicant, hour, submission timestamp, and application ID

Schema

credit_applications

Column	Type	Description
application_idPK	INT	Unique credit application identifier
applicant_id	VARCHAR(20)	CRIF applicant identifier
product_type	VARCHAR(30)	Credit product requested
submitted_at	TIMESTAMP	Application submission timestamp

Your question is Duplicate Credit Applications. 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