Repeat Contact Rate by First-Ticket Month

Hard
345.

Repeat Contact Rate by First-Ticket Month

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

ColumnType
ticket_idint
customer_idint
created_atdatetime
topicvarchar

Primary key: ticket_id.

One row per support ticket. A customer exists here only once they have opened a ticket.

Example

The rows must come back in the order shown.

CustomerTicket

ticket_idcustomer_idcreated_attopic
17012025-01-05 10:00:00KYC
27012025-02-04 18:00:00KYC
37022025-01-20 09:00:00Refund
47022025-02-25 11:00:00Refund
57032025-01-31 22:00:00Login
67042025-02-02 08:00:00Delivery
77042025-02-02 08:00:00Delivery
87052025-02-14 13:00:00Payment
97032025-03-01 12:00:00Login

Output

cohort_monthcustomersrepeat_customersrepeat_pct
2025-013266.67
2025-022150
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.

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

ColumnType
ticket_idint
customer_idint
created_atdatetime
topicvarchar

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_idcustomer_idcreated_attopic
17012025-01-05 10:00:00KYC
27012025-02-04 18:00:00KYC
37022025-01-20 09:00:00Refund
47022025-02-25 11:00:00Refund
57032025-01-31 22:00:00Login
67042025-02-02 08:00:00Delivery
77042025-02-02 08:00:00Delivery
87052025-02-14 13:00:00Payment
97032025-03-01 12:00:00Login

Output

cohort_monthcustomersrepeat_customersrepeat_pct
2025-013266.67
2025-022150

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_month

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