Second-Highest Resolution Time | Dataford Interview Questions

Write an SQL query to find the second-highest ticket resolution time for each customer support agent, handling ties appropriately.

Return all tickets whose resolution time equals the second distinct-highest value for their agent. Ignore tickets with missing resolution times and agents without a second distinct resolution time.

Output

One row per qualifying ticket, with agent_id, agent_name, ticket_id, and resolution_minutes.
Include all tickets tied at the second-highest value.
Order by agent_id, then ticket_id ascending.

Schema

support_agents

Column	Type	Description
agent_idPK	INT	Unique identifier for a customer support agent
agent_name	VARCHAR(100)	Display name of the support agent

support_tickets

Column	Type	Description
ticket_idPK	INT	Unique identifier for a support ticket
agent_id	INT	Agent assigned to the ticket
resolution_minutes	INT	Time required to resolve the ticket in minutes
ticket_status	VARCHAR(30)	Current ticket status

Your question is Second-Highest Resolution Time. Start with the requirements and the two 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