Repeat Contact Rate by First-Ticket Month — SQL Problem
A customer's cohort is the month of their first ticket (the earliest created_at).
- Difficulty: Hard
- Topics: Dates, Subqueries, Aggregation
- Dialect: MySQL
- Problem: #345
Problem statement
A customer's cohort is the month of their first ticket (the earliest created_at). A customer is a repeat contact when they opened another ticket within 30 days of that first ticket — DATEDIFF(other.created_at, first.created_at) at most 30. Any other ticket counts, even one created in the same second as the first.
For each cohort, return the columns cohort_month ('YYYY-MM'), customers, repeat_customers and repeat_pct (100 * repeat_customers / customers, rounded to 2 decimals), ordered by cohort_month.
Tables
Table: CustomerTicket
| Column | Type |
|---|---|
| ticket_id | int |
| customer_id | int |
| created_at | datetime |
| topic | varchar |
Primary key: ticket_id.
One row per support ticket. A customer exists here only once they have opened a ticket.
Examples
Example 1
CustomerTicket
| ticket_id | customer_id | created_at | topic |
|---|---|---|---|
| 1 | 701 | 2025-01-05 10:00:00 | KYC |
| 2 | 701 | 2025-02-04 18:00:00 | KYC |
| 3 | 702 | 2025-01-20 09:00:00 | Refund |
| 4 | 702 | 2025-02-25 11:00:00 | Refund |
| 5 | 703 | 2025-01-31 22:00:00 | Login |
| 6 | 704 | 2025-02-02 08:00:00 | Delivery |
| 7 | 704 | 2025-02-02 08:00:00 | Delivery |
| 8 | 705 | 2025-02-14 13:00:00 | Payment |
| 9 | 703 | 2025-03-01 12:00:00 | Login |
Output
| cohort_month | customers | repeat_customers | repeat_pct |
|---|---|---|---|
| 2025-01 | 3 | 2 | 66.67 |
| 2025-02 | 2 | 1 | 50 |
How to solve Repeat Contact Rate by First-Ticket Month
Cohort analysis is three steps. Find the anchor: each customer's first ticket, MIN(created_at) grouped by customer. Classify the customer: join their tickets back and count how many fall within 30 days of the anchor (DATEDIFF(t.created_at, first_at) <= 30, so a ticket 30 calendar days later still counts and one 31 days later does not). The first ticket itself is always in that count, so a customer is a repeat contact exactly when the count is at least 2 — which also gets the same-second duplicate right without comparing ids. Aggregate per cohort: group the customers by DATE_FORMAT(first_at, '%Y-%m'), count them, count the repeaters with a conditional SUM, and divide, rounding to 2 decimals.
The cohort is the month of the first ticket only; a customer's later tickets in other months never create a cohort row of their own, which is why the grouping happens after the per-customer step and not on the raw tickets.
The alternative picks the first ticket as a row with ROW_NUMBER (ordered by time and then id, so ties pick one) and asks with a correlated EXISTS whether any other ticket id lies within 30 days. It states the definition literally; the counting version needs no correlated subquery. Both scale with tickets per customer.
Reference solution (MySQL)
WITH firsts AS (
SELECT customer_id, MIN(created_at) AS first_at
FROM CustomerTicket
GROUP BY customer_id
), counted AS (
SELECT f.customer_id, f.first_at, COUNT(*) AS tickets_in_30
FROM firsts f
JOIN CustomerTicket t ON t.customer_id = f.customer_id AND DATEDIFF(t.created_at, f.first_at) <= 30
GROUP BY f.customer_id, f.first_at
)
SELECT DATE_FORMAT(first_at, '%Y-%m') AS cohort_month,
COUNT(*) AS customers,
SUM(CASE WHEN tickets_in_30 >= 2 THEN 1 ELSE 0 END) AS repeat_customers,
ROUND(100 * SUM(CASE WHEN tickets_in_30 >= 2 THEN 1 ELSE 0 END) / COUNT(*), 2) AS repeat_pct
FROM counted
GROUP BY DATE_FORMAT(first_at, '%Y-%m')
ORDER BY cohort_monthAnother way
WITH firsts AS (
SELECT ticket_id, customer_id, created_at
FROM (SELECT ticket_id, customer_id, created_at,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at, ticket_id) AS rn
FROM CustomerTicket) x
WHERE rn = 1
)
SELECT DATE_FORMAT(f.created_at, '%Y-%m') AS cohort_month,
COUNT(*) AS customers,
SUM(CASE WHEN EXISTS (SELECT 1 FROM CustomerTicket o WHERE o.customer_id = f.customer_id AND o.ticket_id <> f.ticket_id AND DATEDIFF(o.created_at, f.created_at) <= 30) THEN 1 ELSE 0 END) AS repeat_customers,
ROUND(100 * SUM(CASE WHEN EXISTS (SELECT 1 FROM CustomerTicket o WHERE o.customer_id = f.customer_id AND o.ticket_id <> f.ticket_id AND DATEDIFF(o.created_at, f.created_at) <= 30) THEN 1 ELSE 0 END) / COUNT(*), 2) AS repeat_pct
FROM firsts f
GROUP BY DATE_FORMAT(f.created_at, '%Y-%m')
ORDER BY cohort_month← Gaps in the On-Call Rota · Sessionise API Calls and Count Server Errors →