LinkedIn: Members Whose Profile Views Doubled in May
The "Who viewed your profile" team wants to celebrate members whose profile is suddenly getting noticed.
- Difficulty: Medium
- Topics: Aggregation, Conditional Logic, Dates
- Dialect: MySQL
- Problem: #733
Problem statement
The "Who viewed your profile" team wants to celebrate members whose profile is suddenly getting noticed.
For each viewed member, count the views their profile received in April 2025 and in May 2025. Return member_id (the viewed member), april_views and may_views for members with at least one April view whose May views are at least double their April views. Order the rows by member_id.
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: ProfileView
| Column | Type |
|---|---|
| view_id | int |
| viewer_id | int |
| viewed_id | int |
| viewed_at | datetime |
Primary key: view_id.
One row per profile view: viewer_id looked at viewed_id's profile. Every view counts, including repeat views by one viewer.
Examples
Example 1
ProfileView
| view_id | viewer_id | viewed_id | viewed_at |
|---|---|---|---|
| 1 | 20 | 1 | 2025-04-03 10:00:00 |
| 2 | 21 | 1 | 2025-05-07 12:30:00 |
| 3 | 22 | 1 | 2025-05-31 23:59:00 |
| 4 | 23 | 2 | 2025-04-10 09:00:00 |
| 5 | 24 | 2 | 2025-04-30 23:30:00 |
| 6 | 25 | 2 | 2025-05-01 00:10:00 |
| 7 | 26 | 3 | 2025-05-12 08:00:00 |
| 8 | 27 | 3 | 2025-05-13 08:00:00 |
| 9 | 28 | 4 | 2025-03-31 22:00:00 |
| 10 | 28 | 4 | 2025-04-15 11:00:00 |
| 11 | 29 | 4 | 2025-05-15 11:00:00 |
| 12 | 29 | 4 | 2025-05-16 11:00:00 |
| 13 | 29 | 4 | 2025-06-01 00:00:00 |
Output
| member_id | april_views | may_views |
|---|---|---|
| 1 | 1 | 2 |
| 4 | 1 | 2 |
How to solve LinkedIn: Members Whose Profile Views Doubled in May
Both counts come from one grouped scan with conditional aggregation: each view adds 1 to the April column if it falls in April and to the May column if it falls in May, and views in other months add to neither. Because viewed_at is a datetime, the month is tested as a half-open range — >= '2025-05-01' AND < '2025-06-01' — which keeps a view at 23:59 on the 31st that a BETWEEN '2025-05-01' AND '2025-05-31' would lose (that upper bound is midnight at the start of the 31st).
The comparison runs in HAVING, on the grouped values. Requiring at least one April view matters: without it, a member with no views in either month would satisfy 0 ≥ 2 × 0, and a member who was first noticed in May has no April baseline to double. "At least double" is inclusive, so 2 → 4 qualifies.
The alternative builds a per-month count in two derived tables, using DATE_FORMAT(viewed_at, '%Y-%m') to pick the month, and LEFT JOINs May onto April so a member with no May views gets 0 rather than disappearing; starting from April enforces the baseline rule. It reads the table twice, the conditional version once.
Reference solution (MySQL)
SELECT viewed_id AS member_id,
SUM(CASE WHEN viewed_at >= '2025-04-01' AND viewed_at < '2025-05-01' THEN 1 ELSE 0 END) AS april_views,
SUM(CASE WHEN viewed_at >= '2025-05-01' AND viewed_at < '2025-06-01' THEN 1 ELSE 0 END) AS may_views
FROM ProfileView
GROUP BY viewed_id
HAVING april_views >= 1 AND may_views >= 2 * april_views
ORDER BY member_idAnother way
SELECT a.viewed_id AS member_id, a.views AS april_views, COALESCE(m.views, 0) AS may_views
FROM (SELECT viewed_id, COUNT(*) AS views FROM ProfileView WHERE DATE_FORMAT(viewed_at, '%Y-%m') = '2025-04' GROUP BY viewed_id) a
LEFT JOIN (SELECT viewed_id, COUNT(*) AS views FROM ProfileView WHERE DATE_FORMAT(viewed_at, '%Y-%m') = '2025-05' GROUP BY viewed_id) m
ON m.viewed_id = a.viewed_id
WHERE COALESCE(m.views, 0) >= 2 * a.views
ORDER BY member_id← LinkedIn: Invitation Acceptance With and Without a Note · LinkedIn: Most Common Reaction on Each Post →