Customers Contacting Support Again Within a Week
A repeat contact is a ticket whose customer's previous ticket was created at most 7 calendar days earlier (by date: DATEDIFF of the two creation times is…
- Difficulty: Medium
- Topics: Window Functions, Dates
- Dialect: MySQL
- Problem: #336
Problem statement
A repeat contact is a ticket whose customer's previous ticket was created at most 7 calendar days earlier (by date: DATEDIFF of the two creation times is between 0 and 7). A customer's tickets are ordered by created_at, then ticket_id; a customer's first ticket has no previous one and is never a repeat contact.
Return every repeat contact with the columns customer_id, ticket_id, previous_ticket_id and days_apart (that DATEDIFF), ordered by customer_id, then ticket_id.
Tables
Table: Ticket
| Column | Type |
|---|---|
| ticket_id | int |
| customer_id | int |
| created_at | datetime |
| category | varchar |
Primary key: ticket_id.
One row per ticket. Ticket ids are not in creation order — tickets imported from email get their id later.
Examples
Example 1
Ticket
| ticket_id | customer_id | created_at | category |
|---|---|---|---|
| 11 | 501 | 2025-10-01 09:00:00 | Refund |
| 14 | 501 | 2025-10-08 22:00:00 | Refund |
| 19 | 501 | 2025-10-20 10:00:00 | Login |
| 12 | 502 | 2025-10-03 12:00:00 | Delivery |
| 13 | 503 | 2025-10-05 08:30:00 | Payment |
| 10 | 503 | 2025-10-05 18:00:00 | Payment |
| 16 | 503 | 2025-10-12 07:00:00 | Payment |
| 15 | 502 | 2025-10-11 12:00:00 | Delivery |
Output
| customer_id | ticket_id | previous_ticket_id | days_apart |
|---|---|---|---|
| 501 | 14 | 11 | 7 |
| 503 | 10 | 13 | 0 |
| 503 | 16 | 10 | 7 |
How to solve Customers Contacting Support Again Within a Week
Each ticket has to be compared with the customer's ticket just before it, which is what LAG gives: partition by customer_id, order by created_at and then ticket_id (two tickets can be created in the same second, and the tie-break makes "previous" well defined), and read the previous row's id and creation time. Ticket ids are deliberately not in time order, so ordering by id alone would pair the wrong tickets.
Window functions are computed after WHERE, so the filter goes in an outer query: drop each customer's first ticket (its LAG is NULL) and keep the pairs whose DATEDIFF(created_at, previous_at) is at most 7. DATEDIFF works on the date parts only — 1 October 09:00 to 8 October 22:00 is 7 days, a repeat contact, even though more than 168 hours passed. Two tickets on the same day are 0 days apart.
Before window functions this was a self join to every earlier ticket of the customer with a NOT EXISTS that rules out any ticket in between — the alternative. It returns the same pairs but is quadratic per customer; LAG needs one sort of the table by (customer, time).
Reference solution (MySQL)
WITH ordered AS (
SELECT customer_id, ticket_id, created_at,
LAG(ticket_id) OVER (PARTITION BY customer_id ORDER BY created_at, ticket_id) AS previous_ticket_id,
LAG(created_at) OVER (PARTITION BY customer_id ORDER BY created_at, ticket_id) AS previous_at
FROM Ticket
)
SELECT customer_id, ticket_id, previous_ticket_id, DATEDIFF(created_at, previous_at) AS days_apart
FROM ordered
WHERE previous_ticket_id IS NOT NULL
AND DATEDIFF(created_at, previous_at) <= 7
ORDER BY customer_id, ticket_idAnother way
SELECT c.customer_id, c.ticket_id, p.ticket_id AS previous_ticket_id, DATEDIFF(c.created_at, p.created_at) AS days_apart
FROM Ticket c
JOIN Ticket p
ON p.customer_id = c.customer_id
AND (p.created_at < c.created_at OR (p.created_at = c.created_at AND p.ticket_id < c.ticket_id))
WHERE DATEDIFF(c.created_at, p.created_at) <= 7
AND NOT EXISTS (
SELECT 1 FROM Ticket q
WHERE q.customer_id = c.customer_id
AND (q.created_at > p.created_at OR (q.created_at = p.created_at AND q.ticket_id > p.ticket_id))
AND (q.created_at < c.created_at OR (q.created_at = c.created_at AND q.ticket_id < c.ticket_id))
)
ORDER BY c.customer_id, c.ticket_id← Agents Carrying More Than Their Team's Average Workload · Services Missing Their Uptime Target →