LinkedIn: Boomerang Hires in Members' Work History
Employers like to know who comes back. The talent-insights team looks for boomerangs in members' work history: people who left a company, worked somewhere…
- Difficulty: Hard
- Topics: Window Functions, Subqueries, Dates
- Dialect: MySQL
- Problem: #747
Problem statement
Employers like to know who comes back. The talent-insights team looks for boomerangs in members' work history: people who left a company, worked somewhere else, and then returned.
A position is a return when the member held an earlier position at the same company, and the position immediately before it (the member's previous one by start date) was at a different company. For every return, give member_id, company_name, left_on (the latest end_date among the member's earlier positions at that company), returned_on (the return's start_date) and months_away, the whole months from left_on to returned_on. Order the rows by member_id, then returned_on.
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: WorkHistory
| Column | Type |
|---|---|
| position_id | int |
| member_id | int |
| company_name | varchar |
| start_date | date |
| end_date | date |
Primary key: position_id.
One row per position on a member's profile. A member's positions never overlap and never share a start date; end_date is NULL only for the current position.
Examples
Example 1
WorkHistory
| position_id | member_id | company_name | start_date | end_date |
|---|---|---|---|---|
| 1 | 1 | Northwind Labs | 2018-07-01 | 2020-03-31 |
| 2 | 1 | Kestrel Analytics | 2020-05-01 | 2021-12-31 |
| 3 | 1 | Northwind Labs | 2022-02-15 | NULL |
| 4 | 2 | Saffron Fintech | 2019-01-01 | 2019-12-31 |
| 5 | 2 | Saffron Fintech | 2020-01-01 | 2022-06-30 |
| 6 | 2 | Orbit Retail | 2022-08-01 | 2023-03-31 |
| 7 | 3 | Lotus Health | 2017-06-01 | 2018-05-31 |
| 8 | 3 | Cedar Robotics | 2018-07-01 | 2019-06-30 |
| 9 | 3 | Lotus Health | 2019-08-01 | 2020-12-31 |
| 10 | 3 | Cedar Robotics | 2021-02-01 | NULL |
Output
| member_id | company_name | left_on | returned_on | months_away |
|---|---|---|---|---|
| 1 | Northwind Labs | 2020-03-31 | 2022-02-15 | 22 |
| 3 | Lotus Health | 2018-05-31 | 2019-08-01 | 14 |
| 3 | Cedar Robotics | 2019-06-30 | 2021-02-01 | 19 |
How to solve LinkedIn: Boomerang Hires in Members' Work History
Two conditions define a return, and they look at different parts of the history. The first is local: the position just before this one was at another company. LAG(company_name) over the member's positions ordered by start date hands each position its predecessor's company, and prev_company <> company_name keeps the switches — it also drops a member's first position, whose LAG is NULL, and a promotion within one company, whose predecessor is the same company (member 2 in the example).
The second is global: somewhere earlier, the member worked at this company. A correlated subquery takes MAX(end_date) over the earlier positions at the same company — that is left_on, the last day before leaving — and a NULL means there was none, so the outer filter left_on IS NOT NULL keeps only true returns. Member 3 returns to Lotus Health and later to Cedar Robotics, so both are rows. TIMESTAMPDIFF(MONTH, left_on, returned_on) counts only whole months.
The alternative finds the immediate predecessor p with a self join plus NOT EXISTS (no position starts between the two) and the earlier stints e with another self join, grouping per return. It is more joins for the same answer; the window version sorts each member's history once.
Reference solution (MySQL)
WITH hist AS (
SELECT position_id, member_id, company_name, start_date,
LAG(company_name) OVER (PARTITION BY member_id ORDER BY start_date) AS prev_company
FROM WorkHistory
), comebacks AS (
SELECT s.member_id, s.company_name, s.start_date AS returned_on,
(SELECT MAX(w.end_date) FROM WorkHistory w
WHERE w.member_id = s.member_id AND w.company_name = s.company_name
AND w.start_date < s.start_date) AS left_on
FROM hist s
WHERE s.prev_company <> s.company_name
)
SELECT member_id, company_name, left_on, returned_on,
TIMESTAMPDIFF(MONTH, left_on, returned_on) AS months_away
FROM comebacks
WHERE left_on IS NOT NULL
ORDER BY member_id, returned_onAnother way
SELECT r.member_id, r.company_name, MAX(e.end_date) AS left_on, r.start_date AS returned_on,
TIMESTAMPDIFF(MONTH, MAX(e.end_date), r.start_date) AS months_away
FROM WorkHistory r
JOIN WorkHistory e ON e.member_id = r.member_id AND e.company_name = r.company_name AND e.start_date < r.start_date
JOIN WorkHistory p ON p.member_id = r.member_id AND p.start_date < r.start_date AND p.company_name <> r.company_name
WHERE NOT EXISTS (SELECT 1 FROM WorkHistory b WHERE b.member_id = r.member_id AND b.start_date > p.start_date AND b.start_date < r.start_date)
GROUP BY r.position_id, r.member_id, r.company_name, r.start_date
ORDER BY r.member_id, returned_on← LinkedIn: Top Endorsed Skill per Industry and Its Lead · Meta: Marketplace Listings Sold Within a Day →