Customers Contacting Support Again Within a Week

Medium
336.

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 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

ColumnType
ticket_idint
customer_idint
created_atdatetime
categoryvarchar

Primary key: ticket_id.

One row per ticket. Ticket ids are not in creation order — tickets imported from email get their id later.

Example

The rows must come back in the order shown.

Ticket

ticket_idcustomer_idcreated_atcategory
115012025-10-01 09:00:00Refund
145012025-10-08 22:00:00Refund
195012025-10-20 10:00:00Login
125022025-10-03 12:00:00Delivery
135032025-10-05 08:30:00Payment
105032025-10-05 18:00:00Payment
165032025-10-12 07:00:00Payment
155022025-10-11 12:00:00Delivery

Output

customer_idticket_idprevious_ticket_iddays_apart
50114117
50310130
50316107
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
ticket_idint
customer_idint
created_atdatetime
categoryvarchar

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_idcustomer_idcreated_atcategory
115012025-10-01 09:00:00Refund
145012025-10-08 22:00:00Refund
195012025-10-20 10:00:00Login
125022025-10-03 12:00:00Delivery
135032025-10-05 08:30:00Payment
105032025-10-05 18:00:00Payment
165032025-10-12 07:00:00Payment
155022025-10-11 12:00:00Delivery

Output

customer_idticket_idprevious_ticket_iddays_apart
50114117
50310130
50316107

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_id

Another 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 →