LinkedIn: Jobs Reposted Within 30 Days

Medium
736.

LinkedIn: Jobs Reposted Within 30 Days

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

ColumnType
job_idint
company_idint
titlevarchar
posted_ondate

Primary key: job_id.

One row per job posting. Titles are spelt consistently, so the same role always has the same title.

Example

The rows must come back in the order shown.

JobPosting

job_idcompany_idtitleposted_on
110Data Analyst2025-01-05
210Data Analyst2025-02-04
310Data Analyst2025-03-10
411Data Analyst2025-01-20
510Software Engineer2025-01-25
610Software Engineer2025-02-25
711Product Manager2025-02-01
811Product Manager2025-02-11

Output

job_idcompany_idtitledays_since_previous
210Data Analyst30
811Product Manager10
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: 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

ColumnType
job_idint
company_idint
titlevarchar
posted_ondate

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_idcompany_idtitleposted_on
110Data Analyst2025-01-05
210Data Analyst2025-02-04
310Data Analyst2025-03-10
411Data Analyst2025-01-20
510Software Engineer2025-01-25
610Software Engineer2025-02-25
711Product Manager2025-02-01
811Product Manager2025-02-11

Output

job_idcompany_idtitledays_since_previous
210Data Analyst30
811Product Manager10

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_id

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