LinkedIn: InMail Response Rate per Recruiter — SQL Problem
Talent Solutions coaches recruiters on outreach, and the first number they look at is how often a candidate answers an InMail.
- Difficulty: Easy
- Topics: Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #728
Problem statement
Talent Solutions coaches recruiters on outreach, and the first number they look at is how often a candidate answers an InMail.
For every recruiter who sent at least one InMail, return recruiter_id, inmails_sent, replies (InMails with a non-NULL replied_at) and response_rate, the percentage of InMails that got a reply, rounded to one decimal place. Order the rows by response_rate descending, then recruiter_id ascending.
A practice scenario set at LinkedIn: the tables and data are invented, and this is not a question from, or endorsed by, LinkedIn.
Tables
Table: InMail
| Column | Type |
|---|---|
| inmail_id | int |
| recruiter_id | int |
| candidate_id | int |
| sent_at | datetime |
| replied_at | datetime |
Primary key: inmail_id.
One row per InMail. replied_at is when the candidate first replied, or NULL if they never did.
Examples
Example 1
InMail
| inmail_id | recruiter_id | candidate_id | sent_at | replied_at |
|---|---|---|---|---|
| 1 | 101 | 501 | 2025-04-01 09:15:00 | 2025-04-01 18:40:00 |
| 2 | 101 | 502 | 2025-04-02 10:00:00 | NULL |
| 3 | 101 | 503 | 2025-04-02 11:30:00 | 2025-04-05 08:10:00 |
| 4 | 102 | 504 | 2025-04-03 14:00:00 | NULL |
| 5 | 102 | 505 | 2025-04-04 16:20:00 | NULL |
| 6 | 103 | 506 | 2025-04-04 09:00:00 | 2025-04-04 09:45:00 |
| 7 | 104 | 507 | 2025-04-05 12:00:00 | 2025-04-06 10:00:00 |
| 8 | 104 | 508 | 2025-04-06 12:00:00 | NULL |
| 9 | 104 | 509 | 2025-04-07 12:00:00 | 2025-04-07 13:00:00 |
Output
| recruiter_id | inmails_sent | replies | response_rate |
|---|---|---|---|
| 103 | 1 | 1 | 100 |
| 101 | 3 | 2 | 66.7 |
| 104 | 3 | 2 | 66.7 |
| 102 | 2 | 0 | 0 |
How to solve LinkedIn: InMail Response Rate per Recruiter
Group the InMails by recruiter and compute two counts per group. COUNT(*) is every InMail sent; COUNT(replied_at) counts only the rows where replied_at is not NULL, which is exactly the replies — the NULL-skipping behaviour of COUNT(column) does the conditional counting for free. The alternative spells the same thing out with SUM(CASE …) and SUM(IF(…)), which is the general pattern when the condition is more than "not NULL".
The rate is 100 * replies / sent. In MySQL / is decimal division, so 2 out of 3 gives 66.67 before rounding and 66.7 after; integer division would have given 0. Rounding comes last so no precision is lost along the way. Recruiters with the same rate are ordered by id so the output is fixed. The query is a single grouped scan.
Reference solution (MySQL)
SELECT recruiter_id,
COUNT(*) AS inmails_sent,
COUNT(replied_at) AS replies,
ROUND(100 * COUNT(replied_at) / COUNT(*), 1) AS response_rate
FROM InMail
GROUP BY recruiter_id
ORDER BY response_rate DESC, recruiter_idAnother way
SELECT recruiter_id, COUNT(*) AS inmails_sent, SUM(CASE WHEN replied_at IS NOT NULL THEN 1 ELSE 0 END) AS replies, ROUND(100 * SUM(IF(replied_at IS NULL, 0, 1)) / COUNT(inmail_id), 1) AS response_rate FROM InMail GROUP BY recruiter_id ORDER BY response_rate DESC, recruiter_id← LinkedIn: Company Pages With No New Followers in May 2025 · LinkedIn: Learning Course Completions per Category in Q1 2025 →