LinkedIn: Invitations Pending for More Than 30 Days

Easy
723.

LinkedIn: Invitations Pending for More Than 30 Days

EasyBasicsDates

MySQL

The growth team sends a gentle reminder to members whose connection invitation has sat unanswered for too long, and wants the list as of 2025-06-30.

Return invitation_id, sender_id and days_pending — the number of days from sent_on to 2025-06-30 — for every invitation whose status is pending and whose days_pending is strictly greater than 30. Accepted, ignored and withdrawn invitations are never in the answer. Order the rows by days_pending descending, then by invitation_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: Invitation

ColumnTypeValues
invitation_idint
sender_idint
recipient_idint
sent_ondate
statusenumpending, accepted, ignored, withdrawn

Primary key: invitation_id.

One row per connection invitation. Every sent_on is on or before 2025-06-30.

Example

The rows must come back in the order shown.

Invitation

invitation_idsender_idrecipient_idsent_onstatus
111412025-05-30pending
212422025-05-31pending
313432025-04-02accepted
414442025-04-20pending
515452025-06-15pending
616462025-04-20pending
717472025-03-11withdrawn

Output

invitation_idsender_iddays_pending
41471
61671
11131
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: Invitations Pending for More Than 30 Days

The growth team sends a gentle reminder to members whose connection invitation has sat unanswered for too long, and wants the list as of 2025-06-30.

  • Difficulty: Easy
  • Topics: Basics, Dates
  • Dialect: MySQL
  • Problem: #723

Problem statement

The growth team sends a gentle reminder to members whose connection invitation has sat unanswered for too long, and wants the list as of 2025-06-30.

Return invitation_id, sender_id and days_pending — the number of days from sent_on to 2025-06-30 — for every invitation whose status is pending and whose days_pending is strictly greater than 30. Accepted, ignored and withdrawn invitations are never in the answer. Order the rows by days_pending descending, then by invitation_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: Invitation

ColumnType
invitation_idint
sender_idint
recipient_idint
sent_ondate
statusenum(pending, accepted, ignored, withdrawn)

Primary key: invitation_id.

One row per connection invitation. Every sent_on is on or before 2025-06-30.

Examples

Example 1

Invitation

invitation_idsender_idrecipient_idsent_onstatus
111412025-05-30pending
212422025-05-31pending
313432025-04-02accepted
414442025-04-20pending
515452025-06-15pending
616462025-04-20pending
717472025-03-11withdrawn

Output

invitation_idsender_iddays_pending
41471
61671
11131

How to solve LinkedIn: Invitations Pending for More Than 30 Days

Each invitation qualifies on its own, so this is a filter with a computed column. DATEDIFF('2025-06-30', sent_on) counts the days the invitation has been waiting; keep the rows whose status is pending and whose wait is greater than 30.

The boundary matters: an invitation sent on 2025-05-31 has waited exactly 30 days and stays out, while one from 2025-05-30 has waited 31 and is in. The alternative turns the condition around into a date comparison — sent_on < DATE_SUB('2025-06-30', INTERVAL 30 DAY) — which can use an index on sent_on because the column is not wrapped in a function, and computes the same day count with TIMESTAMPDIFF(DAY, …).

Two invitations sent on the same day have the same days_pending, so the order needs the second key invitation_id to be fixed. The query is one scan of the table.

Reference solution (MySQL)

SELECT invitation_id, sender_id, DATEDIFF('2025-06-30', sent_on) AS days_pending
FROM Invitation
WHERE status = 'pending'
  AND DATEDIFF('2025-06-30', sent_on) > 30
ORDER BY days_pending DESC, invitation_id

Another way

SELECT invitation_id, sender_id, TIMESTAMPDIFF(DAY, sent_on, '2025-06-30') AS days_pending FROM Invitation WHERE status = 'pending' AND sent_on < DATE_SUB('2025-06-30', INTERVAL 30 DAY) ORDER BY days_pending DESC, invitation_id

← Hotstar: Subscribers Covered on Every Playoff Match Day · LinkedIn: Open-to-Work Members With a Blank Headline →