LinkedIn: Boomerang Hires in Members' Work History

Hard
747.

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

ColumnType
position_idint
member_idint
company_namevarchar
start_datedate
end_datedate

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.

Example

The rows must come back in the order shown.

WorkHistory

position_idmember_idcompany_namestart_dateend_date
11Northwind Labs2018-07-012020-03-31
21Kestrel Analytics2020-05-012021-12-31
31Northwind Labs2022-02-15NULL
42Saffron Fintech2019-01-012019-12-31
52Saffron Fintech2020-01-012022-06-30
62Orbit Retail2022-08-012023-03-31
73Lotus Health2017-06-012018-05-31
83Cedar Robotics2018-07-012019-06-30
93Lotus Health2019-08-012020-12-31
103Cedar Robotics2021-02-01NULL

Output

member_idcompany_nameleft_onreturned_onmonths_away
1Northwind Labs2020-03-312022-02-1522
3Lotus Health2018-05-312019-08-0114
3Cedar Robotics2019-06-302021-02-0119
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: 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

ColumnType
position_idint
member_idint
company_namevarchar
start_datedate
end_datedate

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_idmember_idcompany_namestart_dateend_date
11Northwind Labs2018-07-012020-03-31
21Kestrel Analytics2020-05-012021-12-31
31Northwind Labs2022-02-15NULL
42Saffron Fintech2019-01-012019-12-31
52Saffron Fintech2020-01-012022-06-30
62Orbit Retail2022-08-012023-03-31
73Lotus Health2017-06-012018-05-31
83Cedar Robotics2018-07-012019-06-30
93Lotus Health2019-08-012020-12-31
103Cedar Robotics2021-02-01NULL

Output

member_idcompany_nameleft_onreturned_onmonths_away
1Northwind Labs2020-03-312022-02-1522
3Lotus Health2018-05-312019-08-0114
3Cedar Robotics2019-06-302021-02-0119

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_on

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