Ticket First Response Time

ticket_replies holds replies on support tickets. author_type is agent or customer.

For every ticket, compute the minutes between tickets.created_at and the first agent reply, rounded with ROUND(x, 2). Customer replies do not count. Tickets with no agent reply get NULL.

Return columns ticket_id, first_response_minutes. Order by ticket_id ascending.

Schema

CREATE TABLE tickets (
  ticket_id INTEGER PRIMARY KEY,
  subject TEXT NOT NULL,
  created_at TEXT NOT NULL
);
CREATE TABLE ticket_replies (
  reply_id INTEGER PRIMARY KEY,
  ticket_id INTEGER NOT NULL,
  author_type TEXT NOT NULL,
  replied_at TEXT NOT NULL
);

Sample data

tickets

ticket_id	subject	created_at
1	Cannot log in	2026-04-10 09:00:00
2	Refund request	2026-04-10 10:30:00
3	Bug in export	2026-04-10 11:15:00
4	Change plan	2026-04-11 08:00:00

ticket_replies

reply_id	ticket_id	author_type	replied_at
1	1	agent	2026-04-10 09:20:00
2	1	customer	2026-04-10 09:25:00
3	2	customer	2026-04-10 10:35:00
4	2	agent	2026-04-10 12:00:00
5	3	customer	2026-04-10 11:30:00
6	1	agent	2026-04-10 09:10:00

Hint 1

Put the author_type = 'agent' condition in the LEFT JOIN's ON clause. In WHERE it would drop tickets that have no agent reply.

Hint 2

strftime('%s', ts) gives Unix seconds. Divide the difference by 60.0 to get minutes.

Good practice for: Data Analyst, Product Manager, Backend Developer, Business Analyst

SQL (SQLite)

Run checks your query on the sample data (Ctrl + '). Submit also checks hidden databases.