LinkedIn: Jobs Reposted Within 30 Days — SQL Problem
When a company posts the same role again soon after the last posting, it usually means the first one did not fill — the jobs marketplace team wants those…
- Difficulty: Medium
- Topics: Window Functions, Dates
- Dialect: MySQL
- Problem: #736
Problem statement
When a company posts the same role again soon after the last posting, it usually means the first one did not fill — the jobs marketplace team wants those reposts.
A posting is a repost when the same company posted a job with the same title earlier, and the most recent such earlier posting is at most 30 days before it. Return job_id, company_id, title and days_since_previous (days since that most recent earlier posting) for every repost, ordered by job_id. A company never posts the same title twice on one day.
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: JobPosting
| Column | Type |
|---|---|
| job_id | int |
| company_id | int |
| title | varchar |
| posted_on | date |
Primary key: job_id.
One row per job posting. Titles are spelt consistently, so the same role always has the same title.
Examples
Example 1
JobPosting
| job_id | company_id | title | posted_on |
|---|---|---|---|
| 1 | 10 | Data Analyst | 2025-01-05 |
| 2 | 10 | Data Analyst | 2025-02-04 |
| 3 | 10 | Data Analyst | 2025-03-10 |
| 4 | 11 | Data Analyst | 2025-01-20 |
| 5 | 10 | Software Engineer | 2025-01-25 |
| 6 | 10 | Software Engineer | 2025-02-25 |
| 7 | 11 | Product Manager | 2025-02-01 |
| 8 | 11 | Product Manager | 2025-02-11 |
Output
| job_id | company_id | title | days_since_previous |
|---|---|---|---|
| 2 | 10 | Data Analyst | 30 |
| 8 | 11 | Product Manager | 10 |
How to solve LinkedIn: Jobs Reposted Within 30 Days
Each posting is compared with one other row: the latest earlier posting of the same company and title. LAG(posted_on) over a window partitioned by (company_id, title) and ordered by date hands that date to every row, and DATEDIFF turns it into a gap in days. The first posting of a role has no predecessor; LAG gives NULL, DATEDIFF of NULL is NULL, and NULL <= 30 is not true, so first postings are excluded without a separate check.
Only the most recent earlier posting matters: in the example, job 3 comes 34 days after job 2 and is not a repost, even though job 1 is the same role. The same title at a different company is a different partition. The bound is inclusive — exactly 30 days is a repost.
The alternative self-joins each posting to all earlier postings of the same role and takes MAX(p.posted_on) per posting, filtering the gap in HAVING; it is quadratic within a role, while the window version sorts each partition once.
Reference solution (MySQL)
WITH s AS (
SELECT job_id, company_id, title, posted_on,
LAG(posted_on) OVER (PARTITION BY company_id, title ORDER BY posted_on) AS prev_on
FROM JobPosting
)
SELECT job_id, company_id, title, DATEDIFF(posted_on, prev_on) AS days_since_previous
FROM s
WHERE DATEDIFF(posted_on, prev_on) <= 30
ORDER BY job_idAnother way
SELECT j.job_id, j.company_id, j.title, DATEDIFF(j.posted_on, MAX(p.posted_on)) AS days_since_previous
FROM JobPosting j
JOIN JobPosting p ON p.company_id = j.company_id AND p.title = j.title AND p.posted_on < j.posted_on
GROUP BY j.job_id, j.company_id, j.title, j.posted_on
HAVING DATEDIFF(j.posted_on, MAX(p.posted_on)) <= 30
ORDER BY j.job_id← LinkedIn: Custom Profile URLs That Break the Rules · LinkedIn: Applicants Holding Every Required Skill →