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
| Column | Type |
|---|---|
| invitation_id | int |
| sender_id | int |
| recipient_id | int |
| sent_on | date |
| status | enum(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_id | sender_id | recipient_id | sent_on | status |
|---|---|---|---|---|
| 1 | 11 | 41 | 2025-05-30 | pending |
| 2 | 12 | 42 | 2025-05-31 | pending |
| 3 | 13 | 43 | 2025-04-02 | accepted |
| 4 | 14 | 44 | 2025-04-20 | pending |
| 5 | 15 | 45 | 2025-06-15 | pending |
| 6 | 16 | 46 | 2025-04-20 | pending |
| 7 | 17 | 47 | 2025-03-11 | withdrawn |
Output
| invitation_id | sender_id | days_pending |
|---|---|---|
| 4 | 14 | 71 |
| 6 | 16 | 71 |
| 1 | 11 | 31 |
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_idAnother 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 →