LinkedIn: InMail Response Rate per Recruiter

Easy
728.

LinkedIn: InMail Response Rate per Recruiter

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

ColumnType
inmail_idint
recruiter_idint
candidate_idint
sent_atdatetime
replied_atdatetime

Primary key: inmail_id.

One row per InMail. replied_at is when the candidate first replied, or NULL if they never did.

Example

The rows must come back in the order shown.

InMail

inmail_idrecruiter_idcandidate_idsent_atreplied_at
11015012025-04-01 09:15:002025-04-01 18:40:00
21015022025-04-02 10:00:00NULL
31015032025-04-02 11:30:002025-04-05 08:10:00
41025042025-04-03 14:00:00NULL
51025052025-04-04 16:20:00NULL
61035062025-04-04 09:00:002025-04-04 09:45:00
71045072025-04-05 12:00:002025-04-06 10:00:00
81045082025-04-06 12:00:00NULL
91045092025-04-07 12:00:002025-04-07 13:00:00

Output

recruiter_idinmails_sentrepliesresponse_rate
10311100
1013266.7
1043266.7
102200
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.

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

ColumnType
inmail_idint
recruiter_idint
candidate_idint
sent_atdatetime
replied_atdatetime

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_idrecruiter_idcandidate_idsent_atreplied_at
11015012025-04-01 09:15:002025-04-01 18:40:00
21015022025-04-02 10:00:00NULL
31015032025-04-02 11:30:002025-04-05 08:10:00
41025042025-04-03 14:00:00NULL
51025052025-04-04 16:20:00NULL
61035062025-04-04 09:00:002025-04-04 09:45:00
71045072025-04-05 12:00:002025-04-06 10:00:00
81045082025-04-06 12:00:00NULL
91045092025-04-07 12:00:002025-04-07 13:00:00

Output

recruiter_idinmails_sentrepliesresponse_rate
10311100
1013266.7
1043266.7
102200

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_id

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