Top Three Longest Resolutions | Dataford Interview Questions

ASML service teams want to identify the longest-resolving customer support issues for each operating region. Write a PostgreSQL query that ranks resolved tickets by resolution time within each region.

Requirements

Join tickets to customer sites and regions using their foreign-key relationships.
Calculate resolution time in hours from created_at to resolved_at.
Exclude unresolved tickets and tickets without a valid region.
Return no more than the three longest-resolving tickets per region, ordered by region and resolution time descending. Break ties with the ticket ID.

Schema

support_tickets

Column	Type	Description
ticket_idPK	INT	Unique support ticket identifier
site_id	INT	Customer site associated with the ticket
issue_title	VARCHAR(150)	Short description of the support issue
status	VARCHAR(20)	Current ticket status
created_at	TIMESTAMP	Timestamp when the ticket was created
resolved_at	TIMESTAMP	Timestamp when the ticket was resolved

customer_sites

Column	Type	Description
site_idPK	INT	Unique customer site identifier
site_name	VARCHAR(100)	Customer or facility site name
region_code	VARCHAR(10)	Region assigned to the site

regions

Column	Type	Description
region_codePK	VARCHAR(10)	Unique region code
region_name	VARCHAR(100)	Readable region name

Your question is Top Three Longest Resolutions. Start with the requirements and the three tables 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